0

My language settings are German. In a macro, I assign a 1450x20 Range to an array to speed up computation. One column of the array contains dates, formatted as "MMM JJ", as well as strings. I want to change the dates to strings that use the "MMM JJ" format. However, when I tried to use Application.WorksheetFunction.Text(element_of_array, "MMM JJ"), VBA automatically uses American, so for example, instead of "Dez 14", I get "Dec 14".

I haven't found a way to change this and would appreciate help. Thanks in advance :-)

Aharon
  • 1
  • Please start [here](https://stackoverflow.com/a/16935265/14094617) – JohnSUN Sep 22 '20 at 14:02
  • Thank you! I forgot to mention that - I did try doing it that way with the german code [$402], but this resulted in "402" being added to the string - I assume it doesn't work if it isn't done within a cell, but to the element of an array? – Aharon Sep 22 '20 at 14:33
  • 1
    No, no! Do all the operations on the array as before. Just after inserting it into the sheet, apply .NumberFormat to the entire date column - don't convert dates, just format the result. – JohnSUN Sep 22 '20 at 14:38
  • After that, go through the column `For each oCell in` and replace `oCell.Value = "'" & oCell.Text` (for 1450 cells it will be quickly) – JohnSUN Sep 22 '20 at 14:47
  • Make your array store actual `Date` values (not strings!), and then use a `Range.NumberFormat` format string to format the dates as needed in their destination cells. – Mathieu Guindon Sep 22 '20 at 14:50
  • @JohnSUN I think `allTheCells.Value = allTheCells.Text` should work too, without a need for a loop. – Mathieu Guindon Sep 22 '20 at 14:51
  • @MathieuGuindon I have encountered a situation several times when `allTheCells.Text` gave an error. It always works for one cell, but not for a range. – JohnSUN Sep 22 '20 at 14:56
  • @MathieuGuindon In addition, we need to add an apostrophe so that Excel does not helpfully convert the text to a date again. – JohnSUN Sep 22 '20 at 14:59
  • @JohnSUN tbh I just stick with `Range.Value`, the `.Text` being largely irrelevant for programmatic purposes. And you are correct, the `.Value = .Value` thing was about replacing a formula with its value, and I somehow got confused here :) – Mathieu Guindon Sep 22 '20 at 15:41

0 Answers0