VBA:将函数作为参数传递

Question

我正在尝试传递一个函数(实际上是 2 个函数)作为 Sub 的参数:

调用 OpenPulledReports(GetFilePaths(), GetFileNames())

我不确定我是否需要在名称末尾使用 ()，但我收到了一个 Argument not optional 错误。

---

这是这两个函数:

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

---

我想我会包括 GetDateItems() 因为它被这两个函数引用:

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

---

我最初只是对每个 DateItem、FilePath 和 FileName 项目使用 ByRef 但决定将将它们放入自己的函数中以清理我的代码。

非常感谢您的宝贵时间。

编辑:

@Brad 我现在正在尝试使用对象而不是字符串。

我现在在 d.currentMonth = ... 行上收到“对象变量或未设置 block 变量”运行时错误

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

基于此:http://www.cpearson.com/excel/Classes.aspx ，我觉得我做错了，但我不确定如何正确地做。

Answer 1

我不确定这是一种好的编码方式，但如果您想以这种方式传递值，那么您应该使用用户定义类型。下面是一个模块，它提供与您所描述的类似的功能。

如果你运行这个:

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

你会得到这个(在调试窗口中):

路径 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