Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Global Workbook variable in VBA (Ex2003)

    I have a global variable in a workbook. How do I get its value from VBA code in an add-in?

    Suppose the variable is called "Test" and the value is 1

    If it's the same WB (which it is not), I seem to only have success with "[Test].value", any construction with "Range" seems to fail.

    The "[Test].value" does not work however if the variable is in another WB then where my code runs, I need to know if "Test" exists in the activeWB and what it's value is.

    This must be simple but I don't see it (yet...). Sugestions?

    Thanks!

    EJ

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Global Workbook variable in VBA (Ex2003)

    You can create a public function in the workbook where Test is defined:

    Public Function GetTest()
    GetTest = Test
    End Function

    and call it like this from another workbook:

    Debug.Print Application.Run("WorkbookName.xls!GetTest")

    where WorkbookName.xls is the name of the workbook in which Test and GetTest are defined.

    A simpler solution is to store the value of the variable in a cell in a worksheet.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Global Workbook variable in VBA (Ex2003)

    Ooe that's complex... What if I cannot have VBA code in the sheet?

    Basically I am building an Add-in that supports some UDFs.

    As these are alternatives to some standard Excel functions, I don't want to change the function arguments (not even optional ones).

    In stead I'd like to user to just create a new WB and use my functions and STILL allow some control over options. I thought that (s)he could use a named-range but that would be sheet-specific. So I figured why not make a global variable then? Now my problem would be to get this variable and its value in my XLA VBA code...

    Think this is sort of a repeat of a question I asked last week to; I solved that but now I do need it again... sorry

    Hope this is more clear now.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Global Workbook variable in VBA (Ex2003)

    I don't understand - how can you define a global variable if you can't have VBA code?

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Global Workbook variable in VBA (Ex2003)

    Maybe I'm using the wrong names.

    In a sheet:

    Insert / Name / Define...

    In the top bar type a name e.g. Test

    In the "Refers to:", replace the sheet & cell default with e.g. 1

    Click "Add"

    I thought this was a variable that was global to a sheet...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Global Workbook variable in VBA (Ex2003)

    That's not a variable, it's a defined name. Variables "live" in VBA, defined names "live" in the workbook itself. To refer to a defined name from another workbook in a formula, use the syntax

    ='Workbookname.xls'!Definedname

    where Workbookname.xls is the name of the other workbook, and Definedname is the defined name.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Global Workbook variable in VBA (Ex2003)

    And if you want to use VBA, assuming that the "other" workbook is the active workbook:

    MsgBox ActiveWorkbook.Names("Test").Value

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •