[Solved] VBA: Global Variables

  • I haven't worked much with global/public variables but I'm having problems in getting different parts of my program to share data.


    I've created a program that searches for people's names on one spreadsheet and assignes the name to a variable. After doing that, it calls a function but I can't pass the name that's stored in the variable to the function. I would like to know how to declare a global/public variable so when it calls a function, it can pass the value.
    ----------------------------------------------------
    Sub DoThis()


    Global strName As String


    strName = Range("A1").Address
    DoThat


    End Sub
    ----------------------------------------------------
    Function DoThat()


    MsgBox "Name: " & strName


    End Function
    ----------------------------------------------------

  • This might get you started please give this a test and I will try to make the address return to the active cell if this one works ok


    Kindest possible regards


    Jack in the UK


    Code


    Global srtName As String


    Function DoThat()
    Dim myRange As Range
    Set myRange = Range("A1")
    strName = myRange.Address
    MsgBox "Name: " & strName
    End Function

  • Beter still this will return exactly as you ask, let me know if this is OK?


    Jack


    Code:


    Global srtName As String
    Global myStr As String


    Sub myReturnStr()
    DoThatAgain


    End Sub



    ' If you want also the Address to return to a cell
    ' ie the function cell locatation TRY !!!


    Function DoThatAgain()
    Dim myRange As Range
    Set myRange = Range("A1")
    strName = myRange.Address
    MsgBox "Name: " & strName
    myStr = "Name: " & strName
    ActiveCell.Value = myStr
    End Function

  • Hi Peakkaboo,


    These mods should allow you to pass a variable into the function.


    ----------------------------------------------------
    Sub DoThis()


    Dim strName As String


    strName = Range("A1").Address
    DoThat strName


    End Sub
    ----------------------------------------------------
    Function DoThat(Name as string)


    MsgBox "Name: " & Name


    End Function
    ----------------------------------------------------


    With regards to scope and declaring variables as public.
    This is done at the top of the coding area, outside of any subroutine or function.


    Cheers
    Andy

  • Heh Andy my friend, long time no chat i tried to call you ermm deleted you from my contacts, sorry mate email my private work account with the number if you can.,


    BTW good call on the direction re Const, Global and Private Public Const, i missed saying that one!



    Jack

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!