Create Your First Excel Macro with AI

Most people in commercial real estate still think Excel macros are for “the IT guy down the hall.”

You underwrite, you manage rent rolls, you crank out lender packages in Excel all day… but the moment someone says “VBA,” you are out.

That used to make sense. Writing macros meant learning a programming language on top of doing your actual job.

That world is gone.

Today, AI will happily write the VBA for you. Your job is no longer “become a coder.” Your job is “know just enough about the Excel environment so you can plug the code in and make it run.”

Once you get that, macros stop being mysterious and start feeling like leverage on your pipeline, your reporting, and your time.

Watch the walkthrough to create your first macro with AI

In the video, I walk through exactly what I am describing here, using AI to write the code and Excel to run it.

---

The old way vs the new way (in CRE terms)

Old way

• You want to auto‑populate the latest rates using VBA.
• You either do it by hand, or you bribe the Excel wizard on your team to write a macro.
• If it breaks, you are stuck until they fix it.

New way

• You describe what you want in plain English:
  “Write an Excel macro that pulls in the latest yield on the 10-yr UST and put it in cell B2 on the assumptions tab.”
• AI writes the macro.
• You paste it into your workbook and run it.
• If it throws an error, you copy the error back to AI and it fixes the code.

The limiting factor is no longer “do you know VBA.”
The limiting factor is “do you understand the basic Excel macro environment and your role in it.”

That is all this tutorial is: the environment and your responsibility in the loop.

Step 1: Unlock the VBA environment once and move on

You only need to do this setup once on your machine. After that, macros are just another tool in your Excel belt.

Turn on the Developer tab

1. Open Excel.
2. Go to File → Options.
3. Click Customize Ribbon.
4. On the right side, check Developer.
5. Hit OK.

You now have a Developer tab on the ribbon. That is where macro tools live.

Open the VBA editor

Two options:

• Press ALT + F11 (faster), or
• Click Developer → Visual Basic.

You will see a separate window with:

• a project list on the left,
• a big empty code window on the right.

Add a standard Module

This is where you store the code for your first AI‑generated macro.

In the VBA editor:

1. In the left pane, find VBAProject (YourWorkbookName.xlsx).
2. Right‑click it.
3. Choose Insert → Module.

You will see Module1 appear under Modules. A blank code window opens.

That is where your first macro goes. That is it. Door unlocked.

Step 2: Where code is allowed to live (the only version CRE people need)

VBA has a bunch of places code can live, which is where most people get lost.

Standard Module

• Use this for: macros you run on demand or hook to a button.
• Think: “Run this when I say so.”
• Example: “Make a duplicate of a template property-level worksheet to use for portfolio modeling.”

ThisWorkbook

• Use this for: stuff that should happen when the file opens or closes.
• Think: “When this workbook wakes up or goes to sleep, do X.”
• Example: “When the model opens, refresh all data connections.”

Worksheet objects (like Sheet1 (RentRoll))

• Use this for: reactions to things that happen on a specific sheet.
• Think: “On this sheet, when someone edits this cell, run code.”
• Example: “When someone changes the lease end date on the ‘RentRoll’ sheet, recalc a few helper cells and flag anything expiring within 12 months.”

UserForms

• Pop‑up windows with text boxes, dropdowns, etc.
• Nice to have later, totally ignorable at the beginning.

Class Modules

• Advanced stuff. You can go years in CRE Excel work without touching them.

For your first several macros, you can treat this as the main rule:

If you just want to click a button or run a macro by name, use a Standard Module.

That one rule dodges most of the “I pasted the AI code somewhere and nothing happened” problems.

Step 3: The relationship between you, AI, and Excel

In CRE we are used to delegating.

You hand the analyst a messy T12 and ask for historical OpEx line items to be populated. Same idea here. You are not the junior analyst in this setup, AI is.

Here is how the roles actually break down.

AI’s job

• Turn your plain‑English description into a VBA macro.
• Adjust that macro when you show it where it breaks.

Excel’s job

• Store the code in Modules, the workbook, or sheets.
• Run the code when a macro is called or an event fires.

