VBA & Macro Problems

How to Fix XLOOKUP and INDIRECT Formula Errors in VBA

Chanuka Geekiyanage Oct 21, 2026 868 views

Question details

The user is experiencing syntax and runtime errors when dynamically constructing XLOOKUP and INDIRECT formulas using Excel VBA.

Product: Excel VBA
Device & OS: not provided
Scenario: Writing a VBA macro to dynamically generate and insert XLOOKUP or INDIRECT functions into spreadsheet cells.
Observed behavior: The macro fails to execute or outputs a broken formula due to improperly nested quotation marks and incorrect variable concatenation.

Before you start

Ensure you are familiar with how VBA handles string concatenation, and remember that text strings inside an Excel formula require special escaping when written in VBA.

Solution 1Recommended

Properly Escape Quotation Marks and Use String Variables

The most reliable way to build complex formulas like XLOOKUP and INDIRECT dynamically is to separate cell references into variables and properly double-escape the quotation marks within the formula string.

When Excel VBA writes a formula to a cell using the .Formula property, it passes a string. If your formula natively contains quotation marks (like INDIRECT("A1")), you must type double-double quotes in the VBA string to prevent the compiler from reading it as the end of the text.

Declare String Variables

Define string variables for your column letters or row numbers to keep the main formula clean (e.g., Dim MatchIn As String).

Construct Sub-Strings

Build the dynamic cell references outside the main formula string. For example, combine the column letter and row number: MatchIn = ColLet & "$1".

Double-Escape Quotes

When writing the main Selection.Formula string, ensure any double quotes meant to appear inside the cell formula are typed twice (e.g., ""Text"" instead of "Text").

Debug the Formula String

Before assigning the formula to the cell, write Debug.Print followed by your formula string. Open the Immediate Window (Ctrl+G) in the VBA Editor to verify the output looks like a valid Excel formula.

Assign to Cell

Once verified, assign the completed concatenated string to the target range using Selection.Formula = ...

Properly Escape Quotation Marks and Use String Variables

Debugging Tip: Using Debug.Print to print the constructed formula string to the Immediate Window is the fastest way to spot missing commas, ampersands, or quotation marks before a runtime error occurs.

Solution 2

Adjust VBA Error Handling Settings

Improper use of error suppression can mask the exact line where your formula string concatenation fails. Adjusting your error handling helps pinpoint the bug.

Advanced Spreadsheet Macros

Use WPS Spreadsheet for Robust VBA and Formula Handling

WPS Office provides full support for advanced Excel functions like XLOOKUP and INDIRECT, as well as a comprehensive VBA macro environment. You can seamlessly run, write, and debug your macros in WPS Spreadsheet.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled workbook.
2. Access the VBA Editor: Navigate to the 'Tools' tab and click on 'Developer' or 'VBA Editor' to launch the coding environment.
3. Write and Debug Code: Enter your VBA code with proper string concatenation and utilize the built-in Immediate Window to instantly debug your formula output.

Fully compatible with Microsoft Excel VBA syntax and macros (.xlsm).Native support for modern advanced functions including XLOOKUP.Built-in VBA editor for easy debugging and code generation.Lightweight, fast, and free to download.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a Run-time error '1004' when assigning a formula via VBA?

This error frequently occurs because the formula string passed to `Selection.Formula` is syntactically incorrect. It is often caused by missing double quotes around text criteria, incorrect variable concatenation, or a missing parenthesis. Use `Debug.Print` to inspect the text string being sent to the cell.

How do I insert double quotes inside a VBA string formula?

To insert a literal double quote within a VBA string, you must type two double quotes consecutively. For example, to output the formula `=INDIRECT("A1")`, your VBA string needs to be written as `"=INDIRECT(""A1"")"`.

Does WPS Office support XLOOKUP inside VBA macros?

Yes, WPS Spreadsheet fully supports the XLOOKUP function. As long as your version of WPS Office is updated, you can use XLOOKUP both in regular worksheet cells and construct it dynamically using VBA macros.

Excel & Data Problems