Converting Decimal To HexaDecimal In VBA

[Challseus]

Hi guys:

I'm aware of the code: ActiveCell.FormulaR1C1 = "=DEC2HEX(10)" which returns "A" that you can use in Excel.

Is there some function like that I can use in VBA?

 

[Juan Pablo González]

MyAns = Evalulate("DEC2HEX(10)")

 

[Andrew Poulsom]

Use the Hex function:

MyHex = Hex(10)

 

[Challseus]

Thanks guys. 1 question though. What data type does Evaluate("DEC2HEX(10)") return? I keep getting the run time error "Type Mismatch" when I use the following code:

    Dim test As Integer
    test = 10
    Dim ans As String
    
    ans = Evaluate("DEC2HEX(test)")
    MsgBox ans

Thanks in advance

 

[Jay Petrulis]

Hi,

In this case, the ans variable is a string as you dimensioned. Your problem is that you are trying to insert a variable into the function, but are, in fact, actually sending the variable name, rather than its value.

In your code, you are trying to convert "test" to hexadecimal, not 10.

Try the following...

Sub tester()
Dim test As Integer

    test = 10
    Dim ans As String
    
    ans = Evaluate("DEC2HEX(" & test & ")")
    MsgBox ans

End Sub

That said, you have been given a much better option here, as VBA has the Hex function already (something I did not know prior to this).

ans = Hex(test)

would be better to use in this case.

 

[Challseus]

Thanks for all the help guys. I got it working now.

 

[wingnut2.0]

Hello,
I was wondering if someone could explain why this is not working. I am attempting to convert a decimal number in column G to a 4 place Hex value and insert it into column M. Any help would be greatly appreciated. Thanks.

Sub Hex2Dec2()
Columns("G:G").Select
Selection.NumberFormat = "@"
With Range("G2", Range("G" & Rows.Count).End(xlUp))
.Offset(, 6).Value = Evaluate("DEC2HEX(.Address, 4)")
End With
End Sub

 

[Orequina]

Hey you...
Work on this code.... I made this specially for you...
email me if you appreciate it... --> brix102778@gmail.com

Private Sub HextoBin_Click()
Dim deca, decb As Variant
Dim binr As String


deca = Val("&H" & Hext.Text)
binr = ""
Do While deca > 0.6
deca = deca / 2
If Left(Right(deca, 2), 1) = "." Then
deca = Left(deca, (Len(deca) - 2))
binr = 1 & binr
Else
binr = 0 & binr
deca = deca
End If
Loop
Bint.Text = binr
End Sub