Converting Hexadecimal cell value to binary in VBA [duplicate]

Question

I am trying to convert a column of hexadecimal values to binary values and then write the binary values to a binary file. However, I am having trouble with the conversion of the hex values to binary...

I found this thread: Convert hex value string to Binary string

I have been trying to do as the answers suggest and convert each hex character to binary and then append the results to a string representing the full binary number. However, I am getting errors when trying to convert my hexadecimal string characters... Here is what I am currently trying to do:

Dim hexString As String
Dim binaryString As String
hexString = ""
binaryString = ""
Dim rangeOfCells As Range
Set rangeOfCells = Range ("C5: C100")
Dim hexCell As Range

For Each hexCell In rangeOfCells.Cells
    If Not isEmpty(hexCell.value) Then
        hexString = hexCell.Value
        Dim counter As Integer
        For counter = 1 to Len(hexString)
            binaryString = binaryString & Convert.ToString(Convert.ToInt32(Mid(hexString, counter, 1), 16), 2)
        Next
    End If
Next

I keep getting this error on the line converting the hexString character to binary and appending the value to the binaryString:

Run-time error '424': Object required

I have tried simplifying the line of code and continue to get the error event when just trying to convert my hexString to an Int (ie: Convert.ToInt32(hexString)).

Any help on this issue is greatly appreciated. Thank you!

Convert.ToString is VB.NET, not VBA

Tim Williams
– Tim Williams

2017-10-23 17:27:50 +00:00
Commented Oct 23, 2017 at 17:27 — Tim Williams
– Tim Williams, Commented Oct 23, 2017 at 17:27

Mathieu Guindon · Accepted Answer · 2017-10-23 18:35:59Z

2

You're getting an 'Object Required' run-time error because Option Explicit isn't specified.

With Option Explicit you would've had a compile-time error telling you that Convert isn't declared anywhere.

Reason being, it's a static class in the .NET framework, in the System namespace: it has nothing whatsoever to do with VBA.

If the value to convert is relatively small, then you can use the HEX2BIN worksheet function:

Debug.Print Application.WorksheetFunction.Hex2Bin(myHexValue)

If the function throws an error, then the value is too large and you need to implement your own. This site has plenty of readily-usable implementations.

answered Oct 23, 2017 at 18:35

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raunak Thomas · Accepted Answer · 2017-10-23 18:08:51Z

0

Either use hex2bin or some of the other functions mentioned in this post

answered Oct 23, 2017 at 18:08

Raunak Thomas

1,4251 gold badge14 silver badges29 bronze badges

Collectives™ on Stack Overflow

Converting Hexadecimal cell value to binary in VBA [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related