Declaring a variable in a public function (Excel VBA)

Question

I have a few Public functions that are called on in order in another function... Something like this:

Public Sub func1()
  Operation 1
End Sub

Public Sub func2()
  Operation 2
End Sub

Public Sub func3()
  Operation 3
End Sub

Sub Execute()
  Call func1()
  Call func2()
  Call func3()
End Sub

What I would like to understand is if there is a common variable that I am declaring in each of these functions, something like this:

Dim LastRow as Long
LastRow = Sheets("Project_Name").Cells(Rows.count, "B").End(xlUp).row

Is it safe to assume that I can declare it once in func1() and use it in both func2() and func3()?

Or I have to declare it 3x times in each of these functions?

Thanks

You could declare once globally or 3 times locally or declare in first and pass as argument to later. Really depends on what you are doing with this variable. Also, drop the Call keyword. Not needed. — QHarr
– QHarr, Commented Jun 7, 2018 at 11:18
Can you please give me an example how you declare globally? and an example on how to declare on first and pass as argument later? — Oday Salim
– Oday Salim, Commented Jun 7, 2018 at 11:19

QHarr · Accepted Answer · 2018-06-07 11:39:33Z

4

Here is with a global variable, where you assign in func1 (though I would change these names as they are subs). Note the public variable is declared at the top of the module outside of the subs. I have changed the name of one sub to my_Execute, as Execute reminds me too much of a key word in several programming languages.

Option Explicit
Public a As Long

Public Sub func1()
    a = 1
End Sub

Public Sub func2()
    MsgBox a
End Sub

Public Sub func3()
    MsgBox a    
End Sub

Public Sub my_Execute()
    func1
    func2
    func3
End Sub

Declaring once locally and passing through all as an argument by value (ByVal). Note: Here it is declared locally in my_Execute, and then passed as an argument to the other subs.

Option Explicit

Public Sub func1(ByVal a As Long)
   MsgBox "func1 " & a
End Sub

Public Sub func2(ByVal a As Long)
    MsgBox "func2 " & a
End Sub

Public Sub func3(ByVal a As Long)
    MsgBox "func3 " & a
End Sub

Public Sub my_Execute()
    Dim a As Long
    a = 1
    func1 a
    func2 a
    func3 a
End Sub

Passing ByRef so can alter a en route:

Option Explicit
Public Sub func1(ByRef a As Long)
   a = a + 1
End Sub

Public Sub func2(ByRef a As Long)
    a = a + 2
End Sub

Public Sub func3(ByRef a As Long)
     a = a + 3
End Sub

Public Sub my_Execute()
    Dim a As Long
    a = 1
    func1 a
    func2 a
    func3 a
    MsgBox a
End Sub

Information on ByRef and ByVal here

edited Jun 7, 2018 at 11:39

answered Jun 7, 2018 at 11:21

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

QHarr Over a year ago

If you need further explanation/clarification, please let me know.

Oday Salim Over a year ago

Thanks - I think the first method (global var) is most efficient and elegant. What do you think?

QHarr Over a year ago

It really depends what you are going to do with the variable. Have a read of the following: cpearson.com/excel/scope.aspx

Collectives™ on Stack Overflow

Declaring a variable in a public function (Excel VBA)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related