Automate Excel with VBA – A Complete Guide for Businesses

Automate Excel with VBA – A Complete Guide for Businesses

You spend 5 hours every week copying data between Excel files, updating reports and sending the same report to 15 different recipients. With VBA you can automate all of this. Here is how.

In my ten years as an Excel consultant I have seen companies save hundreds of hours every month by automating their Excel workflows. This is not theory from a textbook – these are real solutions that my team and I have built for clients ranging from Volvo to small businesses in Stockholm.

In this guide you get a complete introduction to VBA (Visual Basic for Applications), Excel’s built-in programming language. Whether you have never written a line of code or already know a little VBA, you will get concrete tools for automating your most time-consuming tasks.

Why automate Excel?

Before we dive into the technology we need to talk about why. I often meet finance directors and controllers who say ”we’re doing fine as it is”. Then we discover that their team spends 15-20 hours every month on manual work that a macro would do in 30 seconds.

Time savings are the obvious benefit. When a client contacted us about their monthly reporting, three people spent every month-end compiling data from 47 different Excel files. The process took two full days. We built a VBA solution that did the same thing in 12 minutes. That is 48 working hours saved every month.

Fewer errors make a bigger difference than you think. People make mistakes, especially with repetitive tasks. You copy from the wrong cell, forget to update a formula, send the wrong version to the customer. One of our clients in the accounting industry discovered that manual data transfers led to errors in about 3% of entries. That sounds small, but when you handle thousands of transactions it becomes a significant problem.

VBA code does exactly the same thing every time. If you program it correctly the first time, you get consistently correct results. Yes, the code can have bugs – but once they are fixed, they stay fixed.

Scalability becomes possible. As your business grows, Excel usage often grows too. Suddenly you don’t have 5 files but 50. Not 100 rows but 10,000. Manual processes that worked three years ago become impossible to maintain.

I worked with a growing e-commerce company that handled order data in Excel. When they had 50 orders a day, manual handling worked. At 500 orders the system collapsed. Their solution was to hire more people for data processing. Our solution was a VBA system that handled 5,000 orders as easily as 50.

The team gets to do what they are good at. This is perhaps the most underestimated benefit. Your employees were not trained to copy data between files for five hours. They were trained for analysis, strategy and customer contact.

When we automate repetitive work we free up capacity for what actually creates value. A finance professional who spends 10 hours less on report production has 10 more hours to analyse the figures and make recommendations.

At Excel Department we see automation as an investment, not a cost. The initial time spent to build a VBA system pays back quickly, and keeps delivering value year after year.

VBA for beginners – The basics

VBA stands for Visual Basic for Applications. It is Microsoft’s programming language, built into the whole Office suite but used mostly in Excel. Think of it as Excel’s engine bay – the surface is the worksheet you see, but underneath there is an engine where you can program almost anything.

Where do you find the VBA editor? Press Alt + F11 in Excel and the VBA environment opens. Here you see three main areas: the project explorer on the left (showing your workbooks and modules), the properties window below it, and the large code editor in the middle.

If you don’t see the Developer tab in the ribbon: go to File > Options > Customize Ribbon and tick ”Developer”. From there you can also open the VBA editor, record macros and manage security settings.

Your first macro can be as simple as this:

Sub MittFörstaMakro()
    MsgBox "Hej! Det här är ditt första VBA-makro."
End Sub

Enter the code in a module (Insert > Module in the VBA editor), press F5 or click the play button. A dialog box appears with your message. Congratulations – you have written and run VBA code.

Basic syntax follows clear patterns. Every macro starts with Sub (for ”subroutine”) followed by a name and parentheses. It ends with End Sub. Everything in between is code that runs from top to bottom.

Sub ExempelPåSyntax()
    ' Det här är en kommentar (förklarande text)
    Dim mittvärde As Integer  ' Deklarerar en variabel
    mittvärde = 42            ' Tilldelar ett värde

    ' Skriv till cell A1
    Range("A1").Value = mittvärde

    ' Villkor
    If mittvärde > 40 Then
        MsgBox "Värdet är större än 40"
    End If
End Sub

Some important concepts:

• Range refers to cells (Range(”A1”), Range(”B2:B10”))
• Cells is an alternative way using row/column (Cells(1,1) = A1)
• Dim declares variables (storage spaces for data)
• If/Then/Else handles conditions
• For/Next and Do/Loop are used for repetition

The macro recorder vs manual coding. Many people start with the macro recorder (Developer > Record Macro). You perform actions in Excel and the recorder saves them as VBA code. It is useful for seeing how things translate into code, but recorded code often becomes messy and inefficient.

I recommend using the recorder to learn syntax, then rewriting the code by hand. When you record ”select cell A1, make it bold, type ’Hello’” you may get 15 lines of code. The same thing can be done in 2 lines of hand-written code.

In our Excel courses we always spend the first hour on exactly the basics: the VBA environment, simple syntax, the difference between objects and methods. That knowledge is the foundation for everything else.

The object model is VBA’s logical structure. Application (Excel itself) contains Workbooks, which contain Worksheets, which contain Range objects (cells). Each level has properties (values) and methods (actions).

' Objekthierarki i praktiken
Application.Workbooks("Rapport.xlsx").Worksheets("Jan").Range("A1").Value = 100

You don’t need to write the whole hierarchy every time – VBA understands context. But understanding the structure helps enormously when you troubleshoot or build advanced solutions.

The most important lesson for beginners: start simple. Don’t try to build a complex system from day one. Build a macro that does one thing, get it working, understand every line of code. Then build on it.

Top scenarios for Excel automation

After thousands of consulting hours I have seen which tasks truly benefit from automation. Here are the scenarios where VBA has the greatest effect.

1. Data import from external sources

How often do you copy data from CSV files, text files or other Excel files into your main report? With VBA you automate the whole flow.

Sub ImporteraCSV()
    Dim filnamn As String
    Dim ws As Worksheet

    ' Välj fil
    filnamn = Application.GetOpenFilename("CSV-filer (*.csv), *.csv")

    If filnamn <> "False" Then
        ' Skapa nytt blad för import
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "Importerad Data"

        ' Öppna och importera CSV
        With ws.QueryTables.Add(Connection:="TEXT;" & filnamn, _
            Destination:=ws.Range("A1"))
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With

        MsgBox "Import klar!"
    End If
End Sub

This code lets the user choose a CSV file, creates a new sheet and imports the data with correct formatting. No manual copying, no pasted values that suddenly become text instead of numbers.

2. Repetitive reporting

Monthly reports that always follow the same structure are perfect for automation. We built a system for a finance client where one button generates a 40-page report with charts, tables and analysis based on the latest month’s data.

Sub GenereraRapport()
    Dim wsData As Worksheet
    Dim wsRapport As Worksheet
    Dim sistaRad As Long

    Set wsData = ThisWorkbook.Worksheets("RåData")
    Set wsRapport = ThisWorkbook.Worksheets("Rapport")

    ' Hitta sista raden med data
    sistaRad = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    ' Rensa gammal rapport
    wsRapport.Range("A2:Z1000").ClearContents

    ' Kopiera och bearbeta data
    wsData.Range("A2:E" & sistaRad).Copy
    wsRapport.Range("A2").PasteSpecial xlPasteValues

    ' Formatera rapport
    With wsRapport.Range("A1:E1")
        .Font.Bold = True
        .Interior.Color = RGB(68, 114, 196)
        .Font.Color = RGB(255, 255, 255)
    End With

    ' Lägg till summering
    wsRapport.Range("E" & sistaRad + 2).Formula = "=SUM(E2:E" & sistaRad & ")"

    Application.CutCopyMode = False
    MsgBox "Rapport genererad med " & sistaRad - 1 & " rader data"
End Sub

3. Email directly from Excel

A scenario I see often: you have a customer list in Excel and need to send personal emails to each customer. Manually it takes hours. With VBA it takes minutes.

