How do I accessing other workbook VBA public variables

I do not agree with HansV, because this early binding makes problems.

If your public variables are in class modules you can access them directly:

If we assume you have this code in the module "ThisWorkBook":

--- schnipp ---
Option Explicit

Public Data

Private Sub Workbook_Open()
  Data = "ThisWorkbook"
  Sheets(1).Data = "Sheet1"
End Sub
--- schnapp ---

and this code in the first sheet:

--- schnipp ---
Public Data
--- schnapp ---

and you has saved this workbook as "Mappe1.xls", then you can access this variables from a second workbook like this way:

Sub Test()
  'Access "ThisWorkbook"
  Debug.Print Workbooks("Mappe1.xls").Sheets(1).Parent.Data
  'Access "Sheet(1)"
  Debug.Print Workbooks("Mappe1.xls").Sheets(1).Data
End Sub

Andreas.

Addendum:

AFAIK it is not possible to access public variables in normal modules from other workbooks directly, but it is possible to execute a function in the other workbook to get the variable indirectly:

If we add this function to "Mappe1.xls" in the module "ThisWorkbook":

Function RequestData(Name As String) As Variant
  Select Case Name
    Case "Modul1"
       RequestData = Modul1.Data
    Case "Modul2"
       RequestData = Modul2.Data
  End Select
End Function

and add also 2 normal modules named "Modul1" and "Modul2" with the line "Public Data" in it, we can access them from a second workbook in this way:

  'Modul1
  Debug.Print .Sheets(1).Parent.RequestData("Modul1")
  'Modul2
  Debug.Print .Sheets(1).Parent.RequestData("Modul2")

Andreas.