1

Q1. In this function, what does the final "@" mean?

.Format(Sheet1.Range("A1"), "dd/mm/yyyy;@")

[I'm guessing it means "format this field as TEXT" (in addition to the "dd/mm/yyyy" instruction) but I need to be certain.]

Q2. If this means it's possible to combine multiple formatting parameters into one command, is this available in all versions of Excel or it did become available only as of a particular version?

WHY? I publish a commercial add-in and need my code to be compatible with as many versions of Excel as possible.

I need to format dates independently of the operating system's regional settings (in other words, ignore the Windows Control Panel "Region" setting), so currently I am using two statements:

.Range("A1").NumberFormat = "@"
.Range("A1") = Format(myDate, "mm/dd/yyyy")

From the function above, maybe I can combine these two into a single statement, like this:

.Range("A1") = Format(myDate, "mm/dd/yyyy;@")

Thanks, experts!

Genki
  • 378
  • 6
  • 17
  • 1
    I haven't ever used @ in this way. But your question can been answered (by yourself, for example), after a really quick test: set the format of A1 to date and run the code (.Range("A1") = Format(myDate, "mm/dd/yyyy;@")) and see if the format is still date or it became text. If you do that, you would see that no change happens, what is pretty logical as far as @ is inside a string, but not one assigned to the property in charge of changing the format (NumberFormat). On top of that, you have a problem with your code (the two-line one above): (out of space) – varocarbas Aug 19 '13 at 07:32
  • if you want to ignore completely the regional settings of the computer (which might become a real pain for dates some times, I know that), you should bear in mind that Format accounts for these settings as a NumberFormat date would do; that is, you are not stopping considering the regional settings by using this code. – varocarbas Aug 19 '13 at 07:35
  • I am reading in dates which are in many different formats, not all of which are VBA/Excel compatible, so I parse the incoming date into D, M and Y, store it as a real date value, and then want to output the date as either D/M/Y or M/D/Y, regardless of the regional settings. – Genki Aug 19 '13 at 08:04
  • I am not sure what you mean with "VBA/Excel compatible", but what I can confirm you is that regional settings do represent a problem when dealing with dates in Excel. A problem which you should address. For example, the regional settings in my computer are set to Spain (where the dates are dd/mm/yyyy) and your first code (the two-line one) does not deliver what you are after. Try it yourself (change the regional settings to any European country and restart the computer) and do an intense validation of this part to make sure that it delivers exactly what you want. – varocarbas Aug 19 '13 at 08:10
  • 1
    http://stackoverflow.com/questions/894805/excel-number-format-what-is-409/895672#895672 –  Aug 19 '13 at 08:35
  • @mehow good one, but I think that the exact meaning of "@" is the less important problem for the OP. Understanding the differences between NumberFormat and what you write in a string (via Format or directly) and that if you use Format the regional setting are applied anyway; seem like much more important ideas to have clear. – varocarbas Aug 19 '13 at 09:36
  • @varocarbas oh i just read the edited question now –  Aug 19 '13 at 09:49

0 Answers0