Usage of global variables and local variables in different subs

Question

I'm copy pasting stuff from Excel to PowerPoint using VBA. It's working, but I need to clean up my code. The problem is I have no clue what is good practice when it comes to variables (or objects) that are used in more than one procedure. I think I lack some fundamental understanding in this regard.

When passing a variable from one procedure to another, is it a bad idea to use the same name in both procedures? So for example
```
Sub 1()
    Dim a As Long, b As Long
    Call Sub2(a, b)
    '...
End Sub

Sub2(a As Long, b As Long)
    '...
End Sub
```
Should Sub2 call the variables differently, for example x and y? If not, I'm coming back to question 1): What's the reason to pass these variables directly from Sub1 to Sub2 and not declare them globally? I get it when I intend to use the original value of a and b in Sub1 after calling Sub2 (so basically pass it as ByVal to Sub2), but in my situation that's not the case.
Is there a reason to limit the usage of global variables? I left them in my code as local, but should I define lRowAn, lRowData etc. globally?

When should I pass a variable from a sub to another? In my code below, to do this with iSlides makes sense to me, but not for wsEm.

The following is part of my actual code. The Subs EmData and EmDataAn are very similar and I'll see if I can merge them, but they illustrate very well the issue I have because they use many of the same variables.

Public mySlide As PowerPoint.Slide
Public PowerPointApp As PowerPoint.Application
Public myPresentation As PowerPoint.Presentation
Public MonatNum As String, JahrNum As String, MonatStr As String

Sub CreateReport()    
    Dim DestinationPPT As String
    Dim iSlides As Integer
    Dim fRowAn As Long, lRowAn As Long, lRowData As Long
    Dim wbEm As Workbook
    Dim wsEm As Worksheet

    Set PowerPointApp = New PowerPoint.Application
    DestinationPPT = "C:\VBA\ReportTemplate.pptm"
    Set myPresentation = PowerPointApp.Presentations.Open(DestinationPPT)

    Set wbEm = Workbooks.Open("C:\VBA\Report.xlsx")
    Set wsEm = wbEm.Sheets("Sheet1")

    lRowAn = wsEm.Cells(Rows.Count, 3).End(xlUp).Row
    fRowAn = wsEm.Cells(Rows.Count, 3).End(xlUp).End(xlUp).Row + 1

    If lRowAn >= 127 Then
        If lRowData <= 127 Then '4 Slides, but separate Annotations from Data
            iSlides = 1
            Call EmData(wsEm, iSlides)
            Call EmDataAn(wsEm, iSlides)
        Else '4 Slides
            iSlides = 3
            Call EmData(wsEm, iSlides)
        End If
    Else '3 Slides
        Call EmData(wsEm, iSlides)
    End If

    Application.DisplayAlerts = False
    wbEm.Close SaveChanges:=False
    Application.DisplayAlerts = True

    PowerPointApp.Visible = True
    PowerPointApp.Activate
    Application.CutCopyMode = False
End Sub



Sub EmData(wsEm As Worksheet, iSlides As Integer)
    Dim i As Integer
    Dim fRowDataCalc As Long, lRowDataCalc As Long, lRowCopy As Long
    Dim rowHght As Long
    Dim rng As Range

    For i = 0 To iSlides
        fRowDataCalc = 4 + 40 * i + i * 1
        lRowDataCalc = 4 + 40 * (i + 1) + i * 1

        With wsEm
            .Range("B2:K3").Copy .Range("B500")
            .Range("B" & fRowDataCalc & ":K" & lRowDataCalc).Copy .Range("B502")
            rowHght = .Range("B3").EntireRow.Height
            .Range("B501").RowHeight = rowHght
            lRowCopy = .Cells(Rows.Count, "C").End(xlUp).Row
            Set rng = .Range("B500:K" & lRowCopy)
        End With

        Set mySlide = myPresentation.Slides.AddSlide(myPresentation.Slides.Count + 1, PPLayout("LayoutEmittenten"))
        mySlide.Shapes.Placeholders(1).TextFrame.TextRange.Text = "Headline (" & i + 1 & ")"
        Call PasteEm(mySlide, rng)
        rng.Clear
    Next i

End Sub

