Share via

VBA Error when there is a range name that is also a Power Query name

Anonymous
2024-07-23T17:36:58+00:00

This is a new error that has only cropped up in the last few days (so presumably an update)

I often have a named range that I use in VBA, but also load to Power Query to use as a parameter (e.g. a filter criteria). The PQ then has the same name as the range name. This has never been a problem and I've used the method many times.

Now I'm finding that, when my VBA code refers to the named range, I get a Run-time error. When I debug, the error is because VBA can no longer find the named range. The range name still exists and is used in formulas throughout the workbook.

However, if I create a formula in a new cell which references the range name (e.g. SetMasterData) I see this instead...

=_xlpq.SetMasterData

and the cell shows a #CALC! error. When I start to type =SetM... the selection shows two items called "SetMasterData" but I get the same result whichever one I choose.

It seems that there is a naming conflict that's been introduced in an update.

I have resolved this in a couple of files by changing the range name and then modifying my code and the power query to refer to the new name but this issue could be affecting dozens of files!

Edit: Also just found that original formulas using the range name aren't affected, but if I edit the formula it changes the range name to _xlpq.SetMasterData and causes an error!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-07-24T02:32:32+00:00

    I'd suggest you raise it to Microsoft directly for such bug.

    Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. Support team there will have the correct channel and resources to help you investigate more and find what exactly the reason has caused this situation.

    Get support - Microsoft 365 admin | Microsoft Learn

    About how to find the admin in your organization, you can refer to How do I find my Office 365 admin

    If the issue comes from an update. You may try rollbacking office update as workaround and wait Microsoft fix it.

    ODT tool

    How to revert to an earlier version of Office - Microsoft Support

    Or CMD

    1. Start an Office application (such as Excel), and then select File > Account. select Update Options > Disable Updates.
    2. Type cmd on the start menu, right click on the Command Prompt, choose Run as Administrator
    3. Copy/paste these two commands one at a time and press enter

    cd %programfiles%\Common Files\Microsoft Shared\ClickToRun

    officec2rclient.exe /update user updatetoversion=16.0.17628.20164

    Here is version history. Change the 16.0.17628.20164 to the version number based on your update channel.

    Update history for Microsoft 365 Apps (listed by date) - Office release notes | Microsoft Learn

    0 comments