Hide macro from user in Excel but still call via button?

Question

I have a spreadsheet that will be distributed to co-workers; I want to make it as user friendly as possible so in order to run the macros I have a simple button that automates everything for them. But I don't want them to have access to running any of the macros on their own.

I've managed to somewhat do this using

Option Private Module
Public Sub Run_Batch_Report()
'The actual script that works is here, cutting it to skip to the portion that won't work'
Call Misc_Doc
MsgBox ("Report finished.")
End Sub

All that Public Sub Misc_Doc() does is import data from another spreadsheet and format it.

And then having a Command Button calling the module via application.run

Private Sub CommandButton1_Click()
Application.Run "Batching_Module.Run_Batch_Report"
End Sub

Doing this kind of works; as in it seems to run the Run_Batch_Report sub just fine, but that sub also calls other subs within the same module to complete the job. Those subs will not run UNLESS I unlock the VB in developers options for viewing and editing. Is it possible to have the sub run in its entirety (including calling other subs within the same module) or would I have to restructure my sub to just include all the other subs it calls as well?

Sorry if I worded this haphazardly - I'm actually in the middle of a meeting and juggling this while listening to the bosses.

Edit: To clarify, I've locked the VB from being viewed. When I run the script while it's still locked, it won't allow the sub to call other portions of the module. Once my password is entered to unlock VB for viewing, it works.

You could make it so that the button code sets a global Boolean variable (e.g. call it Enabled) equal to true. All other subs exit immediately if this variable isn't true. All exit points to your subs can set the variable equal to false. If other users try to run a sub other than through the button -- nothing happens. — John Coleman
– John Coleman, Commented Aug 22, 2017 at 13:34
Take the time to edit your post to make a clear problem statement. As it stands, I've no idea what the problem is, or how Run_Batch_Report could possibly "not run other subs" unless the VBE is "unlocked" (it's not locked, anyone can press Alt+F11 anytime to bring it up). Possible you need to restructure your sub, but you haven't included any of it, so how could we know? — Mathieu Guindon
– Mathieu Guindon, Commented Aug 22, 2017 at 13:34
FWIW that Application.Run call is not needed. Why couldn't the click handler just do Run_Batch_Report? Option Private Module has no effect whatsoever on member accessibility. It merely hides public members from the host application's macros list, or available UDF's. Also you should avoid underscores in member names, and stick to PascalCase, e.g. RunBatchReport. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 22, 2017 at 13:37
I've edited a little to hopefully make my problem more clear. I'm still relatively new to VB and kind of picking up as I go. — user3450245
– user3450245, Commented Aug 22, 2017 at 13:54

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

Assuming "But I don't want them to have access to running any of the macros on their own" stands for "I don't want the macros to be listed in the 'Macros' window", you already have that.

I can't reproduce your "macro doesn't run unless project is unlocked" issue, with or without Application.Run getting involved, not sure what that's about. In any case, you seem to be under the false impression that password-protecting your project gives it any kind of security. It doesn't.

VBA code is not secure. VBA project password protection is literally a joke, it only annoys the dev (you!) and prevents clueless users that wouldn't know what to do with the VBE anyway, from viewing source code that they wouldn't understand anyway - and if someone wants to see the code, trust me, they will - in a matter of seconds.

If someone can open the host document, they can get to the VBA code.

Either a user can run the macro, or they can't. If a user can click a button to invoke some VBA code, then they have permission to invoke that VBA code from anywhere.

Have your "hidden" macros in a standard module (.bas) with Option Private Module specified:

Option Private Module
Option Explicit

Public Sub SomeHiddenMacro()
    MsgBox "Hi"
End Sub

And then you can still assign that macro to some shape, by typing the macro's name (it's not going to be listed, because Option Private Module):

Excel's "Assign Macro" dialog

Click the button, see it work:

Message box shows up

Shapes can be formatted to look much prettier than any ActiveX button:

"Run batch report" button with gradient shading and 3D bevel

It doesn't need to be any more complicated than that.

The Classy Solution

You're beginning with VBA, so I presume you haven't toyed with class modules much. One of the nice things about class modules is that their public members can't be invoked as macros, because class modules don't exist at run-time - they're types, not modules. For a type to mean anything, it needs to be instantiated - and the macro runner doesn't do that.

So put the "worker" code in a class module, say BatchReport:

Option Explicit

Public Sub Run()
    'TODO: do your thing
End Sub

Now in the macro that's attached to the button (or in the ActiveX button's Click handler), all you do is create an instance of that object with the New keyword, and invoke its Run method:

Option Private Module
Option Explicit

Public Sub RunBatchReport()
    With New BatchReport
        .Run
    End With
End Sub

Here I'm having a With block hold the object reference. Alternatively you could have declared an object variable, and Set its reference to a New instance of the BatchReport class:

Option Private Module
Option Explicit

Public Sub RunBatchReport()
    Dim report As BatchReport
    Set report = New BatchReport
    report.Run
End Sub

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 22, 2017 at 14:12

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3450245 Over a year ago

Thanks for this, it is helpful. I understand that password protection can easily be broken, but I don't expect anyone to try prying that hard into it. It isn't that big of a deal but a specific preference that I have. However I'm still confused as to why password protecting makes my script only partially run but once I unlock it, the script works just fine.

Mathieu Guindon Over a year ago

As I said, I can't reproduce this issue... but IMO it's moot. You'll quickly get sick of constantly typing that password. And cracking it is not hard at all. I have that linked code in a pinned macro-enabled workbook which I called CrackerJack.xlsm; whenever I need to crack a VBA password I just open that and type CrackItOpen in the immediate pane, and boom, unlocked. 2 seconds.

Tom Over a year ago

Another example of why it's fairly moot: I built it into an Add in called Break Things.xla which includes a ribbon. Now whenever I want to open the code I just click a button on the ribbon and I'm in.

Collectives™ on Stack Overflow

Hide macro from user in Excel but still call via button?

1 Answer 1

The Classy Solution

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

The Classy Solution

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related