Sub SkickaPersonligaEmail()
    Dim OutlookApp As Object
    Dim Mail As Object
    Dim ws As Worksheet
    Dim sistaRad As Long
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Kunder")
    sistaRad = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Set OutlookApp = CreateObject("Outlook.Application")

    For i = 2 To sistaRad  ' Börja rad 2 (rad 1 är rubriker)
        Set Mail = OutlookApp.CreateItem(0)

        With Mail
            .To = ws.Cells(i, 2).Value  ' Kolumn B = e-post
            .Subject = "Hej " & ws.Cells(i, 1).Value  ' Kolumn A = namn
            .Body = "Bäste " & ws.Cells(i, 1).Value & "," & vbNewLine & vbNewLine & _
                    "Tack för ditt intresse. Bifogat finner du information om..." & _
                    vbNewLine & vbNewLine & "Vänliga hälsningar," & vbNewLine & "Ditt företag"
            .Send  ' Använd .Display istället för att granska innan skickning
        End With
    Next i

    Set Mail = Nothing
    Set OutlookApp = Nothing

    MsgBox "Skickat " & (sistaRad - 1) & " e-postmeddelanden"
End Sub

4. Advanced formatting and data validation

Formatting large data sets takes time. VBA does it in seconds, consistently across hundreds or thousands of rows.

A real example from a logistics client: they receive delivery data with 300+ rows daily. Colour coding based on delivery status, bold text for delayed orders, automatic sorting. It used to take 30 minutes manually. Now it takes 3 seconds.

5. Integration between Excel and other systems

VBA can communicate with databases (SQL, Access), fetch data from web services, update SharePoint lists, and integrate with other Office programs. At Excel Department we have built solutions that sync Excel with the company’s CRM, ERP systems and Power BI reports.

6. Complex calculations and data processing

Some calculations are too complicated or resource-intensive for ordinary formulas. VBA can iterate through thousands of rows, run complex algorithms and handle data that ordinary Excel functions struggle with.

The key to successful automation is to identify tasks that are:

• Repetitive (done often)
• Time-consuming (take more than 10 minutes manually)
• Rule-based (follow clear steps)
• Error-prone (risk of human mistakes)

If your task meets three of the four criteria, it is probably worth automating.

How to build your first macro (Step by step)

Now we build something concrete: a macro that imports sales data, removes duplicates, sorts by date and creates a summary. This is a real scenario I have seen at dozens of clients.

Step 1: Plan before you code

The biggest mistake beginners make is jumping straight into the code. Plan first. Write down what the macro should do:

1. Import data from the chosen Excel file
2. Remove duplicate rows based on order number
3. Sort by date (newest first)
4. Create a summary: number of orders, total sales
5. Format the result

Step 2: Open the VBA editor and create a module

Press Alt + F11 in Excel. In the VBA editor, go to Insert > Module. An empty module is created where you write the code.

Step 3: Create the basic structure

Start with an empty macro and build it up piece by piece:

Sub BearbetaFörsäljningsdata()
    ' Deklarera variabler
    Dim wbKälla As Workbook
    Dim wsKälla As Worksheet
    Dim wsMål As Worksheet
    Dim sistaRad As Long
    Dim filväg As String

    ' Inställningar för snabbare körning
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Kod kommer här

    ' Återställ inställningar
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    MsgBox "Bearbetning klar!", vbInformation
End Sub

Step 4: Implement the file import

Sub BearbetaFörsäljningsdata()
    Dim wbKälla As Workbook
    Dim wsKälla As Worksheet
    Dim wsMål As Worksheet
    Dim sistaRad As Long
    Dim filväg As String

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Låt användaren välja fil
    filväg = Application.GetOpenFilename("Excel-filer (*.xlsx;*.xls), *.xlsx;*.xls")

    If filväg = "False" Then
        MsgBox "Ingen fil vald. Avbryter.", vbExclamation
        Exit Sub
    End If

    ' Öppna källfil
    Set wbKälla = Workbooks.Open(filväg)
    Set wsKälla = wbKälla.Worksheets(1)  ' Första bladet

    ' Skapa eller rensa målblad
    On Error Resume Next
    Set wsMål = ThisWorkbook.Worksheets("Bearbetad Data")
    On Error GoTo 0

    If wsMål Is Nothing Then
        Set wsMål = ThisWorkbook.Worksheets.Add
        wsMål.Name = "Bearbetad Data"
    Else
        wsMål.Cells.Clear
    End If

    ' Kopiera data
    sistaRad = wsKälla.Cells(wsKälla.Rows.Count, "A").End(xlUp).Row
    wsKälla.Range("A1:E" & sistaRad).Copy wsMål.Range("A1")

    ' Stäng källfil utan att spara
    wbKälla.Close SaveChanges:=False

