named range in VBA failing

[rjmdc]

did microsoft 365 make a change in using named ranges?
books that worked last week are all failing
how can i fix it?
Dim Smo As String: Smo = Format(Range("UIBilling_Month"), "m-yyyy")

 

[Joe4]

too much code
i will need to roll back to a previous version of 365 until microsoft creates a fix

It could be this is the fix! So you could be waiting a while (if not forever).

It is usually considered poor programming to use the same variable/object names for different items (it is best practice to NOT to do that).
The ambiguity can cause errors and unexpected results (how is VBA to determine which one of the two you are referring to?).

People often run into similar issues when they choose the names of existing functions, properties, or objects as names of their variables, procedures, or functions (i.e. if you created a function and named it "SUM", when you use SUM, how is Excel to know if you mean the native Excel function, or your custom function).

Even though it may be a pain, it would probably be advised to correct the situation. And it is only a one-time fix!
You can use "Find" within VBA to quickly find all references to that value ("UIBilling_Month") in your code.

 

[rjmdc]

i renamed the range
but that created other errors
i renamed to BSSD_Month
then this failed
UIMonthFrom = Worksheets("BSSD StateBilling").Range("BSSD_Month")
NewRow(1, 2) = UIMonthFrom

i cant use the refrence to the same named range again?

 

[RoryA]

Wouldn't it be less work to rename the query in PQ?

 

[rjmdc]

not at this time
9 queries use the named range
i am slowly seeing if i can change the named ranges in VBA
if not i will roll back to a previous version

 

[RoryA]

To be clear, I didn't ask if you had queries that use the named range. I asked if you have a query that has the same name as the named range?

 

[rjmdc]

no the query filters etc on the named ranges

 

[Alex Blakenburg]

Interesting I can replicate the problem but don't have an alternative to renaming the query or named range.
The error only occurs if both the Named Range and Query name are the same. If I add an X to the end of the query name the code works fine.

Code:

Sub testRangeName()
    Debug.Print Sheet1.Range("Data").Address
End Sub

Error:

Range Name = Data

Query Name = Data

 

[rjmdc]

slowly i am running an extensiveprocess as it fails i rename the ranges it asks me to

 

[Alex Blakenburg]

I am sure Rory can come up with a better way but this seems to work:

Dim Smo As String: Smo = Format(Range(ActiveWorkbook.Names("UIBilling_Month")), "m-yyyy")

 

[rjmdc]

hi
my work around was to createnew named ranges and refer to taht
so that means each worksheet has extra data as i didnt know a better worksround
ex 1 cell named UIBilling_From so the other cell is named UIBilling_Fr both have the same data
i will need to update BOTH cells before performing my project execution
1 is for the Power queries the other for the VBA
i am sure there is a better way but this worked for now