Solved

Excel -Use a keyboard shortcut to run a macro located in a Private Module

Posted on 2013-05-22
16
768 Views
Last Modified: 2014-09-23
Excel -Use a keyboard shortcut to run a macro located in a Private Module

Hi
I want to run a macro "SubIReallyWant" by using a keyboard shortcut.
That's easy.
I can assign the shortcut programatically
That's also easy.
Changing the module to Option Private Module stops the shortcut :-/
Commenting 'Option Private Module makes the shortcut work again

Sure I could Do something like

Module1 (Public)
Sub DummySub()
    'Shortcut "Ctrl +Shift + A"
    Call Module2.SubIReallyWant
End Sub

Module2
Option Private Module
Sub SubIReallyWant()
    Do some stuff
End Sub

Only this is kinda clumsy!
Looking for an eloquent solution?
0
Comment
Question by:sirplus
  • 5
  • 4
  • 3
  • +1
16 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39190065
Hi,

Only this is kinda clumsy!
...but is an intentional implementation design within Visual Basic for Applications.

Looking for an eloquent solution?
Why do you need to use a Private Module to store your macro (Visual Basic for Applications) code?

Are you looking to restrict usage, to reduce the overall memory footprint of the application code, or do you have a Public Module that contains a subroutine of a similar name/nature?

Thanks for expanding on your reason(s) for the necessity to restrict the scope of your subroutine.

BFN,

fp.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39191253
The reason for a private sub is to prevent other modules from seeing the sub.  If you want others to see it you make it Public (or also Friend in VB.Net).
If you're looking to make a specific sub private but not others, don't make the whole module private but just prefix the sub with the word private:

'Option Private Module <- don't add this
Sub SubIReallyWant()
    'DoSomeStuff
End Sub

Private Sub DoSomeStuff()
' bla bla bla
End Sub
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39191362
Yes, sorry, I appreciate that, but...

The reason for a private sub is to prevent other modules from seeing the sub

Why do you have a need to do this?
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39197183
The request is for an elegant solution to the problem. It is well known that beauty is in the eye of the beholder. Perhaps the same applies to elegance.
1. I have a sub which is Private and therefore not accessible from the keyboard.
2. Therefore I prepare a 3-line sub that is accessible and use it to call the unavailable sub.

In order to make this solution more elegant I would need to cut back on the code. Obviously, that isn't possible. Therefore sirplus must be thinking of another way. Another way, as sirplus obviously knows, is to make the sub to be called not Private. There is no elegance in shifting goal posts.

I think that a 3-line procedure to outsmart VBA is just about as elegant as code can get. With that being said, I am as curious as everybody else why the sub to be called must be Private or in a Private module or both. Have you noticed that Microsoft has all but done away with Public procedures? Now a-days procedures are Public by default, as are modules. One should look for a very good reason to protect their privacy.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39197532
Thanks Faustulus.

My line of questioning is to establish the requirements for the Private module so I can advise on other options (if they exist).

Without understanding this I am finding it difficult to be "more elegant" with the coding approach.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39199901
fanpages,
To tell you frankly, I never heard of Private modules before you mentioned the term. Therefore I looked it up and find that Chuck Pearson also doesn't mention them. In his excellent article just referenced he also explains about Private procedures. I have observed that Microsoft has stopped referring to Public Subs and Public Functions, replacing these declarations with plain Sub or Function which are Public by default. I take this to mean that you might look for advantage in declaring your procedures as Private without facing disadvantage over allowing them to be Public.
A Private procedure is "visible" within the module in which it is declared. Public procedures, on the other hand, can be referenced from outside the module and even outside the workbook. Your problem seems to be that you would like to access a procedure declared as Private from outside its module of origin, to wit, the keyboard. The most "elegant" - obvious might be the better word - solution would be to simply delete the word "Private" in the declaration which would make the procedure Public by default. You want to know what side effects this might have.
The first difference is in the naming scope. For example, while programming, I often write little procedures which invariably are declared as Private Sub test(). I might have such a procedure on each module in the workbook. When I call such a procedure VBA will know which one I want it to run because within the scope of each module there is only one. If I write a second procedure by the same name in the same module VBA will complain at compile time.
If I were to declare all my test procedures as Public (explicitly or by default) the complaint might not come immediately, depending upon the size of my project, but whenever "test" is called Excel wouldn't know which one to use.
So, before you change your procedure's scope of declaration you should make sure that there is no other procedure by the same name within the project. You can select the procedure's name and search for it (Edit Find) throughout the project. Don't concern yourself with calls, just make sure there is no other declaration. If there isn't, you can safely declare it is Public (explicitly of by default).
The uniqueness of its name may play a part even beyond the workbook. This might be the case if you access the code from other projects. The most common scenario would be one where you have several workbooks open at the same time, each one having the same code and, therefore, the same module and procedure names. The problem would arise from ambiguity. In most such cases it may not matter which of the procedures actually runs because they all do the same job. So long as Excel selects any of them everybody could be happy. However, a procedure which is designed to be run within a single project may contain referencing in its code which would be ambiguous under other circumstances. A programmer who declares his procedure as Private wouldn't have had the need to think of that.
For you, if you frequently keep several workbooks containing the same code open simultaneously, you might wish to examine the referencing contained in the procedure you wish to declare as Public to make sure that there are no potential ambiguities.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39199967
Hi Faustulus,

