Can I share a Public Const across multiple workbooks?

[SliderJeff]

Hey gang,

I've run into an issue which would be easily solved with a #include of a .H file in C, but which I can't figure out how to do in VBA. Here's what I have.

Script.XLSM

Microsoft Excel Objects​

Sheet1 -> Contains a key macro to be copied into the Output.XLSM file on Sheet1​

ThisWorkbook -> No content​

Modules​

Module1 -> Contains my main script with subs and functions​

Output.XLSM

Microsoft Excel Objects​

Sheet1 -> Contains a copied version of the macro stored in Script.XLSM("Sheet1")​

ThisWorkbook -> No content​

Modules​

None

​

So, inside Module1 of Script.XLSM, I have a constant declared as, for example:

Public const MyConstant As 23

I need to use/reference that same constant in Sheet1 of Script.XLSM.

Can someone lend a hand as to how to accomplish this? Thanks!

Regards,
Jeff

 

[Domenic]

If you're copying the macro from Script.xlsm, can't you copy the declaration line for your constant as well? Otherwise, within Output.xlsm, you can set a reference to Script.xlsm...

VBE >> Tools >> Reference

...and select the project name for Script.xlsm. If the workbook is closed, you can use the Browse button to select it. Note, initially the project is named VBAProject. So, if you haven't already done so, you can change the name as follows...

VBE >> Tools >> VBAProject Properties

Once a reference has been established, you can refer to the constant as follows...

MyConst

Or, alternatively, assuming that you've named your project MyScript...

MyScript.Module1.MyConst

By the way, you should declare you constant as follows...

Public Const MyConst As Integer = 23

 

[SliderJeff]

Thanks, Domenic. Sorry about typing in the declaration incorrectly here on the forum. I actually defined them correctly in my file as you indicated.

The issue is that the Script.XLSM file actually CREATES Output.XLSM, so I can't just create a reference to Script.XLSM from Output.XLSM since technically it doesn't exist until Script.XLSM is run. Does that make sense?

The Google searching and searching here on the forum that I've done pointed me to this thread:
http://www.mrexcel.com/forum/excel-...ther-workbooks-visual-basic-applications.html

I was loathe to post in that thread though, since I don't know what the general feel is on reviving necro-threads from 8 years ago.

Perhaps if I explain better what I have going on, it may give a better indication of what I can/should do?

Currently, this is what I have.

Script.XLSM - Previously written file with all of the code to generate Output.XLSM

Module1 = Contains the bulk of my code, including the following const global variables which I need to use across the various code in Sheet1, Module1, and Output.XLSM("Sheet1") (once it's generated)

' Global Constants
Public Const FILE_OFFSET As Integer = 23
Public Const NUM_PARAMS As Integer = 6
Public Const FILENAME_COL As Integer = 1

Sheet1 = Contains a couple uses of the constants I have defined in Script.XLSM("Sheet1").Module1

Set SortRange = Range(Cells(FILE_OFFSET, FILENAME_COL), Cells(LastRow, LastColumn))

Output.XLSM - Dynamically generated output file created from code written in Script.XLSM

Sheet1 - Contains a copied over version of all the code inside Script.XLSM("Sheet1"). So it too needs access to the same globals defined in Script.XLSM("Sheet1").Module1

Set SortRange = Range(Cells(FILE_OFFSET, FILENAME_COL), Cells(LastRow, LastColumn))

Does this help provide any more clarity as to what I'm doing?

Perhaps if I instead define all of the constants in the Sheet which getting copied into the output, and then somehow reference those constants from Script.XLSM.Module1, that might be a better way to do it? I'm not sure how to do that, though.

Thanks,
Jeff

 

[Domenic]

When you say that the output file contains all the code copied from your script file, are you using VBA code to copy the code? If so, can't you include those lines declaring your constants in your code?

If for some reason you would rather add a reference to your script file, you can do it as follows...

    wb.VBProject.References.AddFromFile ThisWorkbook.FullName

...where wb refers to your output file. Note that you'll need to allow access to the VBA Project.

 

[SliderJeff]

Domenic,

Thanks again for keeping an eye on this. I did just solve my issue using the info from Tom Schreiner in the thread I referenced in my original post, as well as the idea of copying different parts of macro files into my output XLSM file using my VBA code.

So, in the interest of hopefully helping someone in the future, here's what I did to solve my issue of sharing global constants across multiple files and workbooks.

Again, the files, modules, sheets, and objects involved are as follows.

Source XLSM file with most of my VBA code which generates an Output.XLSM file which contains certain key subs copied over from the Source.XLSM file.

Define all of your global constants to be used inside Source.XLSM("ThisWorkbook") object as:

' Global constants
Private Const FILE_OFFSET As Integer = 23
Private Const FILENAME_COL As Integer = 1

etc...

Directly beneath that code, create functions which return those constants as:

Public Property Get GetFileOffset() As Integer
    GetFileOffset = FILE_OFFSET
End Property
Public Property Get GetNumParams() As Integer
    GetNumParams = NUM_PARAMS
End Property

etc...

Now, everywhere you need to reference those globals in your Source.XLSM modules, invoke the functions you just defined as, for instance:

Workbooks("Source.xlsm").GetFileOffset)

