Editing date to DD-mmm-YYYY format

Question

I am looking to format a date in Excel properly using VBA. I have looked at other solutions and it hasn't helped.

I am using VBA to take an entry from the page, format it, then edit it into an SQL string that will go through to an Oracle database.

I need the date to be in DD-mmm-YYYY format, "17-Oct-2017" using today's date.

My code:

Range("A1").Select
Selection.NumberFormat = "d-mmm-yyyy"
date= UserSheet1.Cells(1, 1)

In the cell, it appears properly, but when I reference the "date" variable in my code, it appears as 10/17/2017.

Why does it have to be DD-MON-YYYY? Oracle accepts multiple data formats including DD/MM/YYYY. — MT0
@MT0 you are almost definitely right, but maybe the way my group has the DB configured, it rejects it? I get an error if it isn't presented the other way unfortunately :( — wundermahn
Please give a minimal reproducible example including the code you are using to pass the values to Oracle. As it stands it appears to be an XY-problem that might be solved by fixing how you are passing dates to Oracle rather than putting the date into a different format. — MT0
@Scott Craner's answer fixed it without a problem, so, that is unnecessary. Plus, I don't think it helps to paste in 598 lines of code. As soon as I used his solution, my program worked properly. Regardless, thank you for your suggestion. — wundermahn

Scott Craner Scott Craner · Accepted Answer · 2017-10-17T13:27:41

do not use .Select it slows down the code.
Use the .Text to return the text in the cell otherwise you are returning the .Value, which is a number, the number of days since 1/1/1900.

so:

Range("A1").NumberFormat = "d-mmm-yyyy"
date= UserSheet1.Cells(1, 1).Text