1

I have several modules in my Access 2013 database, which include public subs and functions. However they don't show up in the Run Macro list, and I can't call them if I build a macro with a RunMacro or RunCode command. I have security settings to allow for all macros.

Is there any additional step other than declaring your functions/subs as public in order to run them outside of the VBA window?

kainC
  • 400
  • 1
  • 3
  • 14
  • Are you including the '()' at the end of the name? If not, try adding... – Wayne G. Dunn Feb 25 '17 at 22:03
  • Yes I've tried with and without parentheses, and tried adding the Module name in front of it. – kainC Feb 25 '17 at 22:05
  • Have you tried the run macro command? https://msdn.microsoft.com/en-us/library/bb177365(v=office.12).aspx – majjam Feb 25 '17 at 23:54
  • Just so I'm clear on this... is the issue (a) you can't get a function/sub to run, or (b) you can't see a list of functions/sub's when you build a macro? I agree with the two posters below that you need to use the 'RunCode' action. If I build a macro, use RunCode as the action and manually type in 'GetNetworkUserName()', it will execute that function. If the issue is you are not certain if it runs or not, then add a message box or debug.print to the function then run the macro. – Wayne G. Dunn Feb 26 '17 at 18:59
  • The issue is (a)- it doesn't run, though it isn't offered as a choice either. I commented below that it doesn't run in RunMacro or RunCode, so I'll update the question to show that. – kainC Feb 26 '17 at 22:32
  • It must be a Function that you specify with RunCode. Doesn't matter if Public or not, but it can't be a Sub. I created Function 'SendMsgToMe' which contained only a MsgBox, created and saved a Macro that used RunCode and the executed it. Worked fine. If you are unable to do a similar simple test, then create a blank database and repeat the test. If it works, maybe your DB is in need of repair or rebuilding. – Wayne G. Dunn Feb 27 '17 at 18:29

2 Answers2

2

For running public VBA sub/function in macro you should use RunCodeaction, not RunMacro

Sergey S.
  • 6,296
  • 1
  • 14
  • 29
1

Have you tried the run code macro?

majjam
  • 1,286
  • 2
  • 15
  • 32
  • RunCode and RunMacro both give me the same error. It can't find any of the public subs in my modules. – kainC Feb 26 '17 at 14:12
  • 1
    @kainC have you tried this? http://stackoverflow.com/questions/17244980/ms-access-runcode-macro-cannot-find-my-procedure – majjam Feb 27 '17 at 15:54
  • thanks majjam, this turned out to be what I needed. It had to be specified with the RunCode - AND could only call a function, not a sub. Counterintuitive, since RunCode doesn't handle the return value anyway – kainC Jul 25 '17 at 22:54