(Solved) – What is the correct way to declare variables in VBA?

  • by
(solved)-–-what-is-the-correct-way-to-declare-variables-in-vba?

I was working with VBA and started wondering if declaring variables just before referencing them is better at improving performance as we are declaring them in memory so here is the version with variables declared right before referencing them:

Sub ConnectingToDatabases()

    Dim Conn As ADODB.Connection
    Set Conn = New ADODB.Connection

    Dim C As cConnectionString
    Set C = New cConnectionString

    Conn.ConnectionString = C.GetString(Access)
    Conn.Open

    Dim Cat As ADOX.Catalog

    Set Cat = New ADOX.Catalog
    Set Cat.ActiveConnection = Conn

    Dim Tbl As ADOX.Table
    Dim Col As ADOX.Column

    For Each Tbl In Cat.Tables
        If Tbl.Type = "TABLE" And Tbl.Name = "tblActor" Then
            Debug.Print Tbl.Name
            For Each Col In Tbl.Columns
                Debug.Print Col.Name, Col.Type
            Next Col
        End If
    Next Tbl

    Conn.Close

    Set C = Nothing
    Set Col = Nothing
    Set Tbl = Nothing
    Set Cat = Nothing
    Set Conn = Nothing

End Sub

and this one which is most seen method, I want to know if the above is any better than the following version in terms of performances or readability:

Sub ConnectingToDatabases()

    Dim Conn As ADODB.Connection
    Dim C As cConnectionString
    Dim Cat As ADOX.Catalog
    Dim Tbl As ADOX.Table
    Dim Col As ADOX.Column

    Set Conn = New ADODB.Connection
    Set C = New cConnectionString

    Conn.ConnectionString = C.GetString(Access)
    Conn.Open

    Set Cat = New ADOX.Catalog
    Set Cat.ActiveConnection = Conn

    For Each Tbl In Cat.Tables
        If Tbl.Type = "TABLE" And Tbl.Name = "tblActor" Then
            Debug.Print Tbl.Name
            For Each Col In Tbl.Columns
                Debug.Print Col.Name, Col.Type
            Next Col
        End If
    Next Tbl

    Conn.Close

    Set C = Nothing
    Set Col = Nothing
    Set Tbl = Nothing
    Set Cat = Nothing
    Set Conn = Nothing

End Sub

Class module cConnectionString just for reference:

Option Explicit

Public Enum ConnectionStr
    Access
    Sql
End Enum

Public Function GetString(ByVal cType As ConnectionStr)

    Select Case cType
        Case 0
            GetString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersBIFanatic" & _
                            "DesktopPersonalMovies.accdb;Persist Security Info=False;"
        Case 1
            GetString = ""
    End Select

End Function

I know some of you might say it depends which is absolutely true, but want to know if I were to send a code to you with a hundreds of variables which method would ease you in reviewing my code?

Leave a Reply

Your email address will not be published. Required fields are marked *