Passing a variable between modules in VBA

Hi,

I've read lots of threads on this, but for some reason I just can't get it to work and I'm not sure what I'm doing wrong.

I have two modules, each with several subroutines in them. I can pass a variable between subs in the same module, but I can't seem to pass them between modules.

I have tried declaring them as public and as global, but neither option works.

Could anyone please help me?

Thanks

Mike

Create two modules, in module1 put:

Sub macro_2()
MsgBox myvar
End Sub

in module2 put:

Public myvar As String
Sub macro_1()
myvar = "testing"
Call Module1.macro_2
End Sub

run macro_1 in module2, you should get "testing" in your msgbox - is this what you mean?

I've attached a sample workbook here.

As you will see, when you run Module1, even when passing between the two subs, the variables are kept. When it switches to another module, however, the variables are lost.

Any help would be greatly appreciated!

Thanks again

Mike

Hi Mike - your example works for me (i.e. the variables are passed from module2 to module1)

I would not use Public variable.
Module1

Sub firstRun()
    Application.ScreenUpdating = False
    Run "Module2.importOrderSubs", "ReferenceClientWorkbook_Rowan.xlsm", _
                                "trading Spreadsheet_Rowan.xlsm", _
                                "M:\Hedge Fund Management\R files\Rowan's Model\Code\2013-12-02\"
End Sub

Module2

Private Sub importOrderSubs(referenceClkientWorkbook, _
                tradingSpreadsheet, userLocation)
    Workbooks(referenceClientWorkbook).Activate
    Sheets("Pairs").Activate
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & userLocation & "Exports\orderSubs.txt", _
        Destination:=Range("$A$11"))
        .Name = "orderSubs_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Oooh, you guys are right! Sorry, I was just looking at the locals bar at the bottom and, because my variables weren't displayed there, I assumed they hadn't been passed.

Thanks!