VBA - cycle through number formats

user_19376095 0
Viewed 61

I have put together the following code so that when I repeatedly press a shortcut, the number in a cell automatically switches formats between:

2020 2020A 2020E 2020A/E 2,020% 2,020x 2,020bps

Here's the code that isn't currently working:

Sub Number_Format_Cycle()
  Dim x, myFormats

  myFormats = Array("###0;(###0);-", "###0A;(###0)A;-", "###0E;(###0E);-", "###0A/E;(###0A/E);-", "#,##0.0%;(#,##0.0)%;0.0%", "#,##0.0x;(#,##0.0)x;0.0x", "#,##0bps;(#,##0)bps;0bps")
  x = Application.Match(ActiveCell.NumberFormat, myFormats, False)
  If IsError(x) Then x = 0
  Selection.NumberFormat = myFormats((x Mod (UBound(myFormats) + 1)))

End Sub

Any help would be much appreciated

Cheers, Thomas

vba excel
1 Answers

You have to use a backslash (\) in order to add character literals to make them showing up exactly as typed. Changing the myFormats assignment to the following codeline worked for me:

myFormats = Array("###0;(###0);-", "###0\A;(###0)\A;-", "###0\E;(###0\E);-", "###0\A\/\E;(###0\A\/\E);-", "#,##0.0%;(#,##0.0)%;0.0%", "#,##0.0x;(#,##0.0)x;0.0x", "#,##0\bp\s;(#,##0)\bp\s;0\bp\s")

Re-Check each number format you intend to add after a test setting against the array elements.

Caveat: I have to admit that I can't explain the last \bp\s format, nevertheless it works.

user_64602970
Related