Cat On A Spreadsheet

Cat On A Spreadsheet

Creating Excel VBA Add-Ins: Reusable Tools for Your Team

If you often build utilities, custom functions, or macros that multiple people use, packaging them into an Excel Add-In (.xlam) is the most effective way to distribute and maintain them.

 

This post will guide you through:

  • What an Add-In is

  • How to build one

  • Best practices for distribution within your team

 

💡 What is an Excel Add-In?

 

An Add-In is an Excel file saved in .xlam format containing VBA code, UDFs, forms, and modules. When installed, it:

 

✅ Loads automatically with Excel

✅ Makes functions and procedures available in any workbook

✅ Keeps your code protected and centralised

 

🛠️ Building Your First Add-In

 

1. Develop Your Functions or Tools

 

Start with a module containing useful functions. For example:

Function ReverseText(ByVal inputText As String) As String
     Dim i As Long
     Dim result As String
     For i = Len(inputText) To 1 Step -1
          result = result & Mid(inputText, i, 1)
     Next i
     ReverseText = result
End Function

 

This simple function reverses text input in any worksheet.

 

2. Save as Add-In

 

  • Click File > Save As

  • Choose Excel Add-In (*.xlam)

  • Name it clearly, e.g. MyUtilities.xlam

 

3. Install the Add-In

 

  • Go to File > Options > Add-Ins

  • At the bottom, next to Manage: Excel Add-ins, click Go...

  • Click Browse…, locate your .xlam file, and tick it to load

 

Your functions are now available in any workbook without needing to import modules each time.

 

🔒 Protecting Your Code

 

To prevent accidental edits or to protect intellectual property:

  • Open the VBA editor (ALT + F11)

  • Right-click your project > VBAProject Properties

  • Go to Protection tab, tick Lock project for viewing, and set a password

  • Save again as .xlam

 

🚀 Distributing to Your Team

 

✅ Shared Drive: Place the .xlam in a shared location and instruct users to install via Browse… as above.

✅ Network Location: Set up a read-only network folder containing your Add-In files for controlled updates.

✅ Version Control: Include version numbers in filenames or internally within the add-in to manage updates seamlessly.

 

⚠️ Key Considerations

 

  • Relative Paths: Avoid hardcoded file paths in Add-Ins unless you manage folder structures centrally.

  • Updates: Inform users to overwrite old versions and restart Excel.

  • Compatibility: Ensure the Add-In is tested on all target Excel versions in your team.

 

💡 Tips for Effective Team Add-Ins

 

✅ Create a ribbon tab with your macros for easier access (future blog topic)

✅ Maintain a Change Log inside your Add-In

✅ Keep your Add-In modular, adding only necessary functions to avoid bloat

 

🔚 Final Thoughts

 

Excel Add-Ins transform scattered macros into professional, reusable, and maintainable tools for your team. They simplify onboarding, standardise your solutions, and save hours of manual copy-pasting code between files.

Sub ParseLogFileRegex()
Dim FilePath As String
Dim FileNum As Integer
Dim LineText As String
Dim RowNum As Long
Dim regEx As Object
Dim matches As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "\[(.*?)\]\s+(\w+):\s+(.*)"
regEx.Global = False
regEx.IgnoreCase = True
FilePath = "C:\Logs\AppLog.txt"
FileNum = FreeFile
Open FilePath For Input As #FileNum
RowNum = 2
Do While Not EOF(FileNum)
     Line Input #FileNum, LineText
     Set matches = regEx.Execute(LineText)

     If matches.Count > 0 Then
          With ThisWorkbook.Sheets(1)
               .Cells(RowNum, 1).Value = matches(0).SubMatches(0) ' Timestamp
               .Cells(RowNum, 2).Value = matches(0).SubMatches(1) ' Log level
               .Cells(RowNum, 3).Value = matches(0).SubMatches(2) ' Message
          End With
          RowNum = RowNum + 1
     End If
Loop
Close #FileNum

Set regEx = Nothing
End Sub

 

🔍 Pattern Explanation

 

Pattern                                                  Matches

\[(.*?)\]                                                       Anything inside square brackets (timestamp)

\s+                                                              One or more spaces

(\w+)                                                          Log level (INFO, ERROR, etc.)

:                                                                   Literal colon

\s+                                                              One or more spaces

(.*)                                                              The entire message

 

🛠️ Other Useful Patterns

 

Extract email addresses

[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}

Extract numbers

\d+

Extract words inside quotes

"([^"]*)"

 

🔚 Final Thoughts

 

Regex-based parsing transforms your log file handling from brittle string splits to robust, pattern-driven data extraction. Combine this with dictionaries or advanced UDF libraries to build efficient parsers for any data source.

Sub ParseLine(ByVal LineText As String, ByVal RowNum As Long)
Dim TimeStamp As String
Dim LogLevel As String
Dim Message As String
Dim StartPos As Long
Dim EndPos As Long

' Extract timestamp (between [ and ])
StartPos = InStr(1, LineText, "[") + 1
EndPos = InStr(1, LineText, "]") - 1
TimeStamp = Mid(LineText, StartPos, EndPos - StartPos + 1)

' Extract log level (after ] and before :)
StartPos = InStr(1, LineText, "]") + 2
EndPos = InStr(1, LineText, ":") - 1
LogLevel = Mid(LineText, StartPos, EndPos - StartPos + 1)

' Extract message (after the colon)
Message = Mid(LineText, InStr(1, LineText, ":") + 2)

' Output to Excel
With ThisWorkbook.Sheets(1)
     .Cells(RowNum, 1).Value = TimeStamp
     .Cells(RowNum, 2).Value = LogLevel
     .Cells(RowNum, 3).Value = Message
End With
End Sub

 

🧼 Step 3: Setup Headers (Optional)

 

You might want to add headers for clarity:

With ThisWorkbook.Sheets(1)
     .Range("A1").Value = "Timestamp"
     .Range("B1").Value = "Log Level"
     .Range("C1").Value = "Message"
End With
End Sub

 

You can call this sub before running ParseLogFile.

 

⚠️ Bonus: Handling Irregular Lines

 

Some logs may have missing or malformed entries. Use error handling:

On Error Resume Next
' ... parsing logic ...
If Err.Number <> 0 Then
     Message = "PARSE ERROR: " & LineText
     Err.Clear
End If
On Error GoTo 0

 

🧠 Why Use VBA for This?

 

While tools like Python are more common for text processing, VBA offers:

  • Native integration with Excel

  • Immediate visualization and manipulation of parsed data

  • A great option for non-developers working in Excel-driven environments

 

🔚 Final Thoughts

 

VBA is more than just forms and macros—it’s a lightweight data processing engine built right into Excel. With a bit of string manipulation and file handling, you can turn chaotic log files into structured, actionable data in minutes.

 

Want to go further? Try:

  • Parsing multi-line log entries

  • Highlighting rows with ERROR or WARN

  • Exporting results as a CSV or Excel table

07 July 2025

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet