VBA: Passing a Function as an Argument

Question

I'm trying to pass a function (2 functions, actually) as arguments for a Sub with the line:

Call OpenPulledReports(GetFilePaths(), GetFileNames())

I'm not sure if I need the () at the end of the names, but I'm getting an Argument not optional error.

---

Here are those two functions:

Function GetFilePaths(ByRef GetDateItems) As String

Dim fp As String

Dim cy2c As String
cy2c = GetDateItems(currentYear2char)

fp.PartMasterFilePath = "path1" & cy2c & "\" & currentMonth & "\"
fp.SupplierMasterFilePath = "path 2" & cy2c & "\" & currentMonth & "\"

GetFilePaths = fp

End Function

---

Function GetFileNames(ByRef GetDateItems) As String

Dim f As String
Dim cd As String

cd = GetDateItems.currentDate

f.FargoPlant = "part master for SM - blah1 - " & cd & ".xls"
f.Logistics = "part master for SM - blah2 - " & cd & ".xls"
f.PES = "part master for SM - blah3 - " & cd & ".xls"
f.Torreon = "part master for SM - blah4 - " & cd & ".xls"
f.FargoSM = "Supplier Master - blah5 - " & cd & ".xls"
f.TorreonSM = "Supplier Master - blah6 - " & cd & ".xls"

GetFileNames = f

End Function

---

And I suppose I'll include GetDateItems() since it's referenced by both of those functions:

Function GetDateItems() As String

Dim d As String

d.currentMonth = Format(Date, "mmmm") 'July
d.currentDate = Format(Date, "mm-dd-yy") '06-09-15
d.currentYear2char = Format(Date, "yy") '15
d.currentYear4char = Format(Date, "yyyy") '2015
d.currentFiscalMonth = Format(DateAdd("m", 1, Date), "mm") '08
d.wsDate = currentFiscalMonth & currentYear4char '082015

GetDateItems = d

End Function

---

I was originally just using ByRef to every DateItem, FilePath, and FileName item but decided to put them into their own functions to clean my code up.

Thank you very much for your time.

EDIT:

@Brad I'm trying to use an Object now instead of a String.

I'm now getting an "Object variable or With block variable not set" Run-time error on the line d.currentMonth = ...

Function GetDateItems() As String

Dim d As Object

d.currentMonth = Format(Date, "mmmm") 'July
d.currentDate = Format(Date, "mm-dd-yy") '06-09-15
d.currentYear2char = Format(Date, "yy") '15
d.currentYear4char = Format(Date, "yyyy") '2015
d.currentFiscalMonth = Format(DateAdd("m", 1, Date), "mm") '08
d.wsDate = currentFiscalMonth & currentYear4char '082015

GetDateItems = d

End Function

Based on this: http://www.cpearson.com/excel/Classes.aspx , I feel like I'm doing it wrong, but I'm not sure how to do it correctly.

Answer 1

THERE ARE 2 WAYS TO CALL DYNAMICALLY A FUNCTION IN VBA

Here a complete example that you can try

---

I would like to call dynamically one or another function in my procedure depending on some situations ...

To simulate it, here 2 different functions which I will decide at last minute (so at runtime) which to call

Common code to both examples

        Function My_Function_number_1(varTheData As String) As String
            Debug.Print "Calling  My function n°1 with parameter  = (" & varTheData & ")"
            My_Function_number_1 = "I return value = 1"
        End Function

        Function My_Function_number_2(varTheData As String) As String
            Debug.Print "Calling  My function n°2 (" & varTheData & ")"
            My_Function_number_2 = "I return value = 2"
        End Function
        
There are 2 way to do it .. 

---

FIRST MANNER : CALLING DIRECTLY THE FUNCTION WITH THERE PARAMETERS

        Sub Another_Sub_in_which_I_will_pass_the_return_of_the_function_called(ByRef xfunc As Variant)
            Dim RetFromFunction As Variant
            Debug.Print "Another_Sub_in_which_I_will_pass_the_return_of_the_function_called get this datas -> [" & xfunc & "]"
            RetFromFunction = xfunc
            Debug.Print "La fonction appelee dynamiquement a renvoyé ceci : " & RetFromFunction
        End Sub

        Sub My_program_I()
           Dim SomeData As String
           SomeData = "this is the data I want to work in my functions"
           Debug.Print "My_program_I"
           Debug.Print ""
           Debug.Print "Call n°1 -------------------------------------------------------------"
           Call Another_Sub_in_which_I_will_pass_the_return_of_the_function_called(My_Function_number_1(SomeData))
           Debug.Print ""
           Debug.Print "Call n°2 -------------------------------------------------------------"
           Call Another_Sub_in_which_I_will_pass_the_return_of_the_function_called(My_Function_number_2(SomeData))
           Debug.Print ""
        End Sub

Result in debug window

My_program_I
Call n°1 -------------------------------------------------------------
Calling  My function n°1 with parameter  = (this is the data I want to work in my functions)
Another_Sub_in_which_I_will_pass_the_return_of_the_function_called get this datas -> [I return value = 1]
La fonction appelee dynamiquement a renvoyé ceci : I return value = 1
Call n°2 -------------------------------------------------------------
Calling  My function n°2 (this is the data I want to work in my functions)
Another_Sub_in_which_I_will_pass_the_return_of_the_function_called get this datas -> [I return value = 2]
La fonction appelee dynamiquement a renvoyé ceci : I return value = 2