Step 5: Remove duplicates

    ' Ta bort dubbletter baserat på kolumn A (ordernummer)
    sistaRad = wsMål.Cells(wsMål.Rows.Count, "A").End(xlUp).Row

    wsMål.Range("A1:E" & sistaRad).RemoveDuplicates _
        Columns:=1, Header:=xlYes

Step 6: Sort the data

    ' Uppdatera sistaRad efter borttagning
    sistaRad = wsMål.Cells(wsMål.Rows.Count, "A").End(xlUp).Row

    ' Sortera efter kolumn C (datum), nyast först
    With wsMål.Sort
        .SortFields.Clear
        .SortFields.Add Key:=wsMål.Range("C2:C" & sistaRad), _
            SortOn:=xlSortOnValues, Order:=xlDescending
        .SetRange wsMål.Range("A1:E" & sistaRad)
        .Header = xlYes
        .Apply
    End With

Step 7: Create the summary

    ' Lägg till sammanfattning
    Dim antalOrdrar As Long
    Dim totalFörsäljning As Double

    antalOrdrar = sistaRad - 1  ' Minus rubrikrad
    totalFörsäljning = Application.WorksheetFunction.Sum(wsMål.Range("E2:E" & sistaRad))

    ' Skriv sammanfattning under data
    wsMål.Range("A" & sistaRad + 2).Value = "SAMMANFATTNING"
    wsMål.Range("A" & sistaRad + 3).Value = "Antal ordrar:"
    wsMål.Range("B" & sistaRad + 3).Value = antalOrdrar
    wsMål.Range("A" & sistaRad + 4).Value = "Total försäljning:"
    wsMål.Range("B" & sistaRad + 4).Value = totalFörsäljning
    wsMål.Range("B" & sistaRad + 4).NumberFormat = "#,##0 kr"

Step 8: Format the result

    ' Formatera rubriker
    With wsMål.Range("A1:E1")
        .Font.Bold = True
        .Font.Size = 12
        .Interior.Color = RGB(68, 114, 196)
        .Font.Color = RGB(255, 255, 255)
    End With

    ' Formatera sammanfattning
    With wsMål.Range("A" & sistaRad + 2 & ":A" & sistaRad + 4)
        .Font.Bold = True
    End With

    ' Justera kolumnbredd
    wsMål.Columns("A:E").AutoFit

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    MsgBox "Bearbetning klar!" & vbNewLine & _
           "Antal ordrar: " & antalOrdrar & vbNewLine & _
           "Total försäljning: " & Format(totalFörsäljning, "#,##0") & " kr", _
           vbInformation, "Klart"
End Sub

Step 9: Test thoroughly

Create a test file with known data. Run the macro. Verify that:

• The import works
• Duplicates are removed correctly
• The sorting is correct
• The calculations add up
• The formatting looks good

Test with different scenarios: an empty file, a file with only headers, a file with thousands of rows.

Step 10: Save with macro support

Excel files with VBA must be saved as .xlsm (Excel Macro-Enabled Workbook). File > Save As > Choose the file type ”Excel Macro-Enabled Workbook”.

Troubleshooting tips:

Use Debug.Print to write values to the Immediate Window (Ctrl+G in the VBA editor):

Debug.Print "Sista rad: " & sistaRad
Debug.Print "Total: " & totalFörsäljning

Set breakpoints (click in the margin next to a line of code) to pause execution and inspect variables.

This complete example shows the power of VBA: over 100 lines of code that replace hours of manual work. Once you have built a macro you can reuse and modify it for similar tasks.

Advanced VBA techniques

