Calling Subs with variables that are set

[sjk1193]

I have the following 2 subroutines and I'm getting a runtime error "object required" when I am calling "renamesecurities". I think the problem is coming from which Sub i need to define the variables in, but am not sure. Maybe the problem is I need to define more variables?

Sub format()
' Defines variables
Dim InputFile As Workbook
Dim OutputFile As Workbook
' Open input / output workbooks:
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        .InitialFileName = "S:\PQfolders\"
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show
        
        'Store in filepath variable
        filepath = .SelectedItems.Item(1)
    End With
    
Set InputFile = Workbooks.Open(filepath)
Set OutputFile = ThisWorkbook


Call renamesecurities
End Sub

Sub renamesecurities()
   Dim Cl As Range, Fnd As Range
   Dim Dic As Object
   Set Dic = CreateObject("Scripting.dictionary")
   
   With OutputFile.Sheets("INDEX")
      For Each Cl In .Range("b2", .Range("b" & Rows.Count).End(xlUp))
         Dic.Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
   End With
   
   With InputFile.Sheets(2)
      Set Fnd = .Range("1:1").Find("Security Description", , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then Exit Sub
        For Each Cl In .Range(Fnd.Offset(1), .Cells(Rows.Count, Fnd.Column).End(xlUp))
           Cl.Value = Dic.Item(Cl.Value)
        Next Cl
   End With


End Sub

 

[mikerickson]

The problem variable is OutputFile.

It is declared in Sub format and scoped only to that proceedure. When it gets to that line in renamesecurities, that OutputFile has not been set.

One way to do that would be to pass it as an optional argument

Sub RenameSecurities(Optional OutputFile as Workbook)
    If OutputFile Is Nothing then Set OutputFile = ThisWorkbook: Rem make ThisWorkbook default value

    ' your code
End Sub

 

[Fluff]

One option would be to change the Call procedure to

Call renamesecurities(InputFile, OutputFile)

And change the sub to

Sub renamesecurities(InputFile As Workbook, OutputFile As Workbook)