Excal VBA - Format("7A", "00") outputs "00" while Format("7B", "00") results to the desired output of "7A". Why are they different?

user_5502774 0
Viewed 178

I am trying to list hexadecimal values from 7F down to 00 per Excel row. I achieved it by doing the following code:

Sub ListDownHex()
Dim StartValue, i As Integer
Dim nRow As Long
Dim sh As Worksheet

Set sh = ActiveSheet
StartValue = 127 'memory location of 8051 RAM
nRow = 4

For i = StartValue To 0 Step -1
    sh.Range("A" & nRow) = Format(Right$("00" & Hex(i), 2), "00")
    nRow = nRow + 1
Next i

End Sub

except hex values that have "A" like "7A", "6A", "5A", etc. result to "00". Further check on Intermediate Window confirms Format() function does it.

? Format("7B","00") 'desired output
7B
? Format("7A","00") 'not expected output
00

Is this a bug or am I missing something? Why are they acting differently? (Note: The focus of my question is on "strange" behavior of Format() not on my entire code.)

excel-formula vba excel
2 Answers

Format() is a complicated function that formats all sorts of things, dates and numbers included.

When you pass a string to it (which "7A" and "7B" are), it tries to see whether they can be converted to a number (IsNumeric() returns True) or a date (IsDate() returns True). If they can, it converts them, and only then applies formatting.

IsDate() returns True for "7A", which is because "7A" is valid textual representation of time value "7 in the morning" (7am).
That, in turn, is because the short version of the AM/PM formatting token, A/P, returns the "am/pm" designator as "a/p":

? Format$(#07:00:00#, "hA/P")  ' => 7A
? Format$(#17:00:00#, "hA/P")  ' => 5P

So the string "7A" is first converted to a Date value #12/30/1899 07:00:00# (zero date, 7 in the morning). The numeric representation of that value is 0.29166667, which, when formatted according to the "00" format, rounds to "00".

"7B", on the other hand, is not an IsDate(), so Format outputs it without changes like it does to strings that have no other meaning.

In reality though, you do not need the Format() function at all, all you need is

= Right$("00" & Hex$(i), 2)
user_116830

Wow! so clear, and so complete. Thank you, @GSerg!

Allow me to refer to an error in exactly this regard made by Microsoft Support. The following code was taken directly from Microsoft Support.

Function StrToHex (S As Variant) As Variant
'
' Converts a string to a series of hexadecimal digits.
' For example, StrToHex(Chr(9) & "A~") returns 09417E.
'
Dim Temp As String, I As Integer
If VarType(S) <> 8 Then
    StrToHex = S
Else
    Temp = ""
For I = 1 To Len(S)
    Temp = Temp & Format(Hex(Asc(Mid(S, I, 1))), "00")
Next I
StrToHex = Temp
End If
End Function

The URL is https://support.microsoft.com/en-us/office/sort-records-in-case-sensitive-order-8fea1de4-6189-40e7-9359-00cd7d7845c0

user_112538830
Related