I am not sure if your detailed explanation was directed at me, but it is sirplus' reason(s) that I was interested in.

Personally, I would only define a subroutine or function as 'Global' (now known as Public) in a Public (Global) code module if I wished every other code module to be able to use it.

If I did not, then it would remain Private.  I could, however, fully-qualify the calling of a Private routine (prefixing it with the Module name followed by a period "." symbol) if required.

The same is true with variables, constants, user-defined types, external library routines, & any others constructs that can be dimensioned/declared with different scopes.

This was due to my background in Visual Basic for Windows (versions 1.0 to 3.0, in 16-bit versions of the Windows operating system) where the overall memory requirements for running a compiled executable would be greater if Global routines/variables/constants were defined when individual Forms were in use/loaded.

If the "Global Name Table" space &/or "Module Name Table" space was exhausted, the error message "Out of memory" would be seen (all too often).

For reference:
"How to Optimize Memory Management in VB 3.0 for Windows"
[ http://support.microsoft.com/kb/112860/EN-US ]

BFN,

fp.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 14

Expert Comment

by:Faustulus
ID: 39199976
Hello fanpages,
Indeed, my comments were addressed to sirplus. Thank you for correcting me. I will remember both names now. :-)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39199978
OK :)
0
 
LVL 5

Author Comment

by:sirplus
ID: 39258606
Hi
Sorry, its been a while.
The reason is so I can easily run macros which are not available to those that don't know about them. I want these hidden from the list of Macros visible to users so "private" and I want to be able to run same from keyboard shortcut so I can run them without having to hit ALT F11 and type in a password. I would run these macros with other users sitting around the screen so don't want to be delving into the vbe environment. If the trigger macro that calls the hidden is visible in the list then I might as well make the private one public.
The main work around I have thought of is to hard code a list of privileged users that unless the user is one of those then code will exit. That's easy enough only there are instances where I will want to just choose to reveal the shortcut to a user on a whim and wouldn't want to muck about with coding in so they have access. I really just want to be able to hide macros from users but have easy access myself.
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 250 total points
ID: 39258666
Only macros which are available in the macro list can be called by keyboard shortcut. Perhaps the answer would be a dashboard from which you call macros which aren't listed and put a simple password between the lst of available macros and the caller of the dashboard. Meaning, any one can call the dashboard, but you won't see the macro list until you enter 1234 in A1. You could disclose this method / password at a whim and change it the next day.
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 250 total points
ID: 39259908
I don't see any way this can be done within Excel since it is designed to either show you macros or not, but not to selectively decide who gets to see the macros.

If you don't want to allow them to run any of those macros at all, just put them into your personal macro workbook and make sure nobody else has access to it.  They'll see the names of the macros and if they run them, they'll get a message that they don't exist.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39259916
Another option is to rewrite the Excel interface using something like VB.Net, then you can have it do anything you want.  And by tapping into the existing Excel functionality, you would only have to re-write parts of it, such as the macro window.
This is probably much more trouble than it's worth since you'll need to re-connect you app to a lot of the Excel pieces.
0
 
LVL 5

Author Comment

by:sirplus
ID: 40340150
HI
Sorry
My users must be treated as hostile and the less that appears in the list of macros the better.

It prevents the users calling ones that shouldn't be because without the full name inc module name a private macro or macro in a private module cant be called.

If Macros or modules aren't private they appear in the run list and can be called at click of a mouse. Also I only want permissible relevant macros in the run list ant it not be clogged with thousands of irrelevant and potentially damaging macro names.

Simply put, anything public has its name in the run list which is bad for many of my macros from my perspective.

PS
I solved this issue by porting all (300,000+) lines of code to vb6 and compiling to a dll; just keeping pointers as per above but to the code in the dll in excel.
 ;-)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now