VBA to set a custom date (time zone) format

[well2ant]

Hello all,

I'm writing a module to convert date & time provided in UTC format, into a local time zone of my choice.
The code works fine in converting to the required time zone, but I want to them place a formatting over the top to indicate which time zone it relates to.

I do not want to add text to the field, hence why I'm using a format.

If I select the cell(s) I can manually apply a custom number format of
"dd/mm/yyyy hh:mm:ss \(\A\E\D\T \U\T\C\+\1\1\)"
which makes the date/time appear as
21/06/2016 21:16:03 (AEDT UTC+11)

However, I'm trying to get the macro to apply the appropriate format of the time zone that has been calculated. I am using the code below, but it does not change the value in MyRange.NumberFormat from what it was before the macro is run.

        Select Case OutPut
          Case AEDT_Format
            MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\E\D\T \U\T\C\+\1\1\)"
          Case AEST_Format
            MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\E\S\T \U\T\C\+\1\0\)"
          Case ACDT_Format
            MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\C\D\T \U\T\C\+\1\0\.\5\)"
          Case ACST_Format
            MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\C\S\T \U\T\C\+\9\.\5\)"
          Case AWST_Format
            MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\W\S\T \U\T\C\+\8\)"
          Case Else
            MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss"
        End Select
      End With

OutPut: is an integer 0 to 4.

I realise the I do not need all the \ characters, but it ensures that each character is literal.

Does anyone have any suggestions?

Thank you in advance.

 

[Warship]

If this works I would look at the variables like AEDT_Format to be sure they are set proper

Select Case OutPut
  Case 0
    MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\E\D\T \U\T\C\+\1\1\)"
  Case 1
    MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\E\S\T \U\T\C\+\1\0\)"
  Case 2
    MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\C\D\T \U\T\C\+\1\0\.\5\)"
  Case 3
    MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\C\S\T \U\T\C\+\9\.\5\)"
  Case 4
    MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\W\S\T \U\T\C\+\8\)"
  Case Else
    MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss"
End Select

 

[well2ant]

Hi Warship,

The values are set correctly. I did have another thread out there - someone told me that you can't assigned the NumberFormat within a UDF, which is what I was trying to do.
I tried moving the code to a procedure and have the UDF call the procedure, but that didn't work either.

I'll have to work out another way of doing this.

Thanks