excel - 在函数和子之间传递变量

Question

我正在尝试将一个字符串从我的主函数传递给一个子函数。
我明白了

compile error: wrong number of arguments or invalid property assignment"

代码的适用部分如下。

Function Update_Information()
    Dim Master_Workbook As String
    Master_Workbook = ActiveWorkbook.Name
    OpenFileDialog (Master_Workbook)
End Function

Sub OpenFileDialog(Master_Workbook)
    Dim Import_Workbook As String
    Import_Workbook = Application.GetOpenFilename _
      (Title:="Please choose a file to open", _
      FileFilter:="Excel Files *.csv* (*.csv*),")
    If Import_Workbook = "False" Then
        MsgBox "No file selected.", vbExclamation, "Sorry!"
        Exit Sub
    Else
        Workbooks.Open Filename:=Import_Workbook
        ActiveSheet.Copy After:=Workbooks(Master_Workbook.Sheets(Workbooks(Master_Workbook).Sheets.Count)
        ActiveSheet.Name = "Import"
        Workbooks.Close Filename:=Import_Workbook
    End If
End Sub

Answer 1

使用 Workbook变量而不是 String .

如果 Update_Information不返回任何东西，它应该是 Sub而不是 Function .

Public Sub UpdateInformation()
    OpenFileDialog MasterWorkbook:=ActiveWorkbook
End Sub

Private Sub OpenFileDialog(ByVal MasterWorkbook As Workbook)
    ...
    ActiveSheet.Copy After:=MasterWorkbook.Sheets(MasterWorkbook.Sheets.Count) 
End Sub

其他需要考虑的问题:

Workbooks.Open返回 Workbook您可以(或应该)分配给变量的引用:

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=Import_Workbook)
...
wb.Close SaveChanges:=False

(感谢@GSerg 捕捉)主要问题: Workbooks.Close 不同于 Workbook.Close .您正在尝试像后者一样使用前者(没有参数)。你想要后者。