As for my VBA code which copies certain lines from two different sheets in the source file to a specific sheet in the output file, here's what I did.

Sub ImportMacro(ByRef wbSource As Workbook, ByRef wbDestination As Workbook)
Dim src As CodeModule, dest As CodeModule
' Copy the global constants over into the output workbook for use in its double-clicking macro
Set src = wbSource.VBProject.VBComponents("ThisWorkbook").CodeModule
Set dest = wbDestination.VBProject.VBComponents("Sheet1").CodeModule
dest.DeleteLines 1, dest.CountOfLines
dest.AddFromString src.Lines(1, Workbooks("Source.xlsm").GetNumLinesToCopy)
' Copy over the double-clicking macro into the output workbook
Set src = wbSource.VBProject.VBComponents("Sheet1").CodeModule
dest.AddFromString src.Lines(1, src.CountOfLines)
End Sub

Hope that helps someone. Thanks again.

Regards,
Jeff

 

[Domenic]

If you're copying the global constants to your output workbook, why are you using Property Get procedures to reference them from the source workbook?

For what it's worth, in your solution, you can use a single Property Get procedure that accepts a string argument, along with Select Case to retrieve the correct constant...

Private Const FILE_OFFSET As Integer = 23
Private Const FILENAME_COL As Integer = 1

Public Property Get GetConstant(s As String) As Integer
    Select Case UCase(s)
        Case "FILE_OFFSET"
            GetConstant = FILE_OFFSET
        Case "FILENAME_COL"
            GetConstant = FILENAME_COL
        'etc
        '
        '
    End Select
End Property

Then you can retrieve the desired constant as follows...

MsgBox Workbooks("Source.xlsm").GetConstant("FILE_OFFSET")

 

[SliderJeff]

Good call, Domenic. If I hadn't already globally searched and replaced all the code with the brute force way I did it, I'd amend it with your method for ease of use.

I had to use the Property Get procedures to share the globals between Module1 and Sheet1 of Source.XLSM, not just between Source.XLSM and Output.XLSM, if that makes sense.

Regs,
Jeff

 

[Domenic]

I had to use the Property Get procedures to share the globals between Module1 and Sheet1 of Source.XLSM, not just between Source.XLSM and Output.XLSM, if that makes sense.

Oh I see... then it looks like you're all set... that's great.

Cheers!

 

[SliderJeff]

Many thanks, Domenic! I also replied to that thread I referenced above and put your efficient code solution for the Get procedures in there in case someone else needs to know the cleanest way to do that.

Regs,
Jeff

 

[Domenic]

Many thanks, Domenic! I also replied to that thread I referenced above and put your efficient code solution for the Get procedures in there in case someone else needs to know the cleanest way to do that.

Regs,
Jeff

Cool . . .