VBA Assign ActiveWorkBook.name to Global / Public Variable to use in other Subs

[MixedUpExcel]

Hi,

I'm struggling to find the answer.. probably because the way I'm wording the search.

I have a Main Sub Routine which in turn Calls various smaller Sub Routines.

in the 1st Sub I assign a name to the active 'main' workbook eg.

Dim workbooknamemain As String


    workbooknamemain = ActiveWorkbook.Name

then through the course of running the various Sub Routines, I have activated another workbook - done something to that and then need to reactivate the 'main' workbook again.

I'm now in a different Sub Routine than the one that I assigned 'workbooknamemain' to the Main Workbook.

I want 'workbooknamemain' to work in all sub routines after I've assigned the main workbook to it.

I've tried: Public workbooknamemain' As String above all Sub Routines in the Module (right at the top) but that didn't work.

What's the best way to do this please?

Thanks.

 

[Fluff]

I've tried: Public workbooknamemain' As String above all Sub Routines in the Module (right at the top) but that didn't work.

Did you also remove the Dim statement from the macro?
Also is the module a standard module, or a sheet module?

 

[MixedUpExcel]

Did you also remove the Dim statement from the macro?
Also is the module a standard module, or a sheet module?

Hi Fluff,

Honestly can't tell you if I had left the DIM in the module or not when I had created the Public reference above. I have been playing around with different set ups and couldn't get any to do what I wanted.

Happy to follow some direction to get this working though

Regarding the Module - this is just a Standard Module that I created using Insert>Module - so not a worksheet module.

I've also found through looking online since my original post that I can supposedly pass Variables to other Sub Routines by referencing the Variable after the name of the Sub Routine eg. Sub Testing(workbooknamemain) but I've not figured out how that works just yet.

So I guess I'm looking at both options.. which ever can get the job done for me.

Thanks.

Simon

 

[MixedUpExcel]

Hi Fluff,

Based on the question you asked about whether I had the DIM in the Sub Routine made me think that it might not be needed if I'm declaring the Public at the top of the Module. I also just read that declaring a DIM in a Sub Routine limits that to just that Sub.

So I tried:

Public sheetname As String

Sub test1()


   sheetname = ActiveSheet.Name
   
Call hellotest


End Sub


Sub hellotest()


MsgBox sheetname


End Sub

That seemed to work.. so on Monday I'll try with my main workbook (when I'm back in the office) and see if I can iron out the kinks.

Thank you for your question.. looks like it might be putting me on the right track.

Simon