Pass Variable After Calling Sub

Question

I have two issues.

1. Because Var3 can be blank sometimes depending on the Sub, I get a 424 error and the word 'undefined' is copied into the textbox on the website - however I need to leave it blank and not paste anything in this case.

2. I have a dropdown box in Excel to select what Sub will be called. I would like to incorporate this into an IF statement as shown below, but when I try the code, nothing happens.

Here is my attempt:

Public Sub Populate()

Dim Var1 As String
Dim Var2 As String
Dim Var3 As String    

Dim User_Name As String
Dim StrFile1 As String
Dim StrFile2 As String
Dim Filename As String
Dim strFilename As String

Dim IE As Object

If ComboBox1.Value = "Data1" Then
    Call Data1 (Filename, Var1, Var2, Var3)
End If

If ComboBox1.Value = "Data2" Then
    Call Data2 (Filename, Var1, Var2, Var3)
End If

'If ComboBox1.Value = Data3, Data4, etc...

    User_Name = Environ("UserName")
    StrFile1 = "C:\Users\"
    StrFile2 = "\Desktop\"

    strFilename = StrFile1 & User_Name & StrFile2 & Filename

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strFilename, Destination _
        :=Range("$A$22"))
        .Name = Filename
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    ActiveWindow.SmallScroll Down:=-27

    DoEvents

    'FillInternetForm
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate "https://website.com"
    IE.Visible = True
    While IE.busy
        DoEvents  'wait until IE is done loading page.
    Wend

    IE.Document.All(Var1).Value = ThisWorkbook.Sheets("Sheet1").Range("B5")
    IE.Document.All(Var2).Value = ThisWorkbook.Sheets("Sheet1").Range("B6")
    IE.Document.All(Var3).Value = ThisWorkbook.Sheets("Sheet1").Range("B7")

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub


Public Sub Data1 (ByRef Filename As String, ByRef Var1 As String, ByRef Var2 As String, ByRef Var3 As String)

    Filename = "FILE1.CSV"
    Var1 = "Response123"
    Var2 = "Response456"
    Var3 = "Response789"

End Sub

Public Sub Data2 (ByRef Filename As String, ByRef Var1 As String, ByRef Var2 As String, ByRef Var3 As String)

    Filename = "FILE2.CSV"
    Var1 = "Response987"
    Var2 = "Response654"
    Var3 = "Response321"

End Sub

'Public Sub Data3, Data4, etc...

Answer 1

This isn't how variables work, and it isn't how parameters work. As pointed out in the comments, variables declared inside procedures only have scope inside those procedures. If you want to change the value outside of the procedure you're in from a different procedure you have 2 options - either pass them ByRef:

Sub Variable1(ByRef Var As String)
    Var = "R_575031"
End Sub

'In the calling code...
Variable1 Var

...or assign them to the return value of a function:

Function Variable1() As String
    Variable1 = "R_575031"
End Function

'In the calling code...
Var = Variable1

In your case, the second makes for clearer, easier to read code.

As for using it, this line is wrong:

E.Document.All(" & strVar & ").Value = ThisWorkbook.Sheets("Sheet1").Range("B5")

You don't concatenate variables into commands. If you need to pass a parameter, pass a parameter:

E.Document.All(strVar).Value = ThisWorkbook.Sheets("Sheet1").Range("B5")
              '^^^^^^ this is a parameter of .All()

Answer 2

If you Declare (DIM) a Variable within a subroutine/function, it is only available within that subroutine/function.

You need to declare the variables publicly for them to be available to other subroutines.

Alternatively, you could rewrite the code, as follows:

Private Sub Populate()

Dim strVar As String 
Dim IE As Object

DoEvents
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Navigate "https://website.com"
    .Visible = True
    While .busy
        DoEvents  
    Wend
End With
strVar=Variable1()

IE.Document.All(" & strVar & ").Value = ThisWorkbook.Sheets("Sheet1").Range("B5")

End Sub

Private Function Variable1() AS String
    Var = "R_575031"
End Sub

By turning the routine into a function, you can return a value. Private Functions only exist in the module (eg within a form). Public Functions and subs belong in global module containers rather than in Form / Sheet containers.