Skip to content

VBA stuff

Tips

  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
    FROM: http://wellsr.com/vba/2015/excel/3-ways-to-call-a-private-sub-from-another-module/

VBA extensions/add-ins

VBE (Visual Basic Editor) AddIns: - Oaltd.co.uk: - VBE Tools - Smart Intenter - MZ-Tools (mztools.com) - Ribbon Commander (on http://spreadsheet1.com) - connect to VS Code http://www.spreadsheet1.com/visual-studio-code-editor.html

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
FROM: https://excelmacromastery.com/vba-class-modules/

  • Class_Module_Events
    http://www.thelandbeyondspreadsheets.com/a-simple-example-of-how-to-use-class-modules-for-something-useful-in-excel-vba/

  • A good example on StackOverflow
    https://stackoverflow.com/questions/21767307/sub-property-in-vba-excel-class-module

  • On Chip Pearson's Site
    http://www.cpearson.com/excel/classes.aspx

VBA Macro To Refresh Queries & Connections

FROM Ken Puls:
https://www.excelguru.ca/blog/2014/10/22/refresh-power-query-with-vba/

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

    Err.Clear

    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

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

    Debug.print Err.Description

Good description on StackOverflow:
https://stackoverflow.com/a/2932147

VBA Debugging

  • The Conditional Compiler Constant
    https://answers.microsoft.com/en-us/msoffice/wiki/msoffice_word-mso_other/that-whacky-conditional-compiler-constant/3da810b8-9b89-4372-8104-a10c2ad25d4d

  • How to programmatically change conditional compilation properties of a VBA project
    http://stackoverflow.com/questions/19726791/how-to-programmatically-change-conditional-compilation-properties-of-a-vba-proje

  • Chip Pearson
    http://www.cpearson.com/excel/DebuggingVBA.aspx

  • StackOverflow
    http://stackoverflow.com/questions/1070863/hidden-features-of-vba

  • SpreadSheet Page (offline)
    http://spreadsheetpage.com/index.php/tip/vba_debugging_tips/

  • StackOverflow
    http://stackoverflow.com/a/8412488 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)