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]

I use 365, and it works just fine for me.

Are you sure that you have a named range with name?
Are you sure that the range is exactly one cell in size?
Are you sure that the entry in that cell has been entered as a valid date, and not been entered as text instead?

 

[rjmdc]

this book worked forever
i tink its an error in a new update that if VBA and Power query use the smae named range name it fails

 

[Joe4]

this book worked forever
i tink its an error in a new update that if VBA and Power query use the smae named range name it fails

I am not sure what you are saying.
Do you have two different ranges using the same name, perhaps on different sheets?

What is returned if you run this little procedure?

Sub MyTest()
    MsgBox "Address of range is: " & Range("UIBilling_Month").Address
    MsgBox "Is range a date?: " & Application.WorksheetFunction.IsNumber(Range("UIBilling_Month"))
End Sub

 

[rjmdc]

hi
this workbook worked till august 2024
seems like a new update in office 365 doesnt allow the same nme for a range to be used in both power querry and in VBA
i cannot get around it

 

[RoryA]

Do you mean you have a query in PQ with the same name as the named range?

 

[rjmdc]

yes always ddi
every month i use the book to create billing
it worked as is from 2020 through July 2024
now it failed
it seems like a VBA update thats weird

 

[RoryA]

Can't you just rename one of them?

 

[Joe4]

seems like a new update in office 365 doesnt allow the same nme for a range to be used in both power querry and in VBA

Seems like it would be a good practice to use different names for different things.
Using the same name only invites unnecessary confusion/issues.

 

[rjmdc]

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