• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get a variable value from another file

debxxx1

New Member
Hi Genius people,

I have 2 workbooks. Let's say one is X.xlsb and one is Y.xlsb. (X.xlsb is a production file which I can't modify means I can only modify Y.xlsb)

In X.xlsb I have a public variable

Code:
public const publicVar ="123"
How can I get the value of this public variable in Y.xlsb?

I have tried using below but it does not work.

Code:
Application.Workbooks("X.xlsb").publicVar
Please help
 

PCI

New Member
A possibility could be
In file Y.xlsb put next code
Code:
Option Explicit

Sub Test()
Const Stat = "Public Const publicVar"
Const WkF = "X.xlsb"

Dim MyVar
Dim I  As Integer, II As Integer
Dim T, TT
Dim WkC
    With Workbooks(WkF).VBProject
        For I = 1 To .VBComponents.Count
            T = .VBComponents(I).Name
            With .VBComponents(T).CodeModule
                For II = 1 To .CountOfLines
                    TT = .Lines(II, 1)
                    If (TT Like Stat & "*") Then WkC = Split(TT, "=")(1)
                Next II
            End With
        Next I
    End With
    MsgBox (" Const = " & WkC)
End Sub
 
Last edited:

debxxx1

New Member
A possibility could be
In file Y.xlsb put next code
Code:
Option Explicit

Sub Test()
Const Stat = "Public Const publicVar"
Const WkF = "X.xlsb"

Dim MyVar
Dim I  As Integer, II As Integer
Dim T, TT
Dim WkC
    With Workbooks(WkF).VBProject
        For I = 1 To .VBComponents.Count
            T = .VBComponents(I).Name
            With .VBComponents(T).CodeModule
                For II = 1 To .CountOfLines
                    TT = .Lines(II, 1)
                    If (TT Like Stat & "*") Then WkC = Split(TT, "=")(1)
                Next II
            End With
        Next I
    End With
    MsgBox (" Const = " & WkC)
End Sub
Thanks Sir, this is bit long but a very nice solution. :)
Cheers
 
Last edited:
Top