VBA Call function

Question

This code runs but I need a way to define variables x, n and c only once, as say module-wide variables. The example provided is a condensed version of a bigger macro that ‘Calls’ many procedures.

Presently I have to put the variables into each Sub for it to run as required. I tried declaring them upfront using Public, Private, Dim etc but the n and c variables don't reset to 0 after each Sub is run. Thus the variable count continues on to the following procedure from the previous & I need the counter to reset after each sub has run. The x = "Minimum" outside the Subs (i.e. with declared variables) doesn't work either but would be useful for efficiency.

Also:

How to simplify the Main macro that uses Call. Sometimes I have 50 Subs.
Can one consolidate this particular type of Sub (the 2 here are identical in that I send data to the same worksheet 'Rs' but just from 2 different worksheets, 'Ts' & 'BR', in the same excel file)

Dim module As Object

Sub Main()
Call Stic1
Call Stic2
End Sub

Sub Stic1()
Dim x As String
Dim n As Integer, c As Integer
x = "Minimum"
Sheets("Ts").Select
Range("A2").Offset(0, 0).Select
Do Until IsEmpty(ActiveCell)
If ActiveCell.Value = x Then
    ActiveCell.Offset(0, 1).Copy
    Application.Goto (ActiveWorkbook.Sheets("Rs").Range("G2").Offset(n, 0))
    ActiveSheet.Paste
    Application.CutCopyMode = False
    n = n + 1
    Application.Goto (ActiveWorkbook.Sheets("Ts").Range("A2").Offset(c, 0))
    c = c + 1
Else
    Application.Goto (ActiveWorkbook.Sheets("Ts").Range("A2").Offset(c, 0))
    c = c + 1
End If
Application.Goto (ActiveWorkbook.Sheets("Ts").Range("A2").Offset(c, 0))
Loop
End Sub

Sub Stic2()
Dim x As String
Dim n As Integer, c As Integer
x = "Minimum"
Sheets("BR").Select
Range("A2").Offset(0, 0).Select
Do Until IsEmpty(ActiveCell)
If ActiveCell.Value = x Then
    ActiveCell.Offset(0, 1).Copy
    Application.Goto (ActiveWorkbook.Sheets("Rs").Range("I2").Offset(n, 0))
    ActiveSheet.Paste
    Application.CutCopyMode = False
    n = n + 1
    Application.Goto (ActiveWorkbook.Sheets("BR").Range("A2").Offset(c, 0))
    c = c + 1
Else
    Application.Goto (ActiveWorkbook.Sheets("BR").Range("A2").Offset(c, 0))
    c = c + 1
End If
Application.Goto (ActiveWorkbook.Sheets("BR").Range("A2").Offset(c, 0))
Loop
End Sub

The `I need a way to define variables x, n and c only once, as say module-wide variables` and `but the n and c variables don't reset to 0 after each Sub is run` are mutually exclusive. If you want global variables, they are going to preserve their contents between calls. If you want local variables that are purged after each call, you have to declare them as local each time. Not sure why you are even trying to have global variables - only to save keystrokes when typing them into each sub, even at the price of introducing unnecessary dependencies between otherwise unrelated procedures? — GSerg, Jun 14 '16 at 17:25
On an unrelated note, you might like reading http://stackoverflow.com/q/10714251/11683. — GSerg, Jun 14 '16 at 17:32
GSerg thankyou. Yes I'm trying to reduce the size of my macros for ease of QA. My modules some times have around 50 Subs so was hoping to avoid repeating those variable so many times. Sounds like I can't. — Nui, Jun 19 '16 at 20:52

Sgdva · Answer 1 · 2016-06-20T04:14:10.380

Perhaps you are trying to mimic Static behavior for 'x' 'n' and 'c' within their modules? That could save you time instead of declaring them as Public
Static variables retain their value (contrary to Dim ones) even sub is ended (however, they reset if you debug and then do a stop), you may combine Public Static as well.
EDIT:
Integer is "deprecated", you may google Integer vs Long to see why.

Dim module As Object
Public x As String
Public n As Long
Public c As Long
Sub Main()
Call Stic1
Call Stic2
End Sub

Sub Stic1()
Call ResetVariables
x = "Minimum"
Sheets("Ts").Select
Range("A2").Offset(0, 0).Select
....
Sub ResetVariables
x = vbNullString
n = 0
c = 0
End Sub

There's no way to simplify a "call", however, for what can I read, the thing is that you have "mirror subs" here, you may want to take a look at this.
If the sheet is variable, why not?

Sub Stic(WhatSheet as String)
Dim x as String: x = "Minimum"
Sheets(WhatSheet).Select
...
'then 
Sub Main()
Call Stic("Ts")
Call Stic("BR")
....

You'd have only a sub (instead of 50 for each sheet) where the variable place to perform is the sheet (everything is the same just the sheet is the one variable). In your main yes there's no other way than to call each sheet (or if you have something defined for what sheets are going to be performed and which sheets not you can do a For cycle)

Sub Main
Dim WSheetInBook As Worksheet
For Each WSheetInBook In ActiveWorkbook.Worksheets
Call stic(WSheetInBook)
Next WSheetInBook
End Sub

Sub stic(WhatSheet As Worksheet)
x = "Minimum"
WhatSheet.Select
Range("A2").Offset(0, 0).Select
...

PS: As GSerg stated, you may want to focus in avoiding selects after you have understood this, that's so much time consuming for the macro.

Sgdva thankyou. Maybe I misunderstand but what I'm trying to do is have the variables n & c for example defined once before any Sub or Function is defined. Then when a given Sub uses them they are reset. Hence they are 0 at the beginning of each Sub that uses them. This would save me defining them in each Sub. I would only have to define them once at the very beginning of the macro, say directly under Dim module As Object. — Nui, Jun 19 '16 at 21:15
I don't understand why'd you need to do that, that will take more time and memory, nevertheless if I had to, I'd do something like my EDIT for that purpose — Sgdva, Jun 20 '16 at 04:02

VBA Call function

1 Answers1