Skip to content

VBA stuff


  1. Object Explorer (F2)
    "VBA." to give list of all existing objects

  2. Tools > References > "Microsoft Visual Basic for Applications Extensibility 5.3"

  3. Hide Macro (Dummy Variable) Optional byDummy As Byte

VBA extensions/add-ins

VBE (Visual Basic Editor) AddIns: - - VBE Tools - Smart Intenter - MZ-Tools ( - Ribbon Commander (on - connect to VS Code

VBA Classes / Class Modules

we use Let to assign a value to a variable and we use Set to assign an object to an object variable

  • Class_Module_Events

  • A good example on StackOverflow

  • On Chip Pearson's Site

VBA Macro To Refresh Queries & Connections

FROM Ken Puls:

Public Sub UpdatePowerQueries()

    ' Macro to update my Power Query script(s)

    Dim lTest As Long, cn As WorkbookConnection

    On Error Resume Next

    For Each cn In ThisWorkbook.Connections

    lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)

    If Err.Number <> 0 Then


    Exit For

    End If

    If lTest > 0 Then cn.Refresh

    Next cn

End Sub

VBA Code Speedup

    On Error GoTo lblError

    Dim bEvents As Boolean, iCalc As Integer, bScrnUpd As Boolean

    bEvents = Application.EnableEvents
    iCalc = Application.Calculation
    bScrnUpd = Application.ScreenUpdating

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    '-----------------------My code

    Application.EnableEvents = bEvents
    Application.Calculation = iCalc
    Application.ScreenUpdating = bScrnUpd

    Exit Sub

    'reset them even if you are exiting due to error

    Application.EnableEvents = bEvents
    Application.Calculation = iCalc
    Application.ScreenUpdating = bScrnUpd

    Debug.print Err.Description

Good description on StackOverflow:

VBA Debugging

  • The Conditional Compiler Constant

  • How to programmatically change conditional compilation properties of a VBA project

  • Chip Pearson

  • StackOverflow

  • SpreadSheet Page (offline)

  • StackOverflow vb #CONST developMode = True sub Xyz #If developMode Then Debug.Print "something" #End If End Sub

Sheet Visibility

Copying wksheet and setting visibility to very hidden :

Sheets("SheetName").Visible = xlVeryHidden
' or xlSheetHidden or xlSheetVisible

Sheets("SheetName").Visible = True

Dynamically Insert VBA in new Workbook, Sheet Visibility

whether can add code to new workbook to ignore external links on open (dynamically inject code in new workbook)