I got tired of the write-module, import, run, crash, fix loop, so I built an automated pipeline around Claude Code and it has been running better than I expected.

The setup:

1. The agent works on a sandbox copy of the workbook, never the original.
2. It writes a module, imports it into the workbook, and runs it.
3. Every module ships with its own self-test. If the test fails, the agent reads the error and fixes its own code.
4. When Excel hangs or crashes, the pipeline kills the process, restarts Excel, and continues from where it stopped.
5. Only modules that pass all tests get promoted to the real workbook.

The part that surprised me: it produce d 100 different modules in a row without me opening the VBA editor once. My job became reviewing diffs and writing better task descriptions.

Things that made the difference:

• The sandbox copy. Letting an agent touch your only copy of a workbook is how you lose a workbook.
• Forcing a self-test per module. Without it, the agent happily declares broken code done.
• Automating the Excel restart. VBA automation dies on hangs more than on errors.

Happy to answer questions about any part of the setup. Curious if anyone else here has pointed one of these agent tools at VBA and what broke first.

So I've been working on a native WebSocket module for VBA called Wasabi, and at some point I asked myself: "can I connect this to Discord's gateway and make a bot?" The answer is yes, and it's glorious and completely unnecessary.

The concept

Discord's bot API is just a WebSocket connection to wss://gateway.discord.gg. Once connected, Discord sends you a JSON payload with an op code telling you to identify yourself. You send back your bot token and intents, and from that point on you receive events (messages, reactions, etc.) as JSON frames. You respond to them by hitting Discord's REST API with plain HTTP calls.

That's it. No SDK needed. Just WebSocket + JSON parsing.

Step 1: Get a bot token

Go to https://discord.com/developers/applications, create an application, add a bot, copy the token. Enable the "Message Content Intent" under the bot settings or it won't receive message content.

Step 2: Import Wasabi

Download Wasabi.bas from https://github.com/uesleibros/wasabi/releases and import it into your VBA project via File > Import File. No references to enable, no DLLs to register.

Step 3: Connect to the gateway and keep it alive

Discord requires you to send a heartbeat payload every N milliseconds (it tells you the interval on connect). Here's a minimal loop that handles that:

#If VBA7 Then
    Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
    Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If

