VBA Best Practices: working with small subs and functions, passing values between routines

Question

I learned from internet that our vba macros (subs and functions) should be more or less the size of the screen.

But all my code is written exactly in the opposite: a have a huuuge main sub that calls functions and small subs.

My question is: if I decide to convert my main huge sub into small subs (sub1, sub2, sub3, etc), then what should be the best way to keep the value of a variable that was assigned in sub1 and use it in sub2, that was assigned in sub2 and use it in sub3, and so on? If I have no answer to this question, I am going to save those values in a sheet, but maybe this way is going to cause me trouble that I cannot see by now. Thanks for any help.

Answer 1

A Sub or a Function should have a single and clear purpose, told by its name. If you break down your code in such chunks, you will know what information those methods will need and deliver. At the same time they will become smaller. If you have to choose between size and clarity, go for clarity.
Do NOT put your values in a sheet. That is just a different and less efficient way of using global variables, which is something you should avoid (not at all cost, but almost).

Answer 2

Both methods below would work. In my personal opinion, method 1 keeps code more simple (you avoid to pass big bunches of parameters to each macro) but depending on your need you might choose one or the other.

Global variables

Your code would look something like this:

'Global variables declaration
Dim a As Integer
Dim b As Integer
'
Public Sub mainRoutine()
    setA '<-- call first macro
    setB '<-- call second macro
    MsgBox a + b '<-- show A + B (2 + 3 = 5) 
End Sub
'code of sub-routines
Private Sub setA()
    a = 2 '<-- since a is global, it will keep the value
End Sub
Private Sub setB()
    b = 3 '<-- since b is global, it will keep the value
End Sub

Please note: global means that the value of this variable will live for all execution of your program. Memory will be released once the program ends.

Pass variables as ByRef parameters

Public Sub mainRoutine()
    Dim a As Integer '<-- a is local
    a = 2 '<-- setting a to 2
    setA a '<-- passing a as ByRef parameter 
    MsgBox a '<-- a is now 3
End Sub
Private Sub setA(ByRef a As Integer)
    a = 3 '<-- setting a as 3. 
End Sub

Of course, the above method would only allow to keep a = 2/3 during the execution of mainRoutine. But if at some point you execute another macro called by another stack (for example another button in the spreadsheet), you wouldn't be able to access the value of a as you would in method 1.

Important: no, variables on the spreadsheet is not a good idea, unless you don’t need to keep the value after closing and reopening the spreadsheet (in that case you would be using the spreadsheet as a sort of database).

Answer 3

VBA can be considered as an object oriented programming language (OOP).It has 3 of the 4 pillars of OOP:

• Abstraction
• Encapsulation
• Polymorphism

This is discussed in Is VBA an OOP language, and does it support polymorphism?

OOP means understanding the SOLID principles.

• Single Responsibility Principle
• Open Closed Principle
• Liskov Substitution Principle
• Interface Segregation Principle
• Dependency Inversion Principle

Best practice is to adhere to these principles and the first is the Single Responsibility Principle.

This states that your functions/subs should have one responsibility and focus on doing one thing.

Advantages (amongst others):

The reduced code complexity and dependencies mean:

1) Easier to debug as your track down which sub/function the error is raised in

2) Units tests can be more easily be built around code that is based on single responsibility.

3) Can be more easily slotted into a wider code base

To use variables between subs/functions consider:

1) Either public variables, and/or

2) Passing variables as arguments

Other info:

OOP VBA pt.1: Debunking Stuff