Procedure too large, now I need to split my code.. how do I keep variables public?

[RockandGrohl]

Hi guys, I have a report that has about 14 tabs in it. By repeating the template code from one tab to another I've gone over the 64kb limit (seriously, wtf VBA..) anyway

I have some set up that's done in the reporting module, and then it switched to each of the tabs, built them and moved on to the next tab. It's clear that I need the tab-building to be done in separate modules, but the problem is I don't want to declare the tab and workbook names in every module.

If I set up my tabs like this:

Private Sub RunReport_Click()

Dim t3, bd As Worksheet
Dim Lastrow, LastrowBD, Lastcol As Long
Dim temp As Worksheet
Dim wbyr As String


Set t3 = Worksheets("T3 Data")
Set bd = Worksheets("Booking Database")
Set comm = Worksheets("Comm Matrix")
Set weksum = Worksheets("Weekly")
Set monsum = Worksheets("Monthly")
Set consum = Worksheets("Contract")

wbyr = 2021
tdate = Int(CDbl(DateValue(Date)))

And other workbooks are opened like this:

Dim bsam, am, af1, af2 As Workbook
Dim al, pd As Worksheet

For Each wbk In Workbooks
    If wbk.Name = "Budget Sales Allocation Master.xlsm" Then
    wbk.Activate
        If wbk.ReadOnly = False Then
        wbk.Close True
        Else
        wbk.Close False
        End If
    End If
Next
Application.ScreenUpdating = False
Set bsam = Workbooks.Open("\\chw-dc03\company\Sales\Reporting\Budget\Budget Sales Allocation Master.xlsm", False, True)
Application.ScreenUpdating = True

Then how do I keep that consistent across procedures?

Currently, my report building is set up something like this:

weksum.activate

'delete existing results here

' build new lines here

' insert formulas here

' format everything



' then do the next one

In that little block, I might have to reference something like "bsam" or "af1" or "bd" which can be on the original workbook or a different workbook entirely..

So how do I do that? Thanks.

Worryingly, I have a lot of variables:

LastrowBD, Lastrowtemp, Lastcol, wbyr, tdate, t3, bd, af1, af2, bsam, am, comm, weksum, monsum, consum, refsum, feasum, namsum, tcatsum, catsum, depsum, dursum, ltsum

 

[RoryA]

Firstly, you should be aware that if you write this:

Dim bsam, am, af1, af2 As Workbook

only af2 is declared as a Workbook. The other variables are all Variant. You have to specify the type for each variable.

You haven't really given enough detail to say for sure what the best course of action is in your case, but as a general rule you should avoid public variables as far as possible. Whenever possible you should aim to pass variables directly from one routine to another. You should also refactor your code whenever possible - i.e. avoid repeating similar code (which may be what you mean by "template code"). Instead make a generic routine that takes arguments and then manipulates them as appropriate.

 

[RockandGrohl]

Figured it out.

In summary:

Removed the whole subroutine from the control panel userform, so now when I hit "update report" it calls "update.UpdateReport"

The reason for this is I don't want every sub in the userform having a shared public name for stuff.

So now it opens a new Module called Update and runs UpdateReport macro, and that has a huge public declared variable list at the top:

Public temp, t3, bd, comm, weksum, monsum, consum, refsum, feasum, namsum, tcatsum, catsum, depsum, dursum, ltsum, pd As Worksheet
Public bsam, am, af1, af2 As Workbook
Public Lastrow, LastrowBD, LastrowTemp, LastCol, LastrowAF1, LastrowAF2 As Long
Public wbyr As String

And now I can say "call mod_weksum"

and it will go to mod_weksum and be able to use t3, or wbyr, or Lastrow without me needing to declare them again. Fantastic.

 

[RockandGrohl]

Firstly, you should be aware that if you write this:

Dim bsam, am, af1, af2 As Workbook

only af2 is declared as a Workbook. The other variables are all Variant. You have to specify the type for each variable.

You haven't really given enough detail to say for sure what the best course of action is in your case, but as a general rule you should avoid public variables as far as possible. Whenever possible you should aim to pass variables directly from one routine to another. You should also refactor your code whenever possible - i.e. avoid repeating similar code (which may be what you mean by "template code"). Instead make a generic routine that takes arguments and then manipulates them as appropriate.

Hi Rory.

No ****, **** I've been doing that for years, does it really not count them all as Workbook? I have to write dim bsam as workbook, dim am as workbook over and over?


I wanted to make some variables public, but was aware and cautious not to do this across the whole workbook, so I've split off the whole piece of code that builds the report into its own contained module and from there, specifically within that module, I am declaring public variables and passing them around generally.

 

[RoryA]

I have to write dim bsam as workbook, dim am as workbook over and over?

Yes, you do.

If you're declaring public variables, you're probably not "passing them around". Passing, in VBA context, means that you pass a variable directly as an argument, not that you are using the same public variable in more than one place.

 

[RockandGrohl]

Ah I see I see. So passing is when you write in the brackets the variables you want to pass, so (wbyr as string)... And that's written in the call function? So "Call mod_weksum(wbyr as string)"?

And a public variable means you don't need to pass directly, but you'd be in trouble if you had two different sheets referenced as AF1?

Cheers.

 

[RoryA]

So "Call mod_weksum(wbyr as string)"?

Remove the As String, but otherwise yes.

Public variables are available to all routines in that project but are dangerous for that very reason - they can be set/destroyed from any code, which can make errors very hard to debug. Also, any unhandled error in your code can cause a state loss which will reset all your variables, including public ones.