11

It is easy to call a function inside a classModule using CallByName How about functions inside standard module?

''#inside class module
''#classModule name: clsExample
  Function classFunc1()
     MsgBox "I'm class module 1"
  End Function
''# 
''#inside standard module
''#Module name: module1
  Function Func1()
     MsgBox "I'm standard module 1"
  End Function
''#
''# The main sub
Sub Main()
''# to call function inside class module
dim clsObj as New clsExample
Call CallByName(clsObj,"ClassFunc1")

''# here's the question... how to call a function inside a standard module
''# how to declare the object "stdObj" in reference to module1?
Call CallByName(stdObj,"Func1") ''# is this correct?

End Sub
Jeff B
  • 8,572
  • 17
  • 61
  • 140
Kratz
  • 766
  • 5
  • 10
  • 17
  • 3
    You can't. Look up 'Application.Run', which does work with routines in standard modules. – jtolle Apr 23 '10 at 00:15
  • As your 'functions' aren't retuning values, you should use `Sub` instead of `Function`. `Call` is never required, you could just write `CallByName clsObj, "ClassFunc1"`. To call a function Func1 inside a standard module is as simple as `Func1` - use appropriate modifiers in the declaration to change the scope if needed – barrowc Apr 23 '10 at 05:57

6 Answers6

4

I think jtolle's response addressed the question best - the small reference to Application.Run may be the answer. The questioner doesn't want to use simply func1 or Module1.func1 - the reason one would want to use CallByName in the first place is that the desired function.sub name is not known at compile time. In this case, Application.Run does work, e.g.:

Dim ModuleName As String
Dim FuncName As String
Module1Name = "Module1"
FuncName = "func1"
Application.Run ModuleName & "." & FuncName

You can also prepend the Project Name before the ModuleName and add another period ".". Unfortunately, Application.Run does not return any values, so while you can call a function, you won't get its return value.

David Horowitz
  • 43
  • 5
  • 3
    This is frustrating. Apparently the internet wisdom is to use `Application.Run` if you have a module and `CallByName` if you have a class object. Unfortunately they're not interchangeable! Error handling, in particular, is different. If someone raises an error in code called by `Application.Run`, I can't catch it in my procedure, but `CallByName` does let me catch it. Is there any workaround? – Mysterious Dan Sep 16 '13 at 16:46
  • @MysteriousDan - did you happen to find the answer for this, would be grateful if you can link :) – SlowLearner Mar 23 '18 at 10:48
  • 1
    That's too bad you can't handle errors using Application.Run. I guess you could create a wrapper class whose sole purpose is to call some of these functions in standard modules. Then you would be able to use CallByName(). – David Horowitz Oct 07 '18 at 06:43
  • @DavidHorowitz I like that idea – SlowLearner Nov 07 '18 at 11:05
  • Thanks. I realize it still has the limitation on not being able to trap errors from inside the routine called by Application.Run, but it does address OP's question. – David Horowitz Nov 08 '18 at 14:08
  • 2
    @DavidHorowitz actually `application.run` returns a variant... https://learn.microsoft.com/en-us/office/vba/api/access.application.run – SlowLearner May 08 '19 at 02:56
3

Although it is an old question and OP asked for CallByName in a standard module, the correct pieces of advice are scattered through answers and comments, and some may not be that accurate, at least in 2020. As SlowLearner stated, Application.run DOES return a Variant, and in that way both branchs below are equivalent, except by handling errors, as commented around Horowitz's answer:

Dim LoadEnumAndDataFrom as Variant
'FunctionName returns a Variant Array
if fCallByName then
    LoadEnumAndDataFrom = CallByName(ClassObj, "FunctionNameAtClass", VbMethod)
else
    'After moving back function for a standard module
    LoadEnumAndDataFrom = Application.Run("StandardModuleName" & "." & "FunctionNameAtStandard")
endif

I actually just did this above and had no errors at all, tested in Word, Excel and Access, and all return the same Array.

Unfortunately, there is an exception: Outlook's object Model is too protected and it does not have the Run method.

Marcelo Scofano Diniz
  • 590
  • 1
  • 7
  • 24
  • Your answer is the first one I find that tells me `Application.Run` can return values. And this solved my issue. – Kar.ma Jun 16 '20 at 15:55
  • @Kar.ma: I think that in old times it didn't, that's why older answers categorically affirm the opposite. Glad it helped, for me it was a game changer too. – Marcelo Scofano Diniz Jun 16 '20 at 19:49
2

CallByName works only with class objects.

If your subroutine is in a standard module, you can do this:

Sub Main()
    Module1.Func1
End Sub

If it's a function, then you'll probably want to capture the return value; something like this:

Sub Main()
    Dim var
    var = Module1.Func1
End Sub
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
1

Modules in VB6 and VBA are something like static classes, but unfortunately VB doesn't accept Module1 as an object. You can write Module1.Func1 like C.Func1 (C being an instance of some Class1), but this is obviously done by the Compiler, not at runtime.

Idea: Convert the Module1 to a class, Create a "Public Module1 as Module1" in your Startup-module and "Set Module1 = New Module1" in your "Sub Main".

Hans-Gerd Theunissen
  • 11
  • 1
0

Unfortunately it is not possible to prepend the ProjectName before the ModuleName and add another period "." In MS Word this throws a runtime error 438. The call is restricted to the use of simply ModuleName.ProcName.

catfood
  • 4,267
  • 5
  • 29
  • 55
Theo Köster
  • 51
  • 1
  • 7
0

This works:

Public Sub testAppRun()
Dim myfunc As String, teststring As String
    myfunc = "testrunfunc"
    teststring = "this is my string"
    MsgBox Run(myfunc, teststring)
End Sub

Public Function testrunfunc(val As String) As Variant
    testrunfunc = val & " changed"
End Function
Hoggle
  • 1