VBA Financial Modeling and Controls

Table of Contents

    VBA financial modeling can help an analyst run the same forecast under several assumptions without repeatedly typing an input and copying an output. The useful result is a reproducible scenario record. The risk is a neat-looking table built from the wrong workbook, stale formulas or an interrupted run.

    This lesson builds a small, hypothetical desktop Excel example: change sales growth, recalculate an operating model, collect its revenue and EBITDA, and restore the original setup. The worksheet owns the financial formulas; the macro controls repetition. For the broader design of projected statements, start with pro forma financial modeling.

    What Does VBA Do in Financial Modeling?

    Automate a Known Process

    Visual Basic for Applications, or VBA, is a programming language built into desktop Office applications. An Excel macro can write assumptions, trigger calculation and read results. It cannot establish that the forecast assumptions or financial relationships are sensible.

    A recorded macro translates actions into VBA. It can help you discover how Excel represents formatting, cell changes and other operations. A written procedure lets you specify the intended workbook, a scenario loop, input checks and an error path. Recorded code is a starting point: the recorder cannot infer your full business intent.[1]

    For example, a recording may depend on whichever sheet was active. A deliberately written loop can instead say, “Use the Model sheet in the workbook containing this code.” The distinction matters when two similarly named forecasts are open.

    First ask whether code is needed. Ordinary formulas or a one-variable what-if data table already solve the numerical sensitivity in this example. VBA becomes useful when the repeated task also needs labels, checks, a defined result log and controlled cleanup. It adds code that someone must understand and maintain.

    Before Running a Financial-Modeling Macro

    Use a clean demonstration workbook in desktop Excel. Excel for the web can retain macros in a workbook, but cannot create, edit or run VBA. Save this example as an Excel Macro-Enabled Workbook, .xlsm; saving it as .xlsx does not preserve VBA code.[2]

    Desktop Excel for Windows and Mac support VBA, but platform-dependent components can differ. This example avoids Windows API calls, COM automation and external file access. That design does not establish compatibility with every Excel build; test it in your actual supported environment before adapting it.[3]

    Keep Macro Protections in Place

    Run only code you understand and are authorized to use. Office on Windows blocks many macros in files from the internet, subject to version and policy. Do not enable all macros, trust an unknown download or weaken your organization’s controls to follow a tutorial. Use your approved process for code review and trusted workbooks.[4]

    Work in a disposable copy, close unrelated workbooks and save a recovery copy first. The example changes calculation, events and screen updating at the application level. Its contract excludes event-driven formulas, external data, add-ins, user-defined functions, circular calculations and asynchronous work. It never downloads data, saves a file, sends a message or runs automatically on opening.

    Build the Demonstration Worksheet

    Suppose a hypothetical business starts with sales of 1,000 in USD thousands. Forecast sales vary with growth; variable operating costs equal 60% of forecast sales and fixed operating costs remain 200. Model one forecast period, with no interest, taxes, depreciation, amortization, capital expenditure or working-capital schedule.

    Hypothetical Operating Model
    Revenue = Starting sales × (1 + Growth)
    EBITDA = Revenue × (1 − Variable cost share) − Fixed costs
    All monetary values are USD thousands; the two cost assumptions stay fixed across scenarios.

    Create sheets named exactly Model and Runs. Use ordinary, unmerged, unprotected cells and no worksheet/workbook event code. On Model, enter the following labels and values. Format B3 and B4 as percentages; monetary cells contain numbers in thousands, not dollar signs stored as text.

    Model sheet setup: inputs and worksheet formulas
    Label cell and text Value cell Enter Workbook-level name
    A2: Starting sales B2 1000 None required
    A3: Growth B3 0% as a numeric input roc_Growth
    A4: Variable cost share B4 60% as a numeric input None required
    A5: Fixed costs B5 200 None required
    A7: Forecast revenue B7 =B2*(1+B3) roc_Revenue
    A8: EBITDA B8 =B7*(1-B4)-B5 roc_EBITDA

    In Excel’s Name Manager, create those three names with Workbook scope. Their references must be =Model!$B$3, =Model!$B$7 and =Model!$B$8, respectively. Do not create sheet-local versions with the same spelling. The macro checks that each name points to its expected single cell.

    The simplified EBITDA calculation subtracts only the operating costs specified here. It is not net income or free cash flow. If your decision needs investment spending, cash conversion or valuation, build those relationships explicitly rather than relabeling this output.

    Prepare Scenarios and a Scratch Result Block

    On Runs, enter the layout below. Growth values are numeric percentages, so 10% is stored as 0.10. D5:G7 is a dedicated scratch block: the next run replaces it. Archive a successful run separately, with its workbook version and assumptions, if you need history.

    Runs sheet setup: three scenarios and reserved output
    Cells Enter or reserve
    A2 / B2 Enter “Current run status” in A2; leave B2 blank for the macro.
    A4 / B4 Scenario / Growth
    A5 / B5 Downside / -10%
    A6 / B6 Base / 0%
    A7 / B7 Upside / 10%
    D4:G4 Scenario / Growth / Revenue (USD thousands) / EBITDA (USD thousands)
    D5:G7 Leave blank; reserved for a 3-row, 4-column result matrix.

    Format B5:B7 and E5:E7 as percentages, and F5:G7 as numbers with up to two decimal places. A baseline manual calculation at 0% growth should give revenue of 1,000 and EBITDA of 200 before you add VBA.

    A VBA Scenario Loop With a Cleanup Path

    In the Visual Basic Editor, insert a standard module inside this demonstration workbook and paste the complete example below. Do not put it in PERSONAL.XLSB or an add-in: ThisWorkbook means the workbook containing the code. Use the editor’s Compile VBAProject command to check the module, then run roc_RunScenarios through your approved macro process.[5]

    The procedure uses three fixed scenario rows. Its growth range of −100% through +100% is an example guardrail, not a general forecasting rule. Results become usable only when the current run finishes with COMPLETE – 3 scenarios. The code is an educational pattern; it has been checked by arithmetic and static reasoning here, not executed or compiled in Microsoft Excel.

    Option Explicit
    
    Public Sub roc_RunScenarios()
        Dim wb As Workbook, model As Worksheet, runs As Worksheet
        Dim growth As Range, revenue As Range, ebitda As Range
        Dim resultBlock As Range, runState As Range, cell As Range
        Dim oldCalc As XlCalculation, oldEvents As Boolean
        Dim oldScreen As Boolean, settingsSaved As Boolean
        Dim oldGrowth As Double, inputSaved As Boolean
        Dim data(1 To 3, 1 To 4) As Variant
        Dim i As Long, g As Double, share As Double, scenarioLabel As String
        Dim problem As String, restoreProblems As String
    
        On Error GoTo roc_Failed
        oldCalc = Application.Calculation
        oldEvents = Application.EnableEvents
        oldScreen = Application.ScreenUpdating
        settingsSaved = True
        Set wb = ThisWorkbook
        Set model = wb.Worksheets("Model")
        Set runs = wb.Worksheets("Runs")
        If model.ProtectContents Or runs.ProtectContents Then _
            Err.Raise vbObjectError + 201, , "Use unprotected demo sheets."
        For Each cell In model.Range("B2:B5,B7:B8")
            If cell.MergeCells Then Err.Raise vbObjectError + 202, , "Merged model cell."
        Next cell
        For Each cell In runs.Range("B2,A5:B7,D5:G7")
            If cell.MergeCells Then Err.Raise vbObjectError + 203, , "Merged run cell."
        Next cell
    
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Set runState = runs.Range("B2")
        Set resultBlock = runs.Range("D5:G7")
        runState.Value2 = "RUNNING - RESULTS INVALID"
        resultBlock.ClearContents
        Set growth = roc_Cell(wb, model, "roc_Growth", "$B$3")
        Set revenue = roc_Cell(wb, model, "roc_Revenue", "$B$7")
        Set ebitda = roc_Cell(wb, model, "roc_EBITDA", "$B$8")
        If growth.HasFormula Then _
            Err.Raise vbObjectError + 204, , "Growth must be an input constant."
        If Not revenue.HasFormula Or Not ebitda.HasFormula Then _
            Err.Raise vbObjectError + 205, , "Outputs must contain formulas."
        oldGrowth = roc_Number(growth)
        inputSaved = True
        share = roc_Number(model.Range("B4"))
        If roc_Number(model.Range("B2")) < 0 Or _
           roc_Number(model.Range("B5")) < 0 Or share < 0 Or share > 1 Then _
            Err.Raise vbObjectError + 206, , "Invalid cost or sales input."
        If oldGrowth < -1 Or oldGrowth > 1 Then _
            Err.Raise vbObjectError + 207, , "Original growth is outside demo bounds."
    
        For i = 1 To 3
            If runs.Cells(i + 4, 1).HasFormula Then _
                Err.Raise vbObjectError + 216, , "Scenario label must be a constant."
            If VarType(runs.Cells(i + 4, 1).Value2) <> vbString Then _
                Err.Raise vbObjectError + 208, , "Scenario label must be text."
            scenarioLabel = Trim$(CStr(runs.Cells(i + 4, 1).Value2))
            If Len(scenarioLabel) = 0 Then _
                Err.Raise vbObjectError + 209, , "Empty label."
            If InStr(1, "=+-@", Left$(scenarioLabel, 1), vbBinaryCompare) > 0 Then _
                Err.Raise vbObjectError + 217, , "Use a plain-text scenario label."
            g = roc_Number(runs.Cells(i + 4, 2))
            If g < -1 Or g > 1 Then _
                Err.Raise vbObjectError + 210, , "Scenario growth outside demo bounds."
            growth.Value2 = g
            model.Calculate
            data(i, 1) = scenarioLabel
            data(i, 2) = g
            data(i, 3) = roc_Number(revenue)
            data(i, 4) = roc_Number(ebitda)
        Next i
        resultBlock.Value2 = data
        GoTo roc_Cleanup
    
    roc_Failed:
        problem = "Error " & CStr(Err.Number) & ": " & Err.Description
        Resume roc_Cleanup
    
    roc_Cleanup:
        On Error Resume Next
        If inputSaved Then
            Err.Clear
            growth.Value2 = oldGrowth
            If Err.Number <> 0 Then restoreProblems = restoreProblems & " input"
            Err.Clear
            model.Calculate
            If Err.Number <> 0 Then restoreProblems = restoreProblems & " calculation"
        End If
        If settingsSaved Then
            Err.Clear
            Application.Calculation = oldCalc
            If Err.Number <> 0 Then restoreProblems = restoreProblems & " mode"
            Err.Clear
            Application.EnableEvents = oldEvents
            If Err.Number <> 0 Then restoreProblems = restoreProblems & " events"
            Err.Clear
            Application.ScreenUpdating = oldScreen
            If Err.Number <> 0 Then restoreProblems = restoreProblems & " screen"
        End If
        If Len(restoreProblems) > 0 Then _
            problem = problem & " Restoration failed:" & restoreProblems
        Err.Clear
        If Not runState Is Nothing Then
            If Len(problem) = 0 Then
                runState.Value2 = "COMPLETE - 3 scenarios"
            Else
                runState.Value2 = "FAILED - RESULTS INVALID"
            End If
            If Err.Number <> 0 Then problem = problem & " Status write failed."
        End If
        On Error GoTo 0
        If Len(problem) > 0 Then
            MsgBox "FAILED - RESULTS INVALID. " & problem, vbCritical
        Else
            MsgBox "COMPLETE - 3 scenarios", vbInformation
        End If
    End Sub
    
    Private Function roc_Cell(ByVal wb As Workbook, ByVal ws As Worksheet, _
                              ByVal itemName As String, ByVal expected As String) As Range
        Dim r As Range
        Set r = wb.Names(itemName).RefersToRange
        If Not (r.Parent Is ws) Then _
            Err.Raise vbObjectError + 211, , "Wrong sheet: " & itemName
        If r.CountLarge <> 1 Or r.Address <> expected Then _
            Err.Raise vbObjectError + 212, , "Wrong cell: " & itemName
        Set roc_Cell = r
    End Function
    
    Private Function roc_Number(ByVal r As Range) As Double
        Dim v As Variant
        v = r.Value2
        If IsError(v) Then Err.Raise vbObjectError + 213, , "Cell contains an error."
        If IsEmpty(v) Or VarType(v) = vbString Or VarType(v) = vbBoolean Then _
            Err.Raise vbObjectError + 214, , "Expected a numeric cell value."
        If Not IsNumeric(v) Then Err.Raise vbObjectError + 215, , "Non-numeric value."
        roc_Number = CDbl(v)
    End Function

    Understand the Controls Before Reusing the Code

    Explicit references. The procedure qualifies worksheets through ThisWorkbook; it never relies on Select, Activate or the current selection. The helper checks named ranges against both the expected sheet and cell address. Option Explicit requires declarations, helping catch misspelled variable names. None of these checks proves that the formulas themselves are correct.[5]

    Write, calculate, read. Each iteration changes growth, calls model.Calculate and reads the two worksheet outputs through Value2. All dependencies in this fixture are on Model, so worksheet calculation is the chosen scope. A workbook with links, queries or other sheets needs a separately designed calculation/refresh sequence; simply copying this line is insufficient.[6]

    Stage results, then mark completion. The loop collects a 3-by-4 matrix in memory and writes it to the matching contiguous scratch block after all three scenarios finish. It requires constant text labels and rejects formula-like leading characters before copying them. The worksheet status stays invalid until cleanup also succeeds. A range write is not a database transaction: if an error leaves visible values, FAILED – RESULTS INVALID still means they must not be used.

    Restore what was there. The saved growth value and application settings return through one cleanup path after success or a trapped runtime error. If events started False, they return to False; if calculation started Manual, it returns to Manual. Cleanup attempts every restoration even when another restoration fails, and reports which step failed rather than displaying a success message.[7]

    On Error Resume Next is confined to cleanup and the final status attempt, where each error is inspected. Using it throughout the scenario loop could silently skip failed calculations and produce a misleading table. A forced stop, the VBA End statement or an Excel crash can bypass cleanup; recovery copies and an inspection of the actual settings still matter.

    Benchmark the Results and Test a Failure

    Before trusting the loop, change growth manually through −10%, 0% and +10%, calculating after each change. Keep starting sales at 1,000, variable costs at 60% and fixed costs at 200, then restore growth to zero. The expected arithmetic is below; both the manual worksheet and a successful macro run should match it.

    Expected benchmark, not an Excel execution record
    Scenario Revenue calculation EBITDA calculation
    Downside: −10% 1,000 × 0.90 = 900 900 − (900 × 0.60) − 200 = 160
    Base: 0% 1,000 × 1.00 = 1,000 1,000 − (1,000 × 0.60) − 200 = 200
    Upside: +10% 1,000 × 1.10 = 1,100 1,100 − (1,100 × 0.60) − 200 = 240

    Each 10 percentage-point increase in growth adds 100 of revenue and 100 × 40% = 40 of EBITDA, in USD thousands. That pattern follows the constant cost share; it is not evidence that real costs remain proportional. For related interpretation, distinguish the relevant profit margins before comparing models.

    No intermediate rounding is needed for these benchmark values. When checking stored numbers, allow a small floating-point tolerance, such as 0.000001 in the model’s thousand-dollar units, rather than comparing formatted text. Match all three scenario labels, growth inputs and both outputs, not just the Base row.

    Deliberate Failure Exercise

    To inspect the settings, choose View > Immediate Window in the Visual Basic Editor. Enter Debug.Print Application.Calculation, Debug.Print Application.EnableEvents and Debug.Print Application.ScreenUpdating separately, pressing Enter after each command. These commands read the properties without changing them. Record the returned calculation-mode value and the two True/False values, then repeat the same commands after the macro finishes to compare them.[7]

    In the disposable copy, record the initial growth and application settings, then change Runs!B6 to numeric 150%. This breaches the example’s +100% bound. The first scenario temporarily sets growth to −10%; the second should stop the run before the staged matrix is copied.

    1. Expect a failed-run dialog and FAILED - RESULTS INVALID in B2, with no usable output in D5:G7. Never treat a remaining number as a completed scenario record.
    2. Check that Model!B3 returns to its original 0% and the Model formulas again show the original case. Check the actual calculation, events and screen-updating settings against their recorded starting values.
    3. Repeat with calculation initially Manual and events/screen updating initially False. Those initial values must remain Manual/False/False afterward. This checks restoration without assuming the defaults.
    4. Restore Runs!B6 to 0% and rerun the valid three-scenario benchmark. Archive results only after a fresh successful dialog and COMPLETE status.

    These are expected outcomes from static tracing, not a claim that the exercise was run in Excel for this article. You can also remove a required name to check preflight failure. If a missing sheet, protection or an unwritable status cell prevents the worksheet marker, the failed-run dialog takes precedence over any old COMPLETE label. A restoration-failure message requires inspecting the named settings and recovering the workbook before continuing.

    Version Control and Model Checks

    Keep a dated workbook version, a short changelog and a recovery copy. Export standard VBA modules as text for meaningful code diffs, then keep those exports aligned with the actual workbook being used. A module diff does not capture every change to a binary workbook: formulas, named ranges, links, input assumptions and sheet structure need their own review.

    A proportionate record for a reusable scenario process
    Control Record or check
    Identity Workbook/module version, owner, date, Excel platform/build and permitted use.
    Input provenance Scenario labels, numeric inputs, units, fixed assumptions and source dates.
    Output checks Manual benchmarks, formula-error checks, reconciliation tests and run status.
    Change review Code/text diff plus changed formulas, names, dependencies and assumptions.
    Recovery and history Original settings, known-good copy, successful result archive and failed-run notes.

    Limit editing access to trusted storage and identify who maintains the workbook. For a consequential decision, have someone other than the author challenge the assumptions and benchmark the implementation. These are practical controls for the example, not a declaration that every spreadsheet falls under a bank’s supervisory framework. Our model risk management guide discusses the broader governance subject.

    Separate three questions: Did the loop run correctly? Did the worksheet implement the intended equations? Are those equations and assumptions suitable for the decision? A COMPLETE flag addresses only the checked execution path. For example, carrying this EBITDA output into a DCF valuation would still require an appropriate cash-flow forecast and discount-rate framework.

    VBA vs. Formulas, Power Query and Python

    Choose the tool around the repeated task and the people who will maintain it. A finance team can use several tools in the same process; none is universally faster, safer or easier.

    Choose the smallest tool that fits the task
    Tool Useful task Boundary to check
    Formulas / what-if data tables Visible model relationships and one- or two-input sensitivities. Built-in what-if data tables support one or two variable inputs; calculation options can affect updates.
    VBA Repeat desktop workbook input, calculation and output operations with custom checks. Macro trust, Excel state, dependencies, platform and code maintenance.
    Power Query Connect, clean, combine and load recurring data, then refresh the transformation steps. Connectors, credentials, refresh behavior and platform/version support.
    External Python A separately managed data-analysis or file-processing pipeline. Its Python environment, libraries and Excel-file or automation interface require their own maintenance.
    Office Scripts Supported cross-platform Excel automation and Power Automate workflows. A different API from VBA; check current account/license and feature availability.

    Microsoft’s what-if data table is an analysis feature, not merely an ordinary formatted Excel Table. Power Query records transformation steps; it is a natural place to prepare recurring input data before running a model.[8] If your task is instead a Python download/export pipeline, see the separate yfinance export to Excel lesson.

    Python in Excel is a separate product experience from local Python. Its calculations run in Microsoft’s cloud, need internet access and depend on qualifying subscription/platform availability. A local installation’s custom packages do not automatically become that environment. Neither should be assumed to be a drop-in replacement for this VBA procedure.[9]

    Common Mistakes

    • Automating before checking the model. A loop can repeat an incorrect formula perfectly. Establish the manual benchmark first.
    • Reading stale results. Write the input, calculate the required dependencies and then read the output. Calculation is not the same operation as refreshing external data.
    • Assuming the active workbook is the target. Bind the intended workbook and worksheets explicitly, and put this example’s module in the demonstration workbook.
    • Replacing a formula input unintentionally. The example rejects a formula in the growth input; a more general process must preserve whatever it is designed to replace.
    • Resetting every setting to a preferred default. Restore saved values. Turning events on when they began off changes someone else’s session state.
    • Keeping partial results as if they were complete. Treat the scratch block as invalid unless the current run and cleanup succeed.
    • Confusing a text-code diff with workbook review. Review the financial formulas, names and data alongside the module export.

    Limitations and a Reuse Checklist

    This Pattern Has a Deliberately Small Contract

    The demonstration handles three fixed scenarios, two local formula outputs and ordinary numeric inputs. It does not coordinate query refreshes, iterative debt/interest models, linked workbooks, simultaneous users or unattended processing. Expanding those dependencies requires a new design and appropriate execution checks.

    Validation of cell types and ranges is also limited: the macro checks that output cells contain formulas, not that they contain the intended formulas. Disabling events is appropriate only because this example has no event-dependent logic. A handled runtime error follows cleanup; a forced stop or application failure may not.

    Before reusing the pattern, confirm the intended workbook identity, fixed cell/name mapping, units and input limits; map all calculation/refresh dependencies; independently benchmark the financial outputs; test success and intentional failure on the actual Excel environment; verify restoration; and preserve a usable recovery copy plus a versioned run record. If ordinary formulas or a data table accomplish the task clearly, keep that simpler workflow.

    Frequently Asked Questions

    Recording is useful for learning the object model and capturing an initial sequence. Repeated financial-model runs usually need deliberate references, input checks, scenario labels and cleanup that a recording cannot infer. Read and revise recorded code before relying on it.

    Excel for the web does not run VBA. Desktop Excel for Mac supports VBA, but dependencies such as Windows-specific APIs or COM components can limit portability. This example avoids those components; actual compatibility still needs checking on your Excel build.

    Check whether the intended input actually changed, whether output formulas depend on it and whether their dependencies calculated before the read. Wrong references, Manual calculation or an unfinished external refresh can each be involved. Identical outputs can also be economically correct for an insensitive model, so investigate the relationship rather than assuming a single cause.

    Design the macro to save the original settings before changing them and use one cleanup path for success and handled errors. Verify the saved values afterward, including an initially Manual calculation mode or disabled events. If execution was forcibly stopped or cleanup reported an error, inspect and restore the known original settings through your recovery process; do not assume every setting should be True or Automatic.

    It can replace VBA used merely to import and reshape recurring data. It serves a different purpose from an imperative loop that changes workbook inputs and records formula outputs. Choose the required data transformation, model calculation and workflow steps first; they may use different tools.

    No. It shows that the checked execution path finished. Formula accuracy, input quality, economic assumptions and suitability for the decision need separate evaluation. Compare outputs with independent calculations and review changes to both the code and the workbook.

    Sources

    Microsoft documentation accessed November 13, 2026. Feature availability, policies and account requirements can change.

    1. Microsoft Learn, Getting started with VBA in Office: automation, alternatives and macro recording.
    2. Microsoft Support, Work with VBA macros in Excel for the web and supported Excel file formats.
    3. Microsoft Learn, Differences between Office Scripts and VBA macros and Office for Mac VBA overview.
    4. Microsoft Learn, Macros from the internet are blocked by default in Office.
    5. Microsoft Learn, ThisWorkbook, Name.RefersToRange and Option Explicit.
    6. Microsoft Learn, Calculate, Range.Value2 and range values and array assignment.
    7. Microsoft Learn, Calculation, EnableEvents, ScreenUpdating, On Error, Use the Immediate window and Print method.
    8. Microsoft Support, Calculate multiple results by using a data table and About Power Query in Excel.
    9. Microsoft Support, Introduction to Python in Excel.
    Disclaimer

    This article and its code are for educational purposes, not financial, investment, legal or information-security advice. The business, assumptions and outputs are hypothetical. The example was checked by arithmetic and static reasoning, not executed in Microsoft Excel for this article. Review and test any adapted code in a disposable copy under your organization’s policies before using it for a real decision.