Getting value from named range that was defined as a formula

Mary90

New Member
Joined
Sep 8, 2015
Messages
22
Good day guys!

I'm trying to use a hidden named range to hold the value of a date that I can refer to in my VBA formulae.
So I've created a named range called "ExpiryDate" and it does not refer to a cell, but directly to: "=DATE(2018,12,31)"

So using vba, I'd expect
Code:
names("ExpiryDate").refersTo
to be "=DATE(2018,12,31)" and
Code:
names("ExpiryDate").value
to be "31 Dec 2018" (or 43465 or whatever date format).
However, it returns the same as the "refersTo" code (including the "=" as the first character)

How can I get the actual date value to be returned so I can set it equal to an integer variable?

Thanks in advance.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You could try [ExpiryDate].
 
Upvote 0
It's short hand for Evaluate, so you could also use Evaluate("ExpiryDate") if you want to make things clearer - I was just being lazy.:)
 
Upvote 0

Forum statistics

Threads
1,216,022
Messages
6,128,330
Members
449,442
Latest member
CaptBrownShoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top