Sub EmDataAn(wsEm As Worksheet, iSlides As Integer)
    Dim lRowAn As Long, fRowAn As Long, lRowData As Long, fRowDataCalc As Long, lRowDataCalc As Long
    Dim rng As Range
    Dim rowHght As Long, lRowCopy As Long

    lRowAn = wsEm.Cells(Rows.Count, 3).End(xlUp).Row
    fRowAn = wsEm.Cells(Rows.Count, 3).End(xlUp).End(xlUp).Row - 1
    lRowData = wsEm.Cells(Rows.Count, 10).End(xlUp).Row

    iSlides = iSlides + 1
    fRowDataCalc = 4 + 40 * iSlides + iSlides * 1
    lRowDataCalc = lRowData

    'Last sheet with data
    With wsEm
        .Range("B2:K3").Copy .Range("B500")
        .Range("B" & fRowDataCalc & ":K" & lRowDataCalc).Copy .Range("B502")
        rowHght = .Range("B3").EntireRow.Height
        .Range("B501").RowHeight = rowHght
        lRowCopy = .Cells(Rows.Count, "C").End(xlUp).Row
        Set rng = .Range("B500:K" & lRowCopy)
    End With

    Set mySlide = myPresentation.Slides.AddSlide(myPresentation.Slides.Count + 1, PPLayout("LayoutEmittenten"))

    mySlide.Shapes.Placeholders(1).TextFrame.TextRange.Text = "Headline (" & iSlides + 2 & ")"
    Call PasteEm(mySlide, rng)
    rng.Clear
    'Annotations
    Set rng = wsEm.Range("B" & fRowAn & ":K" & lRowAn)
    rng.Copy
    Set mySlide = myPresentation.Slides.AddSlide(myPresentation.Slides.Count + 1, PPLayout("LayoutEmittenten"))
    mySlide.Shapes.Placeholders(1).TextFrame.TextRange.Text = "Headline (" & iSlides + 2 & ")"
    Call PasteEm(mySlide, rng)
End Sub



Sub PasteEm(mySlide As PowerPoint.Slide, rng As Range)
    Dim myShape As PowerPoint.Shape

    rng.Copy
    DoEvents
    mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile ' = 2
    Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
    With myShape
        .Width = 683
        .Top = 70
        .Left = 5
    End With
End Sub

This is not so much about code functionality, but how to use variables in general.

Pᴇʜ · Accepted Answer

Local Variable

First of all declare every variable as local as possible. Eg if it is needed in only one procedure/function declare it there.

Local Variable (Passed as Parameter)

If you need to access a variabe in more than one procedure/function then it is a good idea to pass it to the next function as a parameter. This can be done ByRef (which is default) or ByVal.

Sub ProcedureA()
    Dim ParamA As String
    ParamA = "AAA"
    Dim ParamB As String
    ParamB = "BBB"

    ProcedureB ParamA, ParamB

    Debug.Print ParamA 'returns 111
    Debug.Print ParamB 'returns BBB
End Sub

Sub ProcedureB(ByRef Param1 As String, ByVal Param2 As String)
    Param1 = "111" 'this will change ParamA in ProcedureA too
    Param2 = "222" 'this value will only be changed in ProcedureB
End Sub

While using ByRef (by reference) makes it possible to change the parameter in ProcedureB and have it also changed in ProcedureA, but the parameter that is passed ByVal (by value) does not change in ProcedureA.

Here it technically doesn't make any difference if you name the variables differently or use the same name. Use the name that is most meaningful in each of the procedures would be a good practice (see headline variable names below).

Actually I think it is also a good practice to always specify if it is ByRef or ByVal and not use the default. When using the default you always have to remember that it is ByRef by default in VBA but in VB.NET the default is ByVal which can easily get confusing (at least me).

After ProcedureA ends the variables are not available anymore (data is lost).

Global Variable

If you want the data to be persistant and accessible in more than one function then use global variabes (use them as rarely as possible).

Dim GlobalVarA As String

Sub ProcedureA()
    GlobalVarA = "AAA"
End Sub

Sub ProcedureB()
    Debug.Print GlobalVarA 'return AAA (if ProcedureA was run before)
End Sub

Note that in this case any procedure can change the value of GlobalVarA. If you pass it as a parameter as explained above, then only the procedures that the variable is passed to can access the variable.

Global variables will lose their data when Excel VBA ends (or file gets closed).

A down side of using global variables in a procedure is, you need always to check its value before using it the first time. Because if it was not initialized yet it is Empty or Nothing. For example (above) when running ProcedureB you cannot rely on that ProcedureA was already run before. So you would need to check the value of GlobalVarA before using it in ProcedureB especially if it is an object you have to test if for not beeing Nothing or you will easily run into errors.

Local vs. Global

So we can summarize, that restricting the access to a variable as much as possible makes your code more secure and more reliable (no other function can accidentally change it if it is only declared locally). Only use global variables if you really need to.

Re-Use Variable names

To re-use variable names is no problem in general if they are declared locally. But it gets tricky if you use the same name for a global and local variable (then VBA prefers the local one!)

Dim VarA As String 'global

Sub ProcedureA()
    Dim VarA As String 'same name local
    VarA = "AAA" 'this uses always the local variable!
End Sub

Sub ProcedureB()
    Debug.Print VarA 'this uses the global variable and it is empty (after ProcedureA is run) 
End Sub

In general it is a very good practice to use meaningful variable names only. That means instead of calling a variable rng1 and rng2 call them for example InputRange and OutputRange. Also if you need a counter (to eg loop through rows and columns) often i and j are used, but it is much more readable if you use eg iRow and iCol as variable names.

Option Explicit

In order to force a proper variable declaration I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration. This prevents you from mis-typing variable names and accidentally introduce new variables.