---

SECOND MANNER : CALLING INDIRECTLY THE FUNCTION INSIDE THE OTHER SUB WHICH WILL TAKE IN CHARGE TO PASS SOME PARAMETERS

        Sub Another_Sub_in_Which_I_just_pass_the_function_to_call_and_which_will_take_care_to_call_it_with_some_parameters(varNameofFunction2Execute As String)
            Dim SomeData As Variant
            SomeData = "some datas to pass to the function"
            Debug.Print ("I will call the function : " & varNameofFunction2Execute & " with the paramters (" & SomeData & ")")
            result = Application.Run(varNameofFunction2Execute, SomeData)
            Debug.Print ("this is the retust I obtained = [" & result & "]")
        End Sub

        Sub My_program_II()
            Debug.Print "My_program_II"
            Debug.Print ""
            Debug.Print "Call n°1 -------------------------------------------------------------"
            Call Another_Sub_in_Which_I_just_pass_the_function_to_call_and_which_will_take_care_to_call_it_with_some_parameters("My_Function_number_1")
            Debug.Print ""
            Debug.Print "Call n°1 -------------------------------------------------------------"
            Call Another_Sub_in_Which_I_just_pass_the_function_to_call_and_which_will_take_care_to_call_it_with_some_parameters("My_Function_number_2")
            Debug.Print ""
        End Sub

Result in debug window

My_program_II
Call n°1 -------------------------------------------------------------
I will call the function : My_Function_number_1 with the paramters (some datas to pass to the function)
Calling  My function n°1 with parameter  = (some datas to pass to the function)
this is the retust I obtained = [I return value = 1]
Call n°1 -------------------------------------------------------------
I will call the function : My_Function_number_2 with the paramters (some datas to pass to the function)
Calling  My function n°2 (some datas to pass to the function)
this is the retust I obtained = [I return value = 2]

Answer 2

I am not certain this is a good way to code but if you want to pass values in this manner then you should be using User Defined Types. Below is a module which provides functionality similar to you have described.

If you run this:

call OpenPulledReports(GetFilePaths(GetDateItems()), GetFileNames(GetDateItems()))

You will get this (in the debug window):

path 218\April\part master for SM - blah4 - 04-23-18.xls

Option Compare Database

Type TDateItems
    currentMonth As String
    currentDate As String
    currentYear2Char As String
    currentYear4Char As String
    currentFiscalMonth As String
    wsDate As String
End Type

Type TFiles
    FargoPlant As String
    Logistics As String
    PES As String
    Torreon As String
    FargoSM As String
    TorreonSM As String
End Type

Type TFilePaths
    PartMasterFilePath As String
    SupplierMasterFilePath As String
End Type

Sub OpenPulledReports(ByRef GFP As TFilePaths, ByRef GFN As TFiles)
    ' do things with strings
    Debug.Print GFP.SupplierMasterFilePath & GFN.Torreon
End Function

Function GetFilePaths(ByRef GDI As TDateItems) As TFilePaths
    Dim fp As TFilePaths
    Dim cy2c As String
    cy2c = GDI.currentYear2Char
    fp.PartMasterFilePath = "path1" & cy2c & "\" & GDI.currentMonth & "\"
    fp.SupplierMasterFilePath = "path 2" & cy2c & "\" & GDI.currentMonth & "\"
    GetFilePaths = fp
End Function

Function GetFileNames(ByRef GDI As TDateItems) As TFiles
    Dim f As TFiles
    Dim cd As String
    cd = GDI.currentDate
    f.FargoPlant = "part master for SM - blah1 - " & cd & ".xls"
    f.Logistics = "part master for SM - blah2 - " & cd & ".xls"
    f.PES = "part master for SM - blah3 - " & cd & ".xls"
    f.Torreon = "part master for SM - blah4 - " & cd & ".xls"
    f.FargoSM = "Supplier Master - blah5 - " & cd & ".xls"
    f.TorreonSM = "Supplier Master - blah6 - " & cd & ".xls"
    GetFileNames = f
End Function

Function GetDateItems() As TDateItems
    Dim d As TDateItems
    d.currentMonth = Format(Date, "mmmm") 'July
    d.currentDate = Format(Date, "mm-dd-yy") '06-09-15
    d.currentYear2Char = Format(Date, "yy") '15
    d.currentYear4Char = Format(Date, "yyyy") '2015
    d.currentFiscalMonth = Format(DateAdd("m", 1, Date), "mm") '08
    d.wsDate = currentFiscalMonth & currentYear4Char '082015
    GetDateItems = d
End Function

Answer 3

You can't actually pass functions in VBA unfortunately and OpenPulledReports() thinks it is getting passed 2 values.
So you calling OpenPulledReports(GetFilePaths(), GetFileNames()) thinks it is getting the output from the GetFilePaths() and GetFileNames() which are then getting errors since you are not passing them the proper inputs.