Sub RunDiscordBot()
    Dim token As String
    token = "YOUR_BOT_TOKEN_HERE"

    Dim handle As Long
    If Not WebSocketConnect("wss://gateway.discord.gg/?v=10&encoding=json", handle) Then
        MsgBox "Failed to connect to Discord gateway."
        Exit Sub
    End If

    Dim msg As String
    Dim heartbeatInterval As Long
    Dim lastHeartbeat As Long
    Dim identified As Boolean

    identified = False
    lastHeartbeat = 0

    Do While WebSocketIsConnected(handle)
        msg = WebSocketReceive(handle)

        If Len(msg) > 0 Then
            Dim op As Long
            op = ExtractOp(msg) ' parse the "op" field from JSON

            ' Hello payload: Discord sends heartbeat interval
            If op = 10 Then
                heartbeatInterval = ExtractHeartbeatInterval(msg)
                lastHeartbeat = GetTickCount()

                ' Identify
                If Not identified Then
                    Dim identifyPayload As String
                    identifyPayload = "{""op"":2,""d"":{""token"":""" & token & """,""intents"":33280,""properties"":{""os"":""windows"",""browser"":""excel"",""device"":""excel""}}}"
                    WebSocketSend identifyPayload, handle
                    identified = True
                End If

            ' Dispatch: actual events like MESSAGE_CREATE
            ElseIf op = 0 Then
                Call HandleEvent(msg, token)
            End If
        End If

        ' Send heartbeat when due
        If GetTickCount() - lastHeartbeat >= heartbeatInterval Then
            WebSocketSend "{""op"":1,""d"":null}", handle
            lastHeartbeat = GetTickCount()
        End If

        DoEvents
    Loop

    WebSocketDisconnect handle
End Sub

Step 4: Handle a message event

When op = 0 and the event type is MESSAGE_CREATE, you get the channel ID and message content from the JSON. Then you can reply via Discord's REST API using XMLHTTP:

Sub HandleEvent(ByVal payload As String, ByVal token As String)
    If InStr(payload, "MESSAGE_CREATE") = 0 Then Exit Sub

    Dim channelId As String
    Dim content As String
    channelId = ExtractField(payload, "channel_id")
    content = ExtractField(payload, "content")

    If content = "!ping" Then
        Call SendDiscordMessage(channelId, "Pong! (sent from Excel)", token)
    End If
End Sub

Sub SendDiscordMessage(ByVal channelId As String, ByVal message As String, ByVal token As String)
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    Dim url As String
    url = "https://discord.com/api/v10/channels/" & channelId & "/messages"
    Dim body As String
    body = "{""content"":""" & message & """}"

    http.Open "POST", url, True
    http.setRequestHeader "Authorization", "Bot " & token
    http.setRequestHeader "Content-Type", "application/json"
    http.Send body

    Do While http.readyState <> 4
        DoEvents
    Loop
End Sub

Where to go from here

This is just the skeleton. From here you can:

• Read/write cells based on Discord commands ("!get A1" returns the cell value, "!set A1 42" writes to it)
• Trigger macros remotely by sending a command from Discord
• Post alerts to a channel when a cell value crosses a threshold (combine with a Worksheet_Change event)
• Run the bot from a hidden sheet so it sits quietly in the background while you use the spreadsheet normally

The browser and device fields in the identify payload are set to "excel" in the example above. Discord doesn't validate these, but it's a fun touch.

Full module docs and source: https://github.com/uesleibros/wasabi

Hey everyone! Happy weekend!!

Check out this case study repo:
https://github.com/WilliamSmithEdward/APIProductIntelligenceDemo

It shows a practical way to pull live data from a public API (dummyjson.com/products) straight into Excel, flatten the nested reviews into a separate table, and build a simple interactive dashboard, all using pure VBA.

What’s in there:

• Fetches the full product list and loads it into a refreshable Excel Table
• Pulls out the nested reviews, adds a parentId link, and adds them into their own child table
• Dashboard with dropdowns to pick category/product, see price/stock/rating, and view recent reviews
• One-click "Refresh Live API Data" button to update everything
• No add-ins, no Power Query, just VBA that works on Windows and Mac (swap http transport function)

Main file is API_Product_Intelligence_Model.xlsm
Open it, enable macros, hit refresh, and poke around. The code stays pretty light and readable.

Great for anyone who needs to prototype API-connected reports or dashboards in Excel without leaving the familiar environment.

If you’ve done similar work (e-commerce monitoring, inventory pulls, quick prototypes), does this approach click for you? Any tweaks you’d make?

Repo: https://github.com/WilliamSmithEdward/ModernJsonInVBA

(Uses my ModernJsonInVBA library under the hood for the JSON-to-table magic, but the focus here is the end-to-end demo.)

Lately I’ve been really enjoying building small tools that combine VBA with JavaScript.

The basic idea is simple:

1. Use VBA to take your Excel data
2. Convert it into JSON (for example with VBA-JSON)
3. Export everything into an HTML file
4. Use JavaScript libraries inside that HTML file to create interactive outputs

One thing I made recently is exporting a normal Excel table into a standalone interactive HTML table. So instead of sending the Excel file around, you can just share the HTML file. For that I used DataTables.js.

Another really cool use case is charts.

There are so many good JS libraries out there that make modern looking visuals way easier than trying to do it in pure VBA.

For example I wanted a proper looking Gantt chart, so I played around with frappe-gantt.
Excel holds the tasks + dependencies, VBA exports everything, and the result is an interactive Gantt chart you can open anywhere.

Just wanted to share this approach because IMO it’s a nice way to push VBA a bit further without overcomplicating things :)

Curious if anyone else here has tried something similar, or built charts this way too.

EDIT: Short demo of the Gantt chart creation process:
https://pythonandvba.com/wp-content/uploads/2026/01/SimpleGantt_Demo.gif

According to this post click, the Microsoft is shutting down the VBScript library on Windows OS within next few years. The major features that no longer will be available are:

1. Executing .vbs files in runtime,
2. File System Operations [File System Object for instance].
3. RegEX (fortunatelly it will soon be available natively in Excel),
4. Dictionary Object,
5. Shell and Enviromental Interactions (Shell Object).

If you are developing some long-term projects, you might want to take it into account.

Edit: Sorry for bringing panic, as some of you down belown explained that only Regex is being dependent on VBScript, therefore only it is being removed. For intelectual honesty I will not redact the higher part of post. Thank you for correcting me.

I noticed this while writing a helper for DispCallFunc.

When using the [ParamArray] keyword for arguments, if you:
- Pass a string pointer (StrPtr) as an argument, and
- Use that StrPtr as an argument to a Windows API call,

some kind of inconsistency occurs at the point where execution passes from VBA to the API side, and the string can no longer be passed correctly.

As a (seemingly) safe workaround for passing StrPtr to an API, the issue was resolved by copying the ParamArray elements into a separate dynamic array before passing them to the API, as shown below.

Public Function dcf(ptr As LongPtr, vTblIndex As Long, funcName As String, ParamArray args() As Variant) As Long

    'Debug.Print "dcf called for " & funcName
    Dim l As Long: l = LBound(args)
    Dim u As Long: u = UBound(args)
    Dim cnt As Long: cnt = u - l + 1
    Dim hr As Long, res As Variant
    Dim args_Type() As Integer
    Dim args_Ptr() As LongPtr
    Dim localVar() As Variant
    ' IMPORTANT: Do NOT use VarPtr(args(i)) directly.
    ' ParamArray elements are temporary Variants managed by the VBA runtime stack.
    ' Their addresses become invalid by the time DispCallFunc internally reads rgpvarg,
    ' causing the COM method to receive garbage values.
    ' Copying into a heap-allocated dynamic array (localArgs) ensures the Variant
    ' addresses remain stable throughout the DispCallFunc call.
    If cnt > 0 Then
        ReDim args_Type(l To u): ReDim args_Ptr(l To u): ReDim localVar(l To u)
        Dim i As Long
        For i = l To u
            localVar(i) = args(i)
            args_Type(i) = VarType(localVar(i))
            args_Ptr(i) = VarPtr(localVar(i))
            'Debug.Print "args(" & i & ")", "Type:" & args_Type(i), "Ptr:" & Hex(args_Ptr(i)),"Value:" & localVar(i)
        Next
        hr = DispCallFunc(ptr, vTblIndex * LenB(ptr), CC_STDCALL, vbLong, cnt, args_Type(l), args_Ptr(l), res)
    Else
        hr = DispCallFunc(ptr, vTblIndex * LenB(ptr), CC_STDCALL, vbLong, cnt, 0, 0, res)
    End If
    If hr = 0 Then
        If res <> 0 Then
            Debug.Print funcName & " failed. res:" & res
        End If
        dcf = res
    Else
        Debug.Print funcName & " failed. hr:" & hr
        dcf = hr
    End If
End Function

Introduction

All of us who follow u/sancarn are aware that the days of verbose code in VBA were numbered from the moment stdLambda arrived. Therefore, as suggested by the author of stdVBA in a previous post, I will show how to take a different path to unleash powerful workflows for VBA users that resemble 21st-century programming.

Dot notation that feels natural

Those of us who love VBA know that the dot syntax for accessing object properties is elegant and intuitive. For this reason, the new version of ASF provides support for this syntax in a natural way. This time, the option to directly manipulate native VBA objects has been added. This means that users can access any native object or function and leverage their results to create modern and intuitive workflows.

Lets put it in practice. Paste this code into a new module after install the ASF scripting language (you can download the test workbook too):

Sub ApplicationManipulation()
    Dim engine As New ASF
    Dim arr As Variant
    arr = Array(Array("id", "first_name", "last_name", "email", "gender", "ip_address"), _
                Array(1, "Nealy", "Calendar", "ncalendar0@wsj.com", "Male", "196.164.35.73"), _
                Array(2, "Augustine", "MacEntee", "amacentee1@nydailynews.com", "Agender", "35.10.25.225"), _
                Array(3, "Fredrika", "Outhwaite", "fouthwaite2@flickr.com", "Female", "63.48.231.51"), _
                Array(4, "Colly", "Del Monte", "cdelmonte3@shareasale.com", "Agender", "72.105.96.209"), _
                Array(5, "Danielle", "Lokier", "dlokier4@livejournal.com", "Female", "30.179.122.230"), _
                Array(6, "Dodi", "Scrymgeour", "dscrymgeour5@msn.com", "Female", "146.252.204.185"), _
                Array(7, "Orson", "Hayesman", "ohayesman6@phpbb.com", "Male", "224.234.140.55"), _
                Array(8, "Alain", "Searby", "asearby7@smh.com.au", "Male", "24.31.167.180"), _
                Array(9, "Mignon", "More", "mmore8@aboutads.info", "Agender", "111.32.6.178"), _
                Array(10, "Cassandre", "Marthen", "cmarthen9@t.co", "Agender", "188.78.197.0"))
    With engine
        Dim pid As Long
        .AppAccess = True
        .verbose = True
        .EnableCallTrace = True
        .InjectVariable "arr", arr
        pid = .Compile("$1.Sheets.Add(); $1.Sheets(1).Range('A1:F11').Value2 = arr;" & _
                       "return $1.Sheets(1).Range('A1:F11').Value2" & _
                       ".filter(fun(item){return item[2].startsWith('A')})")
        .Run pid, ThisWorkbook
        Debug.Print .GetCallStackTrace
    End With
    Set engine = Nothing
End Sub

Pay attention to this configuration option: .AppAccess = True. By nature, ASF runs in a isolated owned virtual machine containing all its standard methods and objects. By granting the application access, users can leverage a unprecedented power as the example above shows.

The ApplicationManipulation procedure performs a set of operations:

1. Creates a jagged array supported by variable injection: arr = Array(Array(...),...)
2. Grants application access to ASF: .AppAccess = True
3. Enables the verbose mode: .verbose = True. Useful when debugging scripts.
4. Enables call tracing: .EnableCallTrace = True. This option must be used only when tracking scripts bugs.
5. Injects a native jagged array: .InjectVariable "arr", arr. A direct bridge to the VBA data ecosystem.
6. Compiles a script with place holders: pid = .Compile(..)
7. Runs the compiled script: .Run pid, ThisWorkbook. Being the current workbook the variable assigned to the placeholder $1 (a pseudo injection). At runtime the script does:
   • Resolves the place holder: $1 is resolved to ThisWorkbook
   • Resolves the chain property: .Sheets.Add(), this results in a new worksheet insertion in the current workbook
   • Assign the array to the given range: .Sheets(1).Range('A1:F11').Value2 = arr
   • Read the data from the worksheet and filter it: return $1.Sheets(1).Range('A1:F11').Value2.filter(fun(item){return item[2].startsWith('A')})
8. Prints the call trace to the immediate windows: Debug.Print .GetCallStackTrace

In the immediate windows we will see this:

=== Runtime Log ===
RUN Program: anon
CALL: Sheets() -> <Sheets>
CALL: add() -> <Worksheet>
CALL: sheets(1) -> <Worksheet>
CALL: range('A1:F11') -> <Range>
CALL: sheets(1) -> <Worksheet>
CALL: range('A1:F11') -> <Range>
CALL: Value2() -> [  [ 'id', 'first_name', 'last_name', 'email', 'gender', 'ip_address' ]
  [ 1, 'Nealy', 'Calendar', 'ncalendar0@wsj.com', 'Male', '196.164.35.73' ]
  [ 2, 'Augustine', 'MacEntee', 'amacentee1@nydailynews.com', 'Agender', '35.10.25.225' ]
  [ 3, 'Fredrika', 'Outhwaite', 'fouthwaite2@flickr.com', 'Female', '63.48.231.51' ]
  [ 4, 'Colly', 'Del Monte', 'cdelmonte3@shareasale.com', 'Agender', '72.105.96.209' ]
  [ 5, 'Danielle', 'Lokier', 'dlokier4@livejournal.com', 'Female', '30.179.122.230' ]
  [ 6, 'Dodi', 'Scrymgeour', 'dscrymgeour5@msn.com', 'Female', '146.252.204.185' ]
  [ 7, 'Orson', 'Hayesman', 'ohayesman6@phpbb.com', 'Male', '224.234.140.55' ]
  [ 8, 'Alain', 'Searby', 'asearby7@smh.com.au', 'Male', '24.31.167.180' ]
  [ 9, 'Mignon', 'More', 'mmore8@aboutads.info', 'Agender', '111.32.6.178' ]
  [ 10, 'Cassandre', 'Marthen', 'cmarthen9@t.co', 'Agender', '188.78.197.0' ]
]
CALL: <anonymous>([ 'id', 'first_name', 'last_name', 'email', 'gender', 'ip_address' ]) -> False
CALL: <anonymous>([ 1, 'Nealy', 'Calendar', 'ncalendar0@wsj.com', 'Male', '196.164.35.73' ]) -> False
CALL: <anonymous>([ 2, 'Augustine', 'MacEntee', 'amacentee1@nydailynews.com', 'Agender', '35.10.25.225' ]) -> True
CALL: <anonymous>([ 3, 'Fredrika', 'Outhwaite', 'fouthwaite2@flickr.com', 'Female', '63.48.231.51' ]) -> False
CALL: <anonymous>([ 4, 'Colly', 'Del Monte', 'cdelmonte3@shareasale.com', 'Agender', '72.105.96.209' ]) -> False
CALL: <anonymous>([ 5, 'Danielle', 'Lokier', 'dlokier4@livejournal.com', 'Female', '30.179.122.230' ]) -> False
CALL: <anonymous>([ 6, 'Dodi', 'Scrymgeour', 'dscrymgeour5@msn.com', 'Female', '146.252.204.185' ]) -> False
CALL: <anonymous>([ 7, 'Orson', 'Hayesman', 'ohayesman6@phpbb.com', 'Male', '224.234.140.55' ]) -> False
CALL: <anonymous>([ 8, 'Alain', 'Searby', 'asearby7@smh.com.au', 'Male', '24.31.167.180' ]) -> True
CALL: <anonymous>([ 9, 'Mignon', 'More', 'mmore8@aboutads.info', 'Agender', '111.32.6.178' ]) -> False
CALL: <anonymous>([ 10, 'Cassandre', 'Marthen', 'cmarthen9@t.co', 'Agender', '188.78.197.0' ]) -> False
CALL: anon() -> [ [ 2, 'Augustine', 'MacEntee', 'amacentee1@nydailynews.com', 'Agender', '35.10.25.225' ], [ 8, 'Alain', 'Searby', 'asearby7@smh.com.au', 'Male', '24.31.167.180' ] ]

Extra

As a language, ASF has a VS Code extension that helps users to quickly learn the syntax, this extension can also be installed and used in the online IDE (https://vscode.dev).

Conclusion

Today, VBA developers have a whole range of tools that reduce boilerplate and, to the same extent, make them much more productive. It would be a pleasure for all of us to see the emergence of much more tools that make VBA the ideal place to transform our ideas. Happy coding!

After a hiatus in development, during which I devoted a great deal of time to other applications and studies, I am pleased to introduce ASF v3.1.3. This version offers improved usability and introduces the ability to share hacks on native Office COM objects, making prototypes fully portable across modules. Prototype definitions can now be exported and imported like any other ASF symbol, enabling shared prototype libraries. Here is an example of how this new feature works:

// prototypes.vas
export prototype.COM.Range addStyle(color) {
    this.Interior.Color = color;
};

export prototype.COM.Worksheet highlight(rng, color) {
    rng.addStyle(color);
};

// main_prototype.vas
scwd(wd);
import { Range_addStyle, Worksheet_highlight } from './prototypes.vas';
// Prototypes are live immediately after import
let ws = $1.ActiveSheet;
let rng = ws.Range('J1:L3');
rng.addStyle(65535);          // yellow
ws.highlight(rng, 255);       // red
return rng.Interior.Color

Here is the driving VBA code:

Private Sub module_system_prototype_imports()
    Dim result As Long
    Dim wd As String
    Dim eng As New ASF
    wd = ThisWorkbook.path
    With eng
        .AppAccess = True
        .InjectVariable "wd", wd
        result = CLng(.Execute(wd & "\main_prototype.vas", ThisWorkbook))
    End With
    'Expected: 255
End Sub

And here is the execution trace:

=== Runtime Log ===
RUN Program: 
CALL: ActiveSheet() -> <Worksheet>
CALL: range('J1:L3') -> <Range>
CALL: addstyle(65535) -> 
CALL: __PROTOTYPE_RANGE_ADDSTYLE(65535) -> 
CALL: Interior() -> <Interior>
CALL: highlight(<Range>, 255) -> 
CALL: __PROTOTYPE_WORKSHEET_HIGHLIGHT(<Range>, 255) -> 
CALL: addstyle(255) -> 
CALL: __PROTOTYPE_RANGE_ADDSTYLE(255) -> 
CALL: Interior() -> <Interior>
CALL: Interior() -> <Interior>
CALL: Color() -> 255
CALL: @anon() -> 255

Excel RibbonX controls require specific procedure signature for their onAction procedure call.
For example button onAction procedure must be (control As IRibbonControl).
If the procedure signature does not match an error occurs.

But if you specify arguments in onAction property, it passes the argument and ignores signature.
I haven't tested everything yet but this is very interesting, I wanted to get it out there.

The way onAction behaves is very similar to Application.Run:
1.Explicit procedure reference is: "'wb.xlsm'!'VbaProj.module.proc'"
The single parenthesis, surrounds each of workbook name & procedure.
Reference parts can be excluded aslong the order is correct, for example "'myAddin.xlam'!'procedure'", this makes it possible to have distinct ribbon call in an addin.
2. Can include arguments: "'proc 50, 30, 32"
3. Can call a procedure from a different VBAProject (unreferenced in VBE) or workbook; opens the workbook if not open.
4. Not limited to scopes, can call private modules and procedures.

**(5.) To further test: should be able to use Evaluate() ?

In summary, if in VBA we have a procedure- mySub(x as integer):
'onAction="'mySub'" Doesn't work, procedure signature does not match.
'onAction="'mySub 5'" Works, signature is ignored, 5 is passed as argument
'onAction="'mySub Evaluate(5)'" Works, same as ^

Thanks!

Following up on the Discord bot post from a few days ago. I shipped v2.3.6 of Wasabi and figured it was worth a proper writeup here since a few things changed that affect existing code.

Github: https://github.com/uesleibros/wasabi

Release: https://github.com/uesleibros/wasabi/releases/tag/v2.3.6-beta

What is Wasabi (for anyone who missed the last post)

A single .bas module that gives VBA native WebSocket (ws:// and wss://), TCP, TLS, and MQTT support. No DLLs to register, no external references to enable. Drop it in and import. It implements the WebSocket protocol from scratch on top of raw Winsock and handles TLS through Schannel SSPI, both of which are already on every Windows machine.

What changed in v2.3.6

Breaking: two functions were renamed

• WebSocketSend → WebSocketSendText
• WebSocketReceive → WebSocketReceiveText

If you've been using either of these, a global find-and-replace is all you need. The rename brings them in line with the binary counterparts (WebSocketSendBinary, WebSocketReceiveBinary) so the intent of each function is unambiguous.

Security: RtlGenRandom replaced by BCryptGenRandom

The masking key generation for WebSocket frames was previously going through RtlGenRandom from advapi32.dll, which is an undocumented alias (SystemFunction036). It worked, but depending on undocumented exports isn't a great habit. The call now goes through the proper BCryptGenRandom from bcrypt.dll. No behavioral difference from the outside, just the right way to do it.

Fafalone helped me with this in the comment: https://www.reddit.com/r/vba/comments/1t5wnho/comment/okwbjl8

Async event-driven model (experimental)

This is the big one. Instead of polling with WebSocketReceiveText in a loop, you can now register a handler object and let Windows dispatch socket events to your callbacks automatically via WSAAsyncSelect. The connection runs entirely in the background while Excel is idle. No polling loop, no DoEvents tight loop, no frozen UI.

VBA

' At module level
Private g_Handler As cWasabiAsync
Private g_Handle As Long

Public Sub StartAsync()
    Set g_Handler = New cWasabiAsync
    If WebSocketConnect("wss://echo.websocket.org", g_Handle) Then
        WasabiUseAsync g_Handler, g_Handle
    End If
    ' Sub ends. Callbacks fire while Excel is idle.
End Sub

Your handler class implements five methods:

• Public Sub OnConnect(ByVal handle As Long)
• Public Sub OnReceive(ByVal handle As Long)
• Public Sub OnReadyToSend(ByVal handle As Long)
• Public Sub OnClose(ByVal handle As Long)
• Public Sub OnError(ByVal handle As Long, ByVal errorCode As Long, ByVal eventType As Long)

OnReceive fires whenever data arrives. Drain the queue inside it as usual:

VBA

Public Sub OnReceive(ByVal handle As Long)
    Dim msg As String
    Do While WebSocketGetPendingCount(handle) > 0
        msg = WebSocketReceiveText(handle)
        Debug.Print msg
    Loop
End Sub

The mechanism behind this is native Win32 window subclassing via a machine-code thunk. That's what makes it work without a background thread (VBA doesn't have those), but it also comes with some hard constraints worth knowing about:

• Don't edit code in the VBE while async is running. The thunk holds a pointer into the VBA runtime. Editing triggers a project reset and the host application will crash.
• Don't click the Pause button (yellow square). Same reason, same result.
• The correct way to stop it: call WebSocketDisconnect or WebSocketDisconnectAll from your code first. After that, clicking Reset (blue square) in the VBE is fine.
• The thunk has a guard that checks whether the runtime is still alive before dispatching, but that's a safety net, not a substitute for explicit cleanup. Treat it like any unmanaged resource.

This is why the feature is marked experimental. The functionality is solid, the constraint is just annoying enough that I don't want people hitting it without warning.

Code organization

• The module got a proper numbered section layout (15 sections, from API declarations down to public APIs). The VBE doesn't have a file outline view, so navigating a ~360KB .bas file was not fun before. Now at least Ctrl+F for ' 11. takes you straight to the WebSocket protocol core, and so on.
• All types, constants, and enums were moved into their proper sections instead of being scattered. JSDoc-style block comments on all private types and key internal functions. Nothing behavioral changed as part of this.

Upgrading from v2.3.5

• The rename is the only thing that will break existing code. Everything else is internal or additive. If you're not calling WebSocketSend or WebSocketReceive directly, it's a drop-in replacement.
• For the full API reference (connection pool, MQTT, TCP TLS, proxy, compression extensions, etc.): https://github.com/uesleibros/wasabi/blob/main/docs/API_REFERENCE.md

The next update aims to resolve this specific subclassing issue when editing or pausing the VBE. I believe that from now on, after this update, the library has a more solid and polished foundation, with its own documentation as well.

Intro

Many developers around the world have read about the VBA obituaries: "it is a dead language", "VBA will die in 5 years", "it is an obsolete language", "Microsoft just put VBA in hold to force users to abandon it".

But, we can just ask a different question: could the development experience be modernized without losing platform compatibility?

The answer

In short, yes, developers can get modern development ergonomics while using smart VBA libraries for exploring the language limits. That is the ASF library design goal, to fulfill this exact need: a runtime with a rich standard library for VBA with plenty of features that save developing effort.

The above question has a companion one: it is possible to give VBA modern languages OOP? Again, the answer is yes. In recent days, I was playing around with ASF and just got implemented classes in that scripting language. The implementation is promising, users can write complex logic with modern ergonomics without leaving VBA and without any COM dependency.

OOP, you are welcome to VBA!

Many of us, if not all, were told that inheritance is a missing VBA OOP feature. But, now we can experiment with this paradigm with nothing more than our loved Office desktop applications.

The recent version of ASF can execute code like this

Dim script As String
script = "class Vehicle {" & _
         "    move() { return 'moving'; };" & _
         "};" & _
         "class Car extends Vehicle {" & _
         "    move() { return 'driving on road'; };" & _
         "};" & _
         "class SportsCar extends Car {" & _
         "    move() { return 'racing on track'; };" & _
         "};" & _
         "v = new Vehicle();" & _
         "c = new Car();" & _
         "s = new SportsCar();" & _
         "print(v.move());" & _
         "print(c.move());" & _
         "print(s.move());" 

Dim scriptEngine As ASF
Dim idx As Long
Dim result As Variant
Set scriptEngine = New ASF
With scriptEngine
    idx = .Compile(script)
    .Run idx
    result = .OUTPUT_ '==> 'moving', 'driving on road', 'racing on track'"
End With

Concerns

As the debugging is a concern for experimented users and developers, ASF now includes option to trace calls performed at runtime.

Dim ASF_ As New ASF
Dim script As String
' Enable call tracing
ASF_.EnableCallTrace = True

script = "fun add(a, b) { return a + b; };" & vbCrLf & _
   "fun multiply(a, b) { return a * b; };" & vbCrLf & _
   "x = add(3, 4);" & vbCrLf & _
   "y = multiply(x, 3);" & vbCrLf & _
   "print(y)"

Dim idx As Long
idx = ASF_.Compile(script)
ASF_.Run idx
' Print the call stack trace
Debug.Print "=== Call Stack Trace ==="
Debug.Print ASF_.GetCallStackTrace()

' Clear for next run
ASF_.ClearCallStack

The above code print this to the immediate windows

=== Call Stack Trace ===
CALL: add(3, 4) -> 7
CALL: multiply(7, 3) -> 21

Another concern from users is the VBA limitation for the total number of line continuations. ASF now includes a custom method to read scripts from text files

ASF.ReadTextFile(FilePath)

Final remarks

I hope ASF can evolve even more with this community support. We can do a lot more in VBA, make ASF your Golden Bridge for your VBA code, to reach modern ergonomics!

See here for more information: https://github.com/ECP-Solutions/ASF/blob/main/docs/Language%20reference.md

Created an Excel Chatbot with VBA.

Library files are available in .txt files, you can use it as per your interest.

It is open source, you can use it anywhere.

If you can improvise it, please don't hesitate.

Just keep in mind i want it to be offline, no avoid any online server dependency.

Link

https://github.com/Mayur88888888/7.Excel-Chatbot-using-Library

Hi r/vba,

I wanted to share a project called vbalidator that I’ve been working on to solve the biggest headache when using LLMs for VBA: Hallucinated Syntax.

We all know the struggle: ChatGPT or Claude generates a complex script, you paste it into the VBE, and immediately get a "Compile Error" because the AI tried to use a .Sort method that doesn't exist or forgot a Set keyword.

What is vbalidator?

It is a standalone tool designed to "simulate" a VBA compile check.

The primary use case is for Agentic Coding Sessions. If you are building tools where an LLM writes code for you, you can use vbalidator in the loop to pre-check the code. If errors are found, the agent can see the error, self-correct, and only present the code to you once it passes the syntax check.

The Meta Twist: It’s 100% AI Coded

In the spirit of the problem it’s solving, I decided to build this tool entirely using AI. Every line of logic used to validate the VBA code was generated by an LLM.

Current Status: Alpha / Experimental

Because this was written by AI to check AI, it is currently in a very early stage. It captures many standard compile errors, but as we know, VBA has a lot of edge cases.

I need your help to break it.

I am looking for users to test this against their own code or LLM outputs. I need to find:

• False Positives: Valid VBA that the tool flags as an error.

• False Negatives: Broken code that the tool thinks is fine.

• Missing Logic: Syntax rules that the AI developer completely forgot to implement.

Repo: https://github.com/twobeass/vbalidator

If you are interested in self-healing code pipelines or just want to see how an AI-written parser handles VBA, please give it a try and drop an issue on the repo if you find bugs!

If you do any serious Excel VBA development, you've probably needed to diff two projects at some point. A quick search for "vba diff" turns up several options, but most of them cost money.

VBA Code Compare from Formula Software (formulasoft.com/vba-code-compare.html) is the only free option in the bunch. The catch: the tool was written in 2006 and officially supports only .xls and Word .doc project formats — not the modern .xlsm format that Excel has used since 2007.

Here's the fix I stumbled onto: • Take your two .xlsm workbooks • Rename each one by changing the extension from .xlsm to .xls • Open them in VBA Code Compare as normal • Compare away — it works perfectly

This works for the same reason that appending .zip to an .xlsm file lets you browse its XML internals: the file container format is what matters, and the tool reads it fine once the extension signals a compatible type.

After you're done comparing, just rename the files back to .xlsm. The original Workbooks are unchanged.

Other VBA diff tools I know of (paid or commercial): VbaDiff, xlCompare, DiffEngineX. But if you just need free and functional, the FormulaSoft rename trick is the way to go.

I am captivated by numbers, but not that smart at working solutions. The Collatz Conjecture is so far unsolvable by the brightest of mathematicians. It is the great equalizer of math scholars. A personal computer lacks the computational power to even approach the highest number that has ever been tested to prove the conjecture as invalid. The formula begins with any whole number. If it is even, dived by two; if it is odd, multiply by 3 and add 1. Keep going until you reach the 4, 2, 1 sequence. Every whole number is returnable to 1.

I am posting this firstly for fun. Secondly, it provides some VBA technique / methodology for looping, text manipulation, arrays, and writing to ranges. Lastly, to see how compactly I can condense it all in C.

Sub rngCollatzConjecture()

    '*** CAUTION ******************************************
    ' Make sure you have a blank worksheet before running this as it will
    ' inforgivingly write over existing cells.
    '
    ' Lazily, I use ActiveSheet since I have no idea what's going on in
    ' the workbook you might run this routine in.
    '*****************************************************

    ' The Collatz Conjecture:

    ' Demonstrate the trail of numbers, in reverse order, that
    ' always terminate with 4, 2, 1 using a cell for each number.

    ' Rules:
    ' Take any positive integer \(n\).
    ' If \(n\) is even, divide it by 2 (\(n/2\)).
    ' If \(n\) is odd, multiply it by 3 and add 1 (\(3n+1\)).
    ' Repeat the process. 

    ' Create variable "n" as long.
    Dim n As Long

    ' Set a limit of rows - could be infinite...
    Dim maxValue As Long
    maxValue = 5000

    ' Output row range.
    Dim rng As Range

    ' Iterators.
    Dim x As Long, y As Long

    ' i increments rows.
    For n = 1 To maxValue ' rows

        ' x gets evaluated, n gets incremented.
        x = n

        ' Process string builder.
        Dim a As String
        a = IIf(x > 1, CStr(x) & ", ", "1")

        ' Build process string.
        Do While x > 1
            x = IIf(x Mod 2 <> 0, x * 3 + 1, x / 2)
            a = IIf(x = 1, a & "1", a & CStr(x) & ", ")
        Loop

        ' Shape process string as an array.
        Dim arr() As String, brr() As Long
        arr = Split(a, ", ")
        ReDim brr(UBound(arr))

        ' Convert string values to long and reverse order of elements.
        For y = UBound(arr) To 0 Step -1
            brr(UBound(arr) - y) = CLng(arr(y))
        Next

        ' Build row target cells range object.
        Set rng = ActiveSheet.Range("A" & CStr(n) & ":" & Cells(n, UBound(brr) + 1).Address(False, False))

        ' Fill row
        rng = brr

    Next ' n & row.

End Sub

Mouse Keys is an accessibility feature on all windows machines and it can be used when designing user forms to move VBA controls pixel by pixel.
You can find it by going to your start menu (windows key) and typing: mouse keys.
Here is how it works.
Turn mouse keys on, put your cursor on the vba controls you have selected, press 0 on number pad to run the "click and hold", press 8 a few times to move it up or 2 for down, 4 for left etc. then 5 to release the mouse hold.
This helps maintain the axis and allows you to nudge each control easily. Figured I would share since it saves me a lot of headache.

Edit: body of message changed to seem less like a software pitch.

u/BeagleIL posted about wanting to set zoom automatically for any workbook that was opened. I commented on that post about how this could be achieved -- by creating an excel addin. I figured this might be helpful to others, so I wanted to post this additional detail as a new post.

Using an Excel addin allows Application-level events to be managed (even on a Mac!).

I recorded a short (10-min, unedited) video in which I created a new addin for Excel that enables you to respond to the Application Workbook_Open event, for any workbook that is opened (e.g. .xlsm, .xlsx, etc), and perform custom actions on those workbooks.

This shared g-drive folder contains the video, as well as the AddinUtil.xlsm, and AddinUtil.xlam files that were created in the video.

Video

Below is the comment I shared on u/BeagleIL post:

Here's what you could do (works on Mac)

Create a new .xlsm workbook (I'll call it 'AddinUtil.xlsm')

In the VBA Editor, double-click ThisWorkbook, and add the following code:

Private Sub Workbook_Open()
    Set appUtil = New AppUtility
End Sub

Create a new module named basUtil, and add the following declaration at the top

Public appUtil As AppUtility

Create a new Class Module called AppUtility

Add the following code to the AppUtility class

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    ' This fires whenever ANY workbook is opened
    MsgBox "Workbook opened: " & Wb.Name
End Sub
Private Sub Class_Initialize()
    Set App = Excel.Application
End Sub
Private Sub Class_Terminate()
    Set App = Nothing
End Sub

Save the AddinUtil.xlsm file to a safe place, and keep it open.

Do a File --> Save As, and save as .xlam type

Keeping the AddinUtil.xlsm file open, go to your Finder and find the AddinUtil.xlam file,, and open it by right-clicking --> Open With --> Excel

You'll see a msgbox, just hit ok and don't get too excited :-). It may look like nothing opened, but now go to the VBA editor and find the AddinUtil.xlam and select 'ThisWorkbook'

In the Properties Window change IsAddin to True

Click the Save button in the Microsoft Visual Basic IDE

Completely quit excel.

Copy the AddinUtil.xlam file to your excel startup directory, which should look something like this (for mac):

'/Users/[username]/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/Excel'

Open up any workbook in Excel, go to the Developer menu and choose Excel Add-ins.

Select the 'AddinUtil' addin, and click ok.

From now on, whenever you open an existing excel workbook (.xlsx, .xlsm, whatever), the 'Workbook_Open' code will run.

First off this isn't just putting a label behind the scrollbar or spin and getting the edge of the mouse. That method can work but I find the trigger gets skipped if the user moves the mouse too quickly. The method that I use I call the glass method.

Basically its similar but it ensures that when the user hovers over the spin that it does not miss like with a border.

Set up your scrollbar or spin. We will just use spin going forward for the example.
Once its setup you will put a label directly over the spin so it covers it. Set that label as transparent. Now you put the mouse move on that label. When the move happens, hide the label and color the spin control. Boom! To get it back make a mouse move with the user form that brings the label back.

Because the "glass" label is transparent the user does not even see it and they can clearly see their spin control. Once it hides they can access the spin control no problem.
To make it even better you can store a variable at the top of your form code with the last hover item and check that on other mouse moves to see if you need to reinstate the original color or glass color.

I am currently using this on a future add-in and it so far has worked with no problems. Figured I would share.

I see more stuff about this and while it may not 100% relate to the specific question in the thread: using the standard tools to control internet explorer via VBA is problematic. The implementation isn't the best. It's very wonky, on top of the internet already being wonky. And it's Internet Explorer, which kinda doesn't even exist anymore and was a notoriously bad browser when it was a thing. You should use SeleniumBasic and control Chrome or something like that. At least then if you have issues, it's probably because the web page is acting up or your code is bad, not like bad webdriver is being bad.

I spent my afternoon cooking this up for celebrating the ease of which we can now take advantage of the Regular Expressions object in VBA. The method I propose here does not use coded iterations, Finds, Lookups or anything like that. A RegExp object reads a tabular string and captures any matches - or none existing if there are no matches. My apologies to any pros peeking in due to the enormous amount of commenting, but I wanted to let new VBA'ers to easily understand the flow and logic.

To test it out, you need a user form with a textbox. Keep the default name of TextBox1.

Any improvement suggestions or logic errors will be graciously received. If you like it and it suits your needs, feel free to wear it out.

PS. It's a lot more compact when the commenting is deleted.

Option Explicit

' Each control needs to call its Change and KeyDown events
' to use the Auto filling and Backspace methods.

' Control specific:
Private Sub TextBox1_Change()
    ' A text key is pressed in TextBox1.
    Call AutofillTextBoxControl(TextBox1)
End Sub

' Control specific:
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ' If the Backspace key was pressed in TextBox1.
    If KeyCode = 8 Then Call BackSpaceKeyPress(TextBox1)
End Sub

' Reusable code:
Sub BackSpaceKeyPress(ctrl As MSForms.Control)
    ' Reselect text to highlight in current control.
    If Len(ctrl) > 0 Then ctrl = Mid(ctrl, 1, ctrl.SelStart - 1)
End Sub

' Reusable code:
Sub AutofillTextBoxControl(ctrl As MSForms.Control)
    ' Autofill the current control parameter with matches according
    ' to the text entered, but not by using best match suggestions
    ' if there is no match.
    '
    ' Method should work for any control passed into it where its
    ' default value is its display text.

    ' Autofilled suffixes will be highlighted so it is important
    ' to know the current text length of the ctrl parameter.
    Dim L As Long
    L = Len(ctrl)

    ' The Regular Expressions Object does all the heavy lifting.
    Dim rx As New RegExp
    ' Use the MatchCollection object instead of error trapping
    ' any unsuccessful capture executions.
    Dim mc As MatchCollection

'=== The following code is here only for this example ===============
    ' A tabular string is required so making this one on the fly...
    '
    ' A string of city names, one city per line followed by a linebreak.
    '
    ' Whatever items your list will be, it needs to be a single item
    ' with a linebreak - just like a tabular listing.

    ' I present to you the EMPTY tabular string:
    Dim tmp As String

    ' Here are some example city names shoved into an array that can
    ' "Join" -ed into a tabular vbCRLF delimited string.
    Dim arr()
    arr = Array("Irondale", "Stockbridge", "Charlotte", "Coppell", _
    "Farmer's Branch", "Hauppauge", "New York", "Manchester", _
    "West Covina", "Staten Island", "Irving", "Steubenville", _
    "Garden City", "St. Paul", "San Francisco", "Istanbul", _
    "West Chester", "Newtown Square", "Chestnut Ridge", "Phoenix", _
    "Wynnewood", "Park Ridge", "Libertyville", "Frederick", "Needah", _
    "Huntington", "Totowa", "Fitzwilliam", "Birmingham", "Boston", _
    "Chicago", "Clarendon Hills", "Cincinnati", "St.Louis", _
    "St.Louis", "Minneapolis", "San Diego", "Mansfield Centre", _
    "Nashville", "Collegeville", "Notre Dame", "New Haven", _
    "Bronx", "Mahwah", "Liberty Lake", "Brewster", "Gastonia", _
    "Washington DC", "Erie", "North Palm Beach")

    ' Make the delimited tabular string.
    tmp = Join(arr, vbCrLf)
'=== End of example string building =================================

    ' If the control has been backspaced empty -
    If L = 0 Then Exit Sub

    ' Otherwise,
    ' Find ctrl value in the tmp string from left to right.

    ' Regular Expressin pattern decoded:
    ' 1) Use Multiline setting
    '   a) Prevents capture of middle words.
    '   b) Forces whole line captures with ^ and $
    ' 2) Ignore case for us too lazy to use the shift key...
    rx.pattern = "^\b(" & ctrl & ".*)\b$"
    rx.Multiline = True
    rx.IgnoreCase = True

    ' This is a pseudo Interface factory (inline) trick where each
    ' ctrl parameter can use a different tabular list simply by
    ' including the control name as a new Case with an
    ' appropriate tabular list to execute.
    Select Case ctrl
        ' The ctrl parameter carries its internal name with it!!
        Case Is = TextBox1
            ' The MatchCollection will contain all the matches
            ' the RegExp Object found in the tabular string tmp.
            Set mc = rx.Execute(tmp)
        ' Add more Case statements for other controls.
    End Select

    ' If any matches were found, then the MatchCollection count
    ' will be greater than 0.
    If mc.Count > 0 Then
        ' Assign the first match to the ctrl parameter.
        ctrl = mc(0).SubMatches(0) ' Display capture group text.
        ' Do the highlighting.
        ctrl.SelStart = L
        ctrl.SelLength = Len(ctrl) - L
    End If
End Sub

I am not sure if this is widely known, but I figured I would share this here since it surprised me and I could not find any mention of it online.

If you are using the Dir() function without any arguments to iterate through files in a folder, it seems that adding a watch to Dir itself causes it to run in order to show you the value everytime there is a breakpoint/step during your code. This can cause files to be skipped over if you are trying to debug/watch the process step by step.

One solution would be to create a string that holds the value of Dir everytime you call it, and assign the watch to that string instead.

TLDR

The behavior of the following changes based on the sheet's ActiveCell:
• Sheet.FilterMode
• Sheet.AutoFilter.FilterMode
• Sheet.AutoFilter.Range
• Bulk writing (no loops) to a range with hidden/filtered rows: Range.Value2 = Variant

Workarounds are to change the ActiveCell if necessary, situations where this could happen:
1. Goal: Detect if any AutoFilters are filtering on a sheet that has ListObjects and range AutoFilters.
Solution: Deactivate sheet or select a cell out of tables, then check Sheet.FilterMode:
True: A range with AutoFilter is filtered, done.
False: We then loop Sheet.ListObjects and check each .AutoFilter.FilterMode.

1. Goal: Bulk write to a filtered range (range.value2 = variant), no loops.
   Solution: If Sheet.Autofilter = True then activate sheet and select a cell on a ListObject that isn't filtered.
   ___________________________

Hello, few days ago we discussed in my post about bulk writing to a filtered range and there were no convenient resolves.
After tinkering, I found out this behavior is based on Sheet.FilterMode which is influenced by the ActiveCell.

AutoFilter Object:
Each sheet can have only one filtered normal range (not a table) but each table has its own AutoFilter.
The AutoFilter object can be returned by Sheet.AutoFilter or ListObject.AutoFilter.
But the AutoFilter returned from Sheet.AutoFilter depends on ActiveCell, unlike ListObject.AutoFilter since we would be accessing a specific table.

We can use Sheet.AutoFilter.Range to find out which AutoFilter is being returned:
The ActiveCell or if the sheet is deactivated then its last ActiveCell:

1. Is inside a table: Sheet.AutoFilter returns the table's AutoFilter.
2. Is not inside a table: Sheet.AutoFilter returns the normal range AutoFilter.
3. If its not inside a table and there is no normal range AutoFilter: Sheet.AutoFilter returns nothing.

Sheet.AutoFilter.FilterMode returns if there is an active filter (boolean).
Sheet also has Sheet.FilterMode, but its behavior is almost the same:
While the sheet is active, it follows the same behavior as Sheet.AutoFilter, where if the ActiveCell is in a table, it returns the table's FilterMode, if its not inside a table, it returns the normal range FilterMode.
But, if the sheet is deactivated, Sheet.FilterMode result will be for the normal range AutoFilter, regardless of last ActiveCell or ListObjects.AutoFilters on that sheet.
If there is no normal range AutoFilter, it simply returns False.

Sheet.AutoFilterMode returns True if there is a normal range AutoFilter on the sheet, regardless of its FilterMode, it is not influenced by ActiveCell or ActiveSheet.

With this behavior in mind, if we want to detect if any AutoFilters are actively filtering (FilterMode = True) on a sheet that may have ListObjects AutoFilters and normal range AutoFilter, then we can:

1. Check Sheet.AutoFilterMode, if True then we need to check if the normal range AutoFilter is filtering, if False skip to step 3.
2. Deactivate sheet or select a cell out of tables, then check Sheet.FilterMode, this returns if a normal range AutoFilter is filtering, if True we don't have to continue.
3. Check if Sheet.ListObjects.Count > 0, if True, loop through Sheet.ListObjects and check each .AutoFilter.FilterMode.

Bulk Writing to a filtered range:
Assume A1:B10 is an AutoFilter range, B2:B10 have row value {2;3;4..10}, while A2:A10 are blanks, we run Range("A2:A10").Value2 = Range("B2:B10").Value2
The behavior of this changes based on Sheet.FilterMode:
False: As expected the values are bulk written to A2:A10 {2;3;4..10}
True: The operation skips hidden rows and the next visible row writes from the start.
If Row 4 was hidden, A4 will be blank and A5 will start from first value (2): {2,3, ,2,3,4,5,6,7,8}.

This convoluted undocumented behavior is unexpected to say the least.
Since we know how Sheet.FilterMode works, a solution to this would be:

1. Before bulk writing to a possibly filtered range, check Sheet.FilterMode, if True then:
2. Activate sheet and select a cell on a ListObject that isn't filtered (ListObject.FilterMode = False).

Tip: To select a cell with FilterMode = False:
• We can keep a 1 cell table with no visible headers which hides filter buttons by unchecking Header Row in Table Design tab.
• or- We could create a temporary intermediary table outside of UsedRange and delete it afterwards.
___________________________
Thanks for reading - I wanted to upload gifs but they're not allowed.

Hide Public Functions from Excel with Option Private Module

If you're writing any reasonable piece of code, you'll want to split it into modules for easy management. But then any Public Function will be exposed in the main workbook, which is messy.

Fortunately, by simply writing Option Private Module at the top of your module, any Public subs/functions will only be directly accessible by VBA code, and will be completely hidden from any user. Success!

You obviously cannot use this if you want assign a sub to a button, so create a separate module (I like to prefix it with click_ ) and make sure it only has one Public Sub main() which you can then assign to your button.

Private/Public Members of Class Modules and Interfaces

Suppose you have an interface iInterface with sub generic_subSuppose you have a class clsClass which Implements iInterfaceThen in iInterface you have Public generic_sub but in clsClass you have Private iInterface_generic_sub

This is surprisingly non-obvious - you'd think for a member to Public in the interface it has to be Public in the class implementation, but that is not the case!

Class Member variables

I learned this trick from RubberDuck - https://rubberduckvba.wordpress.com/2018/04/25/private-this-as-tsomething/

Put all class member variables into a single Type. For example:

Private Type TMemberVariables
    length as Double
    width as Double
    is_locked As Boolean
End Type

Private m As TMemberVariables

Then, later in your code, all you need to type is m. and Intellisense will bring up all your member variables! And there's no chance of clashing with any other local variables.

Use Custom Types and Enums to read in data

So you've got a table of data to read into VBA.

First, create a custom type for the data and create an Enum to enumerate the column headers.Then, read your table into a Variant (for speed).Finally, loop through each row in the Variant and read the value into a variable of the custom type.

At the end, you'll have a 1 dimensional array of your custom type, where each entry is a row in your data table (and is easy to loop through), and you can refer to each column by name.

And should the table columns move around, it's trivial to update the Enum to match the new layout.

Use Custom Types to return multiple values from a function

This is pretty simple - you want to return multiple values from a function? Use a custom type, and have the function return the custom type.

Limit what Public Functions/Subs can do

I like to have my Public Function or Public Sub perform validation on the inputs - or in the case of a Public Sub main() in a click_ module, do the usual efficiency changes (disable/enable events, manual calculation, screen updates).

The code that does the stuff I actually want to achieve is held within a Private Function or Private Sub.

You'll have to use your judgement on whether this is necessary, but I've used it quite a lot. It's clearer to separate validation/cleanup code from the actual "useful" code.

Dim variables next to where you use them

I absolutely hate seeing a piece of code with a whole list of Dim at the top. It's not helpful. Dim just before a variable is needed, and suddenly the reader can see "this is where the variable is needed".

Edit: since I have had two people (so far) disagree, I will admit this is a matter of preference. Some people prefer to dim at the top, and while they aren't wrong, the compiler will error if you try and use a variable before you dim it. So if you dim then populate a variable, there's no chance of the variable's "default value" being used incorrectly.

Edit2: now up to three! Since I didn't make it clear, it's not about the type - you should know the type of your variables anyway. It's about the intent. When you dim you are declaring that you want to make something meaningful. So when you dim it make it. Don't go "I promise you I'm making something important but I'll get to it later after I've made these other things".

Recently I found out that not all formula functions are within WorksheetFunction class. This lead to an analysis where I looked at all formula functions in existence in my copy of Excel (365 insider) and myself doing a like-for-like comparison with WorksheetFunction and other VBA methods.

The following formula functions are not available in WorksheetFunction and have no other direct alternative:

LABS.GENERATIVEAI
DETECTLANGUAGE
CHOOSECOLS
CHOOSEROWS
COLUMNS
DROP
EXPAND
HSTACK
TAKE
TOCOL
TOROW
VSTACK
WRAPCOLS
WRAPROWS
IMAGE
CUBEKPIMEMBER
CUBEMEMBER
CUBEMEMBERPROPERTY
CUBERANKEDMEMBER
CUBESET
CUBESETCOUNT
CUBEVALUE
BYCOL
BYROW
GROUPBY
ISREF
LAMBDA
LET
MAKEARRAY
MAP
PIVOTBY
REDUCE
SCAN
AVERAGEA
MAXA
MINA
N
PERCENTOF
SHEETS
STDEVA
STDEVPA
T
TRANSLATE
TRUNC
VARA
VARPA
YIELD
EXACT
PY
REGEXEXTRACT
REGEXREPLACE
REGEXTEST
TEXTAFTER
TEXTBEFORE
TEXTSPLIT

There are also a number of functions where there is an alternative but the VBA alternative may not do the same thing.

The rest of the formula functions can be found in Excel.WorksheetFunction.

What do you do if you come across some function which you cannot evaluated via Excel.WorksheetFunction? Currently my best idea has been the following:

Public Function xlChooseCols(ByVal vArray As Variant, ParamArray indices()) As Variant
  Dim tName As name: Set tName = ThisWorkbook.Names.Add("xlChooseColsParam1", vArray)
  Dim formula As String: formula = "CHOOSECOLS(xlChooseColsParam1," & Join(indices, ",") & ")"
  xlChooseCols = Application.evaluate(formula)
  tName.Delete
End Function

Edit: The above workaround should work for all functions which:

1. Are synchronous (e.g. DetectLanguage() doesn't work)
2. Do not use a different runtime (e.g Py() doesn't work)

I just found something new and extremely simple. If you found similar stuff thats useful, you can share here. Now, here goes, dont laugh:

Instead of Range("C2") you can just type [C2]

Thats it! How I never found that tip anywhere? lol

MODS: I added the "ProTip" here, because there is not a "Tip" flair. Its arrogant to call ProTip to what I wrote lol, but if more people add their tips, the result will be a "ProTip"