Your job

• Open the right file.
• Put the code in the right type of container (Module, ThisWorkbook, or a sheet).
• Save as a macro‑enabled workbook (.xlsm).
• Run the macro or trigger the event.
• When it misbehaves, capture the error and send it back to AI.

Step 4: How to talk to AI so it can actually help you

“Make my spreadsheet nicer” is not a prompt.

You want a simple checklist that gives AI enough information to write something that works on your file.

Use this pattern:

1. Worksheet name
   “Use the sheet named RentRoll.”
2. Range
   “Data is in A1:K300, headers in row 1.”
3. Trigger vs manual run
   “I will run this manually by clicking a shape.”
   or
   “I want it to run when someone edits column H on RentRoll.”
4. Expected outcome
   Spell out what “success” looks like.

Here is a CRE‑specific example as your “first macro” prompt:

I am using Excel on Windows.

Worksheet: RentRoll
Data: headers in row 1, units in rows 2 through 500, columns A:L.

I want a VBA macro that I will run manually.

The macro should:
1) Remove any completely blank rows in the data range.
2) Standardize all lease start and end dates in columns E and F to real date values.
3) Autofit the columns for A:L.

Please give me code I can paste into a standard Module.

AI will write the code. You just need to know where to put it and how to run it.

Step 5: Paste it in, save, and push the button

Back to Excel and the VBA editor:

1. Make sure you have a Module (like Module1) under Modules.
2. Click inside the empty code window.
3. Paste the macro AI gave you.
4. Hit Ctrl + S and save your workbook as .xlsm (Macro-enabled Workbook) if it is not already.

To run it:

1. Go back to Excel.
2. Press ALT + F8.
3. Pick the macro name from the list.
4. Click Run.

Step 6: Debug like a deal person, not like a programmer

Here is the honest part: the first few times, you will break things.

That is fine. You do not need to think like a software engineer to fix it. You just need a simple way to observe what is happening and describe it back to AI.

Four tools are enough.

1) Step through line by line

In the VBA editor:

1. Click your cursor anywhere inside the macro.
2. Press F8.

VBA will highlight the next line to run in yellow. Press F8 again to move to the next line.

You can literally watch it walk through the logic. Handy when you are not sure which part is doing what.

2) Pay attention to the highlighted error line

When something blows up, VBA:

• shows an error message, and
• highlights a specific line in yellow.

Your move:

• copy the exact error text,
• copy the highlighted line of code,
• copy the whole macro.

Then tell AI:

This macro threw an error.
Error message: <paste message>
Error line: <paste line>

Here is the full macro as I have it pasted:
<paste macro>

Can you fix this?

That is it. You are giving AI the same information you would give a human developer.

3) Drop in cheap “checkpoints” with message boxes

Message boxes are an easy way to see how far the macro is getting.

Add lines like:

MsgBox "Got here"

Example:

Sub CleanRentRoll()
    MsgBox "Macro started"

    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("RentRoll")
    MsgBox "Worksheet set"

    ' rest of the code...
End Sub

Run it.

If you see “Macro started” but not “Worksheet set,” you know the issue is right at or before the Set ws line. That context helps AI zero in on the problem quickly.

4) Loop until it works

The workflow looks like this:

1. Run or step through the macro.
2. When it breaks, grab:
   • the error message,
   • the highlighted line,
   • which message boxes did or did not appear.
3. Paste all of that plus the macro into AI.
4. Get an updated version, paste it back into the same spot.
5. Try again.

---

Why this matters for your CRE career

You already live in Excel: underwriting, asset management, reporting, fundraising decks, lender asks.

Historically, VBA macros were a hard wall:

• If you were “the Excel person,” you wrote them.
• If you were not, you waited in line or you just did it by hand.

AI knocks that wall down.

You no longer need to be “the VBA person.” You just need to:

• know how to open the VBA editor and add a Module,
• know where code is allowed to live,
• give AI clear, concrete instructions,
• copy errors back when things break.

---

Frequently Asked Questions: Create Your First Excel Macro with AI

---