Converting String to Hex in Excel VBA

Question

I need to convert strings to hexadecimals in my Excel VBA macro.

I tried:

Dim val As String
val = "10"
Dim hexVal As Integer
hexVal = Convert.ToInt32(val, 16)

but Convert.ToInt32(val, 16) does not work, I'm guessing because it's not VB.NET?

Anyway, I can use CInt(val) to convert a string to an integer but if I have the string representation of a hex value, e.g. "3366CC", how do I convert it to hex so that I can perform hex calculations on it?

Answer 1

Consider:

Sub dural()
    Dim val As String, hval As String
    val = "10"
    hval = Application.WorksheetFunction.Dec2Hex(val)
    MsgBox hval
End Sub

VBA code needs to know or make an assumption about the input string. The code above assumes that the string represents a decimal value.

If the input string is Hex, then:

Sub dural2()
    Dim val As String, hval As Long
    val = "10"
    hval = Application.WorksheetFunction.Hex2Dec(val)
    MsgBox hval
End Sub

Here hval can be used arithmetically.

Answer 2

In VBA you need to mess about with the &H literal:

value = val("&H" & "10") '// 16
value = val("&H3366CC")  '// 3368652

Edit

Function FromHex(hexString As String) As Long
    FromHex = Val("&H" & hexString)
End Function

Then

resultString = hex$(FromHex("3366CC") / FromHex("A"))

Or for constants obviously;

resultString = hex$(FromHex("3366CC") / &HA)