vba private scripts

Question

I know that even private vba scripts can be called by the user, such that making it "private" in fact only hides its name.

However, is there a way to set up a macro so it is only runnable if you are inside that particular VBA project? Not from Excel and not from any VBScript or the likes.

Answer 1

If you want to lock down the code you could

• make the code private so the macro names areen't exposed
• lock the protect, and then test that the project is actually unlocked before letting the code run

The sample code below checks that the VBA in the host workbook is not protected before running

Does this meet your needs?

    Private Sub TestMe()
    Dim objVB As Object
    Set objVB = ThisWorkbook.VBProject
    If objVB.Protection = 0 Then
        Call TestSub
    Else
        MsgBox "Sorry sport - unauthorised", vbCritical
    End If
    End Sub

    Private Sub TestSub()
    MsgBox "Project unprotected - i've been run", vbOK
    End Sub

Answer 2

Honestly, there is no foolproof way around it. You can have certain checks but that's all you can do.

If you are sure that a user will not have access to VBA Code then what brettdj suggested is the best way to go ahead. Protect your project. One cannot run a macro from Excel or from outside Excel if one doesn't know the macro name ;)

The basic intention of making a macro Private is not to prevent it from running but making it invisible to the user. Mostly, the only macros that needs to be private in Excel are the inbuilt Worksheet or Workbook events or macros referred by other macros which necessarily don't need to be accessed by the user. A user can still access them from outside VBA if he or she wants to.

Having said that, you can restrict (But not STOP - Not referring to disabling your macro security) the macros from running. Pass an authorization FLAG. Only when it receives an "Authorization", will it run.

For Example

Option Explicit

Private Sub Sample(Auth As Boolean)
    If Auth = True Then
        '~~> Your macro code goes here
    End If
End Sub

Now if you want this macro to be called from VBA then you have to call it like this

Sample True

or

Call Sample(True)

This will ensure that the above macro will only run when you allow it to.

Would this method prevent user from running this from VBS?

NO. It won't. However, it won't run till the time the user specifies or gives the "Authorization"

HTH