MS Access RunCode Macro cannot find my procedure

Question

I'm only posting this since I wasn't able to find a solution anywhere. I finally figured it out. Kind of silly really.

When using the RunCode property within an Access Macro, I was trying to run a Sub from my global module. I was getting the error "The expression you entered has a function name that database can't find." I couldn't figure out what the issue was. I followed the advice of everyone that posted on this issue, which was mostly the following:

1. Use () at the end of the procedure name
2. DO NOT use the "=" before the procedure name

Still didn't work!

Disgorge

Answer 1

THEN I read the error message carefully. It mentions that it could not find the FUNCTION name. Apparently, the RunCode property specifically requires a "Function" not a Sub. So, I simply changed my Sub to Function and it worked fine!

Hope this helps.

Disgorge

Gosh, this was driving me crazy half a day! I'm not that used to Access programming, and just needed a little code to include. The code was fine, module showed no errors, but my "subs" disn't even show up in the picker (another indicator that something is wrong). Just replacing "Sub" with "Function" indeed solved the issue! Needless to say that the "MS Access (context) Help" was not even able to point the direction... – Strychninism

Just wanted to let you know that years later, this is still saving people's bacon – Antoineantoinetta

Thanks. I found there's no need for the function to return anything or have a non-default type. You can just replace the word "Sub" with the word "Function". – Nigro

@Disgorge This still does not work. Where do you save your module? I have it on the left hand side navigation bar. This is driving me crazy, seems like the easiest stupid thing in the world. – Ollieollis

If you press ALT-F11, the VBA code area will be opened. All the modules are there. Not sure what you mean by saving a module on the nav bar. If you mean your MACRO shows in the nav bar, that makes sense. But your Function code will reside in the underlying code, which you can access by ALT-F11. There is a menu (ribbon) option for it but I can't remember where. I never use it. I think it will say "view code" or "view VBA" or something like that. – Disgorge

Remember to add the parenthesis after the function name in the macro. – Pointer

Answer 2

Another solution that worked for me:

The module name can NOT have the same name as the procedure(s) in the module(s).

Arlyn

I was using MS Access 2016 and started having this problem so this problem is still rearing its ugly head. For mine to work the Function name and Macro Name had to be different and in the code after Function myFunc() the name there had to be different than the Module Name that you see in the Access side bar. Another odd thing. When I selected RunCode in Macro it would show me the Function (it would not show me the Sub VBA modules) I was trying to select but when I selected it, Macro coded it as the Function name with a single left paren "(". This was running on a Windows 8 Pro machine. – Oidium

Answer 3

I had a similar issue with the error message. My VBA code had the following declaration:

private function MyFunction()

....

end function

I removed private declaration to get the Macro Runcode to execute the MyFunction()

For example:

Function MyFunction()

End Function

Benedix

Answer 4

My mistake was putting the function in a Class Module instead of a regular module.

Disrespectful

Answer 5

Access 2013: A function called with MyFunction() from RunCode where MyFunction does not exist gives me error 2425. None of the above work for me, however, and I was still getting Error Number 2001 when the function exists and is public. Database is in a Trusted Location. No compile errors, but something in MyFunction did not work, namely

DoCmd.ShowAllRecords 

after GoToControl worked to select my subform. Real problem was my code to remove a filter with VBA. Manual for ShowAllRecords seems to indicate that this should work, but replacing DoCmd.ShowAllRecords with

DoCmd.RunCommand acCmdRemoveFilterSort 

fixed my problem.

Inexact

As noted, the function must be public, cannot be in a class module (which includes forms/reports code modules). In fact you cannot call subs or functions that reside in a class module. So it is important to keep in mind that forms (or reports) code modules are in fact class objects. To call code in a class module, you must FIRST create or have a running instance of that object. You can however call public functions in a form by using the expression service, and that includes custom menu or ribbon code. – Bone

Answer 6

The database seems to need to have objects in it other than the VBA function being called as well. Without other objects (specifically a table in my case), the function is unable to be found from within the calling environment (eg Excel VBA).

Gebelein

Answer 7

Access Office 365: My subroutine works when called from within VBA and used to work in Macros. Moved function text to a separate module, saved it by itself. Gave the module (not class) a unique name.

Billman

Answer 8

I was borrowing a "template" vbasic text from online as a shell. And while I renamed the function in the code section, MSAccess/Vbasic did not show the name change in the function heading of the Module box so when I ran the macro which called this function it said it couldn't find it. Tried repeatedly to fix. Then I noticed the name was different in the code section of vbasic versus the heading of the function dialog box. So I changed the name manually in the heading box and it prompted me to save which I did and now it works. Maybe this is why snake software is so popular. :)

Paleography

Answer 9

My VSTO C# calls to app.Run("MyMacro") stopped working suddenly and the error message started printing. ("Access cannot find the procedure MyMacro.")

The problem on my machine was that my code (which connected to a running instance of Access) was connecting to a zombie database instance that did not have that VBA macro in it. Go figure. The zombie instance did not show in the Taskbar, in the Alt-TAB display, or in a list of windows that I printed out using a console program.

The way I discovered it was to have my VSTO code print out the name of the database that it was connecting to. The Access database was one that I had been working with earlier in the day, without errors. I had closed the Access app holding it successfully hours before I tried running MyMacro.

I can't think of anything that I did that was unusual, other than upgrading VStudio to the latest version. Probably a reboot will fix things. What a weird problem.

Electroplate