Once you master the basics, the door opens to more powerful solutions. Here are techniques I use in almost every major project at Excel Department.

UserForms – Custom dialog boxes

Instead of having users edit code or cells, you create professional interfaces. UserForms are custom windows with buttons, text boxes, dropdown lists and other controls.

Example: a client needed a tool where salespeople enter customer data. Instead of typing directly into the sheet we created a UserForm with validation: the email must contain @, the phone number must be digits, no field may be empty. Data is saved only if everything is correct.

To create a UserForm: Insert > UserForm in the VBA editor. Drag controls from the Toolbox. Double-click buttons to write code:

Private Sub cmdSpara_Click()
    Dim ws As Worksheet
    Dim sistaRad As Long

    ' Validera input
    If txtNamn.Value = "" Or txtEmail.Value = "" Then
        MsgBox "Namn och e-post måste fyllas i", vbExclamation
        Exit Sub
    End If

    If InStr(txtEmail.Value, "@") = 0 Then
        MsgBox "Ogiltig e-postadress", vbExclamation
        Exit Sub
    End If

    ' Spara till blad
    Set ws = ThisWorkbook.Worksheets("Kunder")
    sistaRad = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ws.Cells(sistaRad, 1).Value = txtNamn.Value
    ws.Cells(sistaRad, 2).Value = txtEmail.Value
    ws.Cells(sistaRad, 3).Value = txtTelefon.Value

    MsgBox "Kund sparad!", vbInformation
    Unload Me
End Sub

Error handling that actually works

Code without error handling crashes at the slightest problem. Professional code handles errors gracefully.

Sub RobustImport()
    On Error GoTo FelHanterare

    Dim filväg As String
    Dim wb As Workbook

    filväg = Application.GetOpenFilename()

    If filväg = "False" Then
        MsgBox "Ingen fil vald", vbExclamation
        Exit Sub
    End If

    Set wb = Workbooks.Open(filväg)

    ' Bearbeta data här...

    wb.Close SaveChanges:=False
    Exit Sub

FelHanterare:
    MsgBox "Ett fel uppstod: " & Err.Description & vbNewLine & _
           "Felkod: " & Err.Number, vbCritical

    If Not wb Is Nothing Then
        wb.Close SaveChanges:=False
    End If
End Sub

Performance optimisation

VBA can be slow if you don’t optimise. Three simple tricks give an enormous speed increase:

Sub SnabbKörning()
    ' Stäng av skärm-uppdatering
    Application.ScreenUpdating = False

    ' Stäng av automatisk beräkning
    Application.Calculation = xlCalculationManual

    ' Stäng av events (förhindrar oändliga loopar)
    Application.EnableEvents = False

    ' Din kod här...
    ' Bearbeta 10 000 rader på sekunder istället för minuter

    ' Återställ
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

Avoid reading/writing individual cells in loops. Instead, read the data into arrays, process it in memory, and write it back:

' LÅNGSAMT (kan ta minuter)
For i = 1 To 10000
    Cells(i, 1).Value = Cells(i, 1).Value * 2
Next i

' SNABBT (tar millisekunder)
Dim data As Variant
Dim i As Long

data = Range("A1:A10000").Value  ' Läs in allt på en gång

For i = 1 To 10000
    data(i, 1) = data(i, 1) * 2
Next i

Range("A1:A10000").Value = data  ' Skriv tillbaka allt på en gång

Working with external files

You don’t need to open files to read them. It is faster to use ADO (ActiveX Data Objects):

Sub LäsUtanAttÖppna()
    Dim conn As Object
    Dim rs As Object
    Dim filväg As String

    filväg = "C:\Data\Försäljning.xlsx"

    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & filväg & ";" & _
              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    rs.Open "SELECT * FROM [Sheet1$]", conn

    ' Kopiera data till Excel
    Range("A2").CopyFromRecordset rs

    rs.Close
    conn.Close
End Sub

Database integration

For really large data sets or multi-user systems you need databases. VBA can connect to SQL Server, MySQL, Access:

