Run-time error 438 in Outlook VBA when using Application.Run

Question

I am building a VBA macro to execute in Outlook 2019. One of the things I need to do is preprocess some data before I can use it. The data will itself always be a String: but the way I preprocess the data depends on which field in an Excel spreadsheet the data came from. (I'm not invoking Excel from Outlook: I load the spreadsheet from Excel into arrays in Outlook when Outlook loads. So, all of this code is within Outlook VBA.)

Because I don't know what field the data is from until runtime, I cannot hard-code a subroutine name into my code. (I could, I suppose, use a select statement to invoke the preprocessing subroutine for each type of data separately. But that would seriously bulk up the code--I have 25 different data types--and would make adding new data types a more difficult proposition, since I would have to update the code in multiple places.) So instead, I want to build the name of the preprocessing subroutine dynamically, and then call it that way.

I have a module set up called ProcessingModule, in which I have various subroutines: Preprocess_Title, Preprocess_ClientName, and so on. Once I know the name of the field from the Excel spreadsheet, I can find the name of the preprocessing subroutine by removing any spaces from the field and prepending "Preprocess_" to that.

The problem I am having is that when I try to execute the preprocessing subroutine, I keep getting Run-time Error 438: Object doesn't support this property or method.

The exact line of code reads as follows:

Temp = Application.Run(PREPROCESSPREFIX & Replace(g_DataPatterns(OutputData(I), 1), " ", ""), InputData)

PREPROCESSPREFIX is a constant "Preprocess_", g_DataPatterns is a two-dimensional array that contains (in the first column of each row) the name of the field, OutputData is an array that stores the indices of the rows in g_DataPatterns that are of interest, and InputData is the data (String) that I want to preprocess.

I have tried prepending "ProcessingModule." to the name of the subroutine I am trying to invoke, wondering if the problem was that the function was in another module. But that doesn't seem to make any difference. I have also tried putting the preprocessing subroutine in the same module as the subroutine that is invoking the preprocessing module, but that also doesn't seem to make a difference.

I thought about maybe using CallByName instead of Application.Run. But CallByName requires an object with a property or method, and I am trying to work on a String rather than an object. And putting the data in an object just to invoke the subroutine seems like a lot of unnecessary work (since I would need to create a class just to add the preprocessing subroutines as methods of the class).

I have gotten Application.Run to work from Word VBA. But in that case, I needed to call a subroutine in another module, and I knew the name at compile time: I used Application.Run only because I could not access the subroutine any other way.

Any help would be greatly appreciated.

Answer 1

The Run-time Error 438 in Outlook VBA typically occurs when you try to call a method or property that does not exist for the object you are working with. In your case, it seems that the issue arises when using Application.Run to call a subroutine dynamically from another module. Here are a few suggestions to troubleshoot and resolve this issue:

1. Check the Subroutine Name: Ensure that the constructed name of the subroutine is correct. You can debug by printing the name to the Immediate Window before calling Application.Run to verify it is what you expect.
2. Module Reference: Since you mentioned trying to prepend "ProcessingModule." to the subroutine name, ensure that the module name is correct and that the subroutine is declared as Public. If the subroutine is private, it cannot be accessed from outside its module.
3. Use CallByName: While you mentioned that CallByName requires an object, you can create a temporary object to hold your subroutine calls. For example, you can create a dummy object that contains your preprocessing methods as properties, which would allow you to use CallByName effectively.
4. Check for Typos: Double-check for any typos in the subroutine names or the way you are constructing the names dynamically. Even a small typo can lead to this error.
5. Simplify the Call: If possible, simplify the call to just one or two subroutines to see if the error persists. This can help isolate whether the issue is with the dynamic construction of the name or with the subroutine itself.
6. Debugging: Use breakpoints and step through your code to see exactly where it fails. This can provide insights into whether the problem is with the dynamic name resolution or something else.

If these steps do not resolve the issue, consider providing more specific details about the subroutine declarations and how they are structured within the ProcessingModule. This additional context could help further diagnose the problem.

---

References:

• Expected procedure, not module
• Expected Sub, Function, or Property

Answer 2

Hi, Viorel.

Calling the subroutine by full name (ProcessingModule.Preprocess_Title(InputData)) works. But to do that I would have to know the data type at compile time, which is not an option.

Where does it say that Application.Run is not available for Outlook VBA? I'm honestly not surprised that that is the problem, but it would be nice if that information was more easily located.

I guess I will have to try building a class module that includes the preprocessing subroutines, so I can invoke (hopefully) the subroutines using CallByName. I suppose I can invoke the subroutines without having to actually use any data from the class modules.

Thanks!

Ariel