How to Write Effective VBA Prompts

Get professional, working VBA code on your first try by describing your needs clearly and specifically

Try It Now

Common Problem

Most users struggle to get working VBA code because their prompts are too vague. "Format my spreadsheet" or "Sort my data" doesn't give the AI enough information to create code that works for your specific situation.

The Golden Rule

If you were explaining this to a colleague who can't see your screen, would they understand exactly what you need?

Essential Components of a Good Prompt

What You Want to Achieve

Start with the end goal. What should happen when the macro runs?

✓ Good Examples:

"Generate a monthly sales report"
"Remove duplicate customer entries"
"Send automated emails to clients"
"Create a pivot table from raw data"

Where Your Data Lives

Specify exact cell ranges, sheet names, or data locations.

✓ Good Examples:

"Data is in columns A through E, rows 2 to 500"
"Names are in column A, emails in column B"
"The active sheet is 'Sales Data'"
"Headers are in row 1"

Specific Details and Requirements

Include formatting, conditions, and any special rules.

✓ Good Examples:

"Highlight totals in yellow with bold text"
"Sort by date descending, then by amount"
"Add borders to all cells with data"
"Skip rows where column C is empty"

How to Handle Errors

What should happen if something goes wrong?

✓ Good Examples:

"Show a message box if no data is found"
"Skip invalid email addresses"
"Ask user to select the file if path doesn't exist"
"Display error message if sheet is protected"

Before & After Examples

❌ Vague Prompt:

"Format my data"

✓ Specific Prompt:

"Apply formatting to my customer data in columns A to D. Make row 1 headers bold with a light blue background (RGB 200, 230, 255). Add borders to all data cells. Alternate row colours - white for odd rows, light grey (RGB 240, 240, 240) for even rows. Auto-fit all column widths."

❌ Vague Prompt:

"Remove duplicates from my list"

✓ Specific Prompt:

"Remove duplicate email addresses from column B in my 'Contacts' sheet. Keep the first occurrence and delete subsequent duplicates. After removing duplicates, display a message box showing how many records were removed. Data starts in row 2 (row 1 is headers)."

❌ Vague Prompt:

"Create a report"

✓ Specific Prompt:

"Create a monthly sales summary report in a new sheet called 'Summary'. Calculate total sales from column E, average sale from column E, and count of transactions. Display results in cells B2:B4 with labels in A2:A4. Add a column chart below the summary showing sales by product category (column C). Format the chart title as 'Monthly Sales by Category'."

💻 Platform Compatibility: Mac vs Windows

VBA code can work differently on Mac and Windows. To get code that works on your platform, always specify which one you're using.

Mac / LibreOffice

✓ When to specify:

Using Excel for Mac
Using LibreOffice Calc
Need cross-platform code

📝 Add to your prompt:

"This code needs to work on Mac" or "Make this Mac-compatible"

Windows

✓ When to specify:

Using Excel for Windows
Need advanced features (UserForms, etc.)
File system operations

📝 Add to your prompt:

"This code is for Windows Excel" or "Use Windows features"

Important: Always specify your platform

If you don't specify, you may receive Windows-specific code that won't work on Mac. Mentioning "Mac", "macOS", "LibreOffice", or "cross-platform" ensures you get compatible code.

Pre-Submit Checklist

Before hitting "Generate", ask yourself:

Have I specified my platform (Mac/Windows) if using platform-specific features?

Have I specified exactly which columns/cells contain my data?

Have I described the exact formatting or output I want?

Have I mentioned how to handle empty cells or errors?

Could someone unfamiliar with my spreadsheet understand what I need?

Have I included specific examples or sample values if relevant?

Common Mistakes to Avoid

Using "my data" or "this column"

The AI can't see your screen. Always specify exact column letters or range addresses.

Assuming it knows your goal

"Process my spreadsheet" is too vague. Specify what processing means - sort? filter? calculate?

Not mentioning data structure

Tell us if you have headers, how many rows typically, whether data is continuous or has gaps.

Requesting too much at once

Break complex tasks into smaller, focused requests. Get one part working before adding more.

Ready-to-Use Prompt Templates

Copy these templates and fill in the [PLACEHOLDERS] with your specific details:

📊 Data Formatting Template

Format my data in columns [COLUMN LETTERS] starting from row [ROW NUMBER]. Make the headers in row [ROW NUMBER] [FORMATTING DETAILS]. Apply [BORDER/COLOUR/FONT DETAILS] to the data cells. [ANY CONDITIONAL FORMATTING RULES].

🔍 Data Cleaning Template

Remove [duplicates/blanks/specific values] from column [COLUMN LETTER] in my [SHEET NAME] sheet. The data starts in row [ROW NUMBER] and currently has approximately [NUMBER] rows. [KEEP FIRST/LAST] occurrence of duplicates. Show a message box with the count of rows removed.

📈 Report Generation Template

Create a [FREQUENCY] report from my data in sheet [SHEET NAME]. Calculate [METRICS TO CALCULATE] from columns [COLUMN LETTERS]. Place the results in a new sheet called [NEW SHEET NAME] with [LAYOUT DESCRIPTION]. Include a [CHART TYPE] chart showing [CHART DATA DESCRIPTION].

📁 File Operations Template

Import data from [FILE TYPE] files in folder [FOLDER PATH]. Each file has [STRUCTURE DESCRIPTION]. Combine all data into the current sheet starting at row [ROW NUMBER]. [FILE NAMING PATTERN]. Handle errors by [ERROR HANDLING APPROACH].

Ready to Generate Perfect VBA Code?

Use what you've learnt to create a clear, specific prompt and get professional VBA code in seconds

Generate VBA Code Now