Named range issue with VBA (Run-time error 1004)

[dotsent]

Hi

I'm running similar (not identical) Excel VBA code in 2 different environments. However, both with Microsoft/Office 365 packages and the same Excel v2406 (build 17726.20126)

Both codes have been running fine for months if not years. But similar error messages since this morning (fresh updates for Excel 365?). From now on I can't address SOME named ranges in Excel all of a sudden (not all are affected). For an example:

Range("API_input").Activate

gives Run-time error 1004 (this named range obviously exists - and has been existing ever since this code has been running). If I rename the affected named range into something else, e.g. "API_input_x" then addressing the named range in VBA code will work. However renaming, saving and changing back into original name, won't change anything. Also .xlsm -> .xlsx -> .xlsm saving didn't fix the issue.

Are other people experiencing named range problems with fresh Excel?
This is just a random code example how I test, in reality the VBA code inserts a string variable into named range.

 

[dmt32]

Hi
is the sheet your name range refers to Active when you run the code?

Dave

 

[dotsent]

No, named range is not on ActiveSheet.
.Activate method indeed works only with ActiveSheet, hence the example above was not the best. However I get the same error also when the named range IS on ActiveSheet.

In reality the code is more like this (and worked like a charm for months and months):

Dim ws As Worksheet
Dim myTable As Listobject
Dim myRow As Listrow
Dim myVariable As String

Set ws = ThisWorkbook.Sheets("mySheet")
Set myTable = ws.ListObjects("myTable")

myVariable = myRow.Range(myTable.ListColumns("myColumn").Index)
Range("API_input") = myVariable

Set myTable = Nothing
Set ws = Nothing

(ws is not necessarily ActiveSheet and also named range "API_input" can be on another worksheet)

 

[dmt32]

No, named range is not on ActiveSheet.
.Activate method indeed works only with ActiveSheet, hence the example above was not the best. However I get the same error also when the named range IS on ActiveSheet.

Always helpful if you share the actual code that is causing the issue with the Forum from outset.

If as you say, you get the issue even when sheet is active then I have no immediate thoughts as to why but maybe others here can assist.
It would be helpful to forum if you are able to share copy of the worksheet using MrExcel XL2BB Addin

Dave

 

[dotsent]

Yes, I'm sorry for being vague, but I can't share the entire file. Which is always a nuisance in those situations, of course.

Did some additional testing and it's likely NOT a VBA issue anyway. If I open the sheet with affected named range, select it from the named range dropdown menu in the upper left corner, nothing happens (range should activate). Just like it wouldn't exist. If I delete the named range, create it again, no help. If I rename it, it will come alive.

However, I tend to believe it's a bug.

I have 2 machines in different environments with Excel v2406 build 17726.20126 - having this issue.
Third PC has v2406 Build 17726.20160 - and don't experience the issues (all with Windows 10).

"Update now" from Excel Account page gives immediate "You're up to date" screen with build 20160. Meanwhile build 20126 will start to download updates, but that seemingly can take hours with nothing happening.

Looks very much like some bug with build 20126.
I suppose the real question now is how to upgrade to build 20160. Weird as I don't get many responses in Google for that.

 

[dmt32]

Was not asking for entire file just sheet you are having problems with but no worry if cannot share it.
Can only speculate about your issue - it may, as you suggest, be a MS build issue or perhaps your workbook may have a corruption in this case, you may want to try Repairing a workbook but I would, just as a precaution, recommend that you make a back up before doing so.

Hope manage to resolve - sorry could not be of any further help

Dave

 

[dotsent]

No worries, thanks for taking the time.

After further investigation the title is not accurate anymore. I managed to update Office build for other computers as well, but unfortunately this was not really a fix. Also, it can't be a corrupted workbook issue since I have archived copies and old versions act the same. Open-repair function didn't work either. Still, given the issues started on the same day in 2 different environments, I suspect this is caused by recent Excel update.

However, I discovered why some named ranges work fine, meanwhile others are non-responsive in Excel UI and cause an error when addressing via VBA.

Some of the named ranges (singular cells) have Data connections linked to them (they are used in PowerQuery as query parameters). Has worked great well over a year. But suddenly those named ranges that have data connections have broken down. Some pointers:

1. Data connections themselves and related queries still work fine - however VBA is used to add values in the named ranges as query parameter input. But as VBA can't interact with those named ranges anymore in an automated way, it has become useless as is. Even though manual data manipulation in the named range and query refresh still works.

2. If data connection to the named range is deleted, named range is again fully functional. Named ranges that didn't have data connections to begin with, haven't been affected.

3. If I change the name of the named range (cell positioning can remain the same) and adjust range name convention in the data connection, it will work again. This has been my fix so far. The unfortunate part is, however, that I have no confidence right now that it wouldn't happen again at some point.