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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
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:


VBA Code:
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:

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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Firstly, you should be aware that if you write this:

VBA Code:
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

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
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:

VBA Code:
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

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
Firstly, you should be aware that if you write this:

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,205
Messages
5,640,837
Members
417,173
Latest member
Tuanphun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top