Sub HämtaFrånSQL()
    Dim conn As Object
    Dim rs As Object

    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    conn.Open "Provider=SQLOLEDB;Data Source=servernamn;" & _
              "Initial Catalog=databasnamn;User ID=användarnamn;Password=lösenord;"

    rs.Open "SELECT * FROM Kunder WHERE Land = 'Sverige'", conn

    Range("A2").CopyFromRecordset rs

    rs.Close
    conn.Close
End Sub

Scheduled execution

VBA cannot be scheduled directly in Excel, but you can use Windows Task Scheduler to open an Excel file whose Auto_Open macro runs on startup:

Sub Auto_Open()
    ' Körs automatiskt när filen öppnas
    Call MinSchemalagdaKörning

    ' Stäng arbetsboken efter körning
    ThisWorkbook.Close SaveChanges:=True
End Sub

Sub MinSchemalagdaKörning()
    ' Din kod här
    ' T.ex. importera data, generera rapport, skicka e-post
End Sub

Then create a task in Windows Task Scheduler that opens the Excel file at 07:00 every day.

At Excel Department we use these techniques daily. When a client needs something more advanced than basic automation, it is usually a combination of UserForms for input, database connections for large data sets, and optimised code for performance.

For really complex systems, or when Excel reaches its limits, we often recommend that you complement with Power BI or build dedicated web applications. Read more in our comparison Power BI vs Excel to understand when to choose which tool. But VBA can take you surprisingly far.

Mistakes to avoid (Pitfalls)

I have reviewed hundreds of VBA projects over the years. The same mistakes turn up again and again. Here are the worst pitfalls – and how to avoid them. For more on Excel problems in general, see our guide to the most common Excel problems.

1. No planning – starting to code straight away

I see it every week: someone spends 10 hours building a complex macro, then realises they solved the wrong problem. Or that there is a much simpler way.

Always spend 30 minutes on planning before you write the first line of code. Draw a flowchart. Write pseudo-code. Discuss with colleagues. Time invested in planning saves hours of rewriting.

2. No comments in the code

You write a macro today. It works perfectly. Six months later you need to change something. You have no idea what the code does or why.

Comment your code as if someone else will maintain it (because they will – perhaps future you):

' DÅLIGT - ingen förklaring
For i = 2 To lr
    If Cells(i, 5).Value > 1000 Then
        Cells(i, 6).Value = Cells(i, 5).Value * 0.15
    End If
Next i

' BRA - tydliga kommentarer
' Beräkna provision på försäljning över 1000 kr (15%)
For i = 2 To sistaRad  ' Börja rad 2 (hoppa över rubriker)
    If Cells(i, 5).Value > 1000 Then  ' Kolumn E = försäljning
        Cells(i, 6).Value = Cells(i, 5).Value * 0.15  ' Kolumn F = provision
    End If
Next i

3. Hard-coded values everywhere

Code full of numbers and text like ”A5”, ”Report 2024”, ”info@företag.se” becomes impossible to maintain. When next year comes you have to hunt through 500 lines of code to change ”2024” to ”2025”.

Use constants and variables:

' DÅLIGT
Range("A1").Value = "Försäljningsrapport 2024"
Range("A2").Value = "info@exempelföretag.se"

' BRA
Const NUVARANDE_ÅR As Integer = 2024
Const FÖRETAGSEMAIL As String = "info@exempelföretag.se"
Const RAPPORTSTART As String = "A1"

Range(RAPPORTSTART).Value = "Försäljningsrapport " & NUVARANDE_ÅR
Range("A2").Value = FÖRETAGSEMAIL

4. No testing

”It worked on my test data” is not the same as ”it works”. Test with:

• Empty data
• Extremely large data sets
• Special characters and strange input
• Users who do the unexpected (you asked for a number, they typed text)

I once saw a macro that worked perfectly with 100 rows of data. At 1,000 rows it ran for 30 minutes. At 10,000 rows Excel crashed. The company discovered the problem when they were about to use it in production.

5. Ignoring security

VBA code can do almost anything on your computer. That includes harmful things. Some security rules:

• Validate all user input
• Don’t use On Error Resume Next everywhere (it hides errors)
• Be careful with code from the internet – review it before you run it
• Protect sensitive information (passwords, API keys)
• Restrict macro permissions in the Excel Trust Center

6. No version control

You change something. It goes wrong. You want to undo. But you overwrote the working version.

For simple projects: save versions with dates (Report_v1_2024-01-15.xlsm). For larger projects: use actual version control such as Git. At Excel Department we use Git even for Excel files, which requires a little extra setup but saves us regularly.

7. Assuming the environment is the same everywhere

Your code works on your computer. A colleague’s Excel crashes. Why? Different Excel versions, different language settings, different installed references.

Write robust code that doesn’t assume too much:

' Kontrollera att bladet finns
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Data")
On Error GoTo 0

If ws Is Nothing Then
    MsgBox "Bladet 'Data' saknas. Makrot kan inte köras.", vbCritical
    Exit Sub
End If

8. Trying to do everything in VBA

VBA is powerful but not always the right tool. Sometimes a simple formula is better than 50 lines of code. Sometimes you really need a database instead of Excel.

In our Excel courses we teach when to use VBA and when not to. If you spend weeks building something in VBA that Power Query does in 10 minutes, you have chosen the wrong tool.

Perhaps the most important lesson: start simple, test often, comment generously. Complexity can always be added later. Simplicity is much harder to add afterwards.

Tools and resources

After teaching hundreds of people VBA over the years I know that the right resources make an enormous difference. Here are the tools and sources I use and recommend myself.

Built-in Excel resources

Macro Recorder is your first teacher. Record actions, study the code, learn syntax. But don’t use recorded code directly in production – it is too messy.

Object Browser (F2 in the VBA editor) shows all available objects, methods and properties. Invaluable when you wonder ”is there a method to do X?”.

Immediate Window (Ctrl+G) is your test environment. Write code directly and see the results:

?Range("A1").Value  [Enter] → visar värdet i A1
Range("B2").Value = 100 [Enter] → sätter B2 till 100

The debugging tools: F8 to step through code line by line, F9 to set breakpoints, Ctrl+Shift+F9 to clear all breakpoints. Learn to use these – they save hours of frustration.

Online communities

Stack Overflow (stackoverflow.com) – tag: [excel-vba]. Almost every problem you run into has already been solved by someone else. Search before you ask, and when you ask: include specific code and describe what goes wrong.

r/vba on Reddit – an active community that helps beginners. More forgiving than Stack Overflow for basic questions.

MrExcel Forum (mrexcel.com) – a specialised Excel forum with a dedicated VBA section. Many experts who respond quickly.

Learning platforms

Microsoft Learn (learn.microsoft.com) – official documentation. Dry reading but 100% accurate. Use it as a reference when you need exact details.

Excel Campus (excelcampus.com) – Jon Acampora’s tutorials are excellent, especially for visual learners.

Wise Owl Tutorials on YouTube – free video series from beginner to advanced. A British accent as a bonus.

For training in Swedish, we at Excel Department offer both basic and advanced courses specifically adapted to Swedish companies and business needs. We often see participants learn faster when the examples come from a Swedish business context (VAT calculations, personal identity number validation, Swedish date formats). Read our guide to Excel courses to find the right training for your needs.

Useful plugins and add-ins

Rubberduck VBA (rubberduckvba.com) – a free add-in that gives the VBA editor modern features: unit testing, code inspections, refactoring tools. Game-changing for serious VBA development.

MZ-Tools (mztools.com) – productivity tools for VBA. Automatic code formatting, copying routines between projects, finding unused code. The free version is enough for most.

AutoMacro (automacro.io) – a code library with hundreds of ready-made functions. Good for learning patterns, but don’t become dependent on it.

Books that are actually worth reading

Most VBA books are either too basic or outdated. Two exceptions:

”Excel VBA Programming For Dummies” by John Walkenbach – despite the name it is genuinely good. Covers the basics properly without being condescending.

”Professional Excel Development” by Rob Bovey et al. – for when you are ready to build real systems. Covers design patterns, testing, deployment.

When you need professional help

I am always honest with clients: sometimes DIY is the right way, sometimes it isn’t. You can learn VBA yourself for simple tasks. But some projects require years of experience.

Signs that you should get professional help:

• The project is business-critical (if it goes wrong it costs a lot of money)
• You have struggled for weeks without progress
• It involves integration with other systems
• Security and data integrity are critical
• You need it done now, not in three months

At Excel Department we offer everything from single consulting hours to help you over an obstacle, to full system development projects. Often the best approach is that we build the basic structure, then teach your team to maintain and develop it further.

Final advice on resources: spend time finding a couple of good sources and get to know them properly, instead of jumping between a hundred different tutorials. Quality over quantity.

Next step – When you need professional help

You have read this guide. Perhaps you have even built your first macro. But now you face a decision: carry on by yourself or bring in expert help?

How far can you get by yourself?

With basic VBA knowledge and time you can solve a lot. Simple automation tasks, standardised reports, data import and simple processing – it is entirely possible to learn these yourself.

I often meet finance professionals and controllers who have built impressive solutions on their own. They have spent evenings and weekends, read documentation, tested and debugged. The result works and saves hours every week.

But there is a cost: your time. If you are a finance director on a monthly salary of SEK 150,000, you may spend 40 hours learning to build something that a VBA specialist does in 6 hours. Quick maths: 40 hours of your time costs the company roughly SEK 25,000 in opportunity cost. The consultant cost for 6 hours? Around SEK 9,000.

When complex systems require expertise

Some projects lie beyond what is reasonable to learn as a hobbyist:

• Multi-user systems where several people work with the same data at the same time
• Integration with external systems (CRM, ERP, databases, web APIs)
• Security-critical solutions where errors can lead to financial losses
• Performance-critical systems that handle hundreds of thousands of rows of data
• Complex user interfaces with advanced controls and validation

For these you need someone who has built similar systems before. Who knows what pitfalls exist. Who can design an architecture that scales as your business grows.

At Excel Department we have built VBA systems for companies from 5 to 5,000 employees. We have seen what works long term and what collapses after six months. That experience cannot be read out of a book.

What we offer

As Excel consultants we work in three main areas:

1. System development – We build complete VBA solutions from scratch. From requirements analysis to implementation, testing and documentation. You get a system that works, is maintainable and comes with full documentation.

2. Optimisation of existing solutions – Do you have a macro that works but is slow? Code that someone built years ago that nobody understands? We review, document and improve.

3. Training and knowledge transfer – We don’t just build solutions and leave. We teach your team how the system works so they can maintain and develop it further. Our Excel courses are tailored to your needs.

A typical project begins with a free 15-minute call where we discuss your needs. Sometimes a few hours of consulting is enough to set you on the right path. Sometimes a full-scale project over several weeks is needed.

We believe in transparency. You always get a clear quote before we start. No unexpectedly inflated invoices. And you own the code we write – no vendor lock-in.

Why clients choose us

Our 5-star Google rating (26 reviews) is based on a few simple principles:

• We listen first, code later. Many consultants jump straight to a technical solution. We spend time understanding your business process.

• We build for the future. Code that works today but is impossible to maintain is worthless in a year. We build systems that your team can work with long term.

• We are good teachers. Technical expertise without the ability to explain is frustrating. We make sure you understand what we have built and why.

• We are realistic. Sometimes VBA is not the right solution. We say so. Sometimes you need Power BI instead. Sometimes ordinary Excel formulas are enough. We always recommend what is best for you, not what gives us the most work.

Get started today

If you have a project in mind, or just want to discuss whether VBA is the right solution for your needs, contact us for a free consultation.

Call +46-10-264-20-20 or email info@exceldepartment.se. You speak directly to me, Lukas, or someone else in our expert team. No sales pitch, just an honest discussion about what can help your business.

Automating Excel is not magic – it is systematic work that saves time, reduces errors and frees your team to do value-creating work. Whether you do it yourself or with our help, the first step is always the same: identify what takes time today, and visualise how it could be automated.

Good luck on your VBA journey. We are here if you need support along the way.