VBA hex value generation from an array

Question

i have a few checklist box in my excel sheet (say A-F i.e., caption of checkbox),based on the enabling of check list, i have to generate hex value...does the below code will do it.

Private Sub CommandButton1_Click()

Dim SegArray(15) As Integer

 For i = 0 To 13

     If CheckBox(i).Value = True Then
         SegArray(i) = 1
     Else
         SegArray(i) = 0
     End If

 Next i

 hex_value = Hex(SegArray())

 MsgBox "0x" & hex_value

End Sub

i got an error:

compile error: sub or function isnt defined

chris neilsen · Accepted Answer · 2012-07-16 11:37:04Z

1

There are a number of problems with your function

the CheckBox's are not an array. They cannot be referenced as ChecklBox(i)
The first checkbox placed will be named CheckBox1 not CheckBox0
the Hex function converts a decimal number, not an array, or a binary number

Heres your function reworked to address these issues

Private Sub CommandButton1_Click()
    Const num_of_checkboxes = 14

    Dim SegArray() As Long
    Dim i As Long
    Dim dec_value As Long
    Dim hex_value As String

    ReDim SegArray(1 To num_of_checkboxes)
    dec_value = 0

    For i = 1 To num_of_checkboxes
        If Me.OLEObjects("CheckBox" & i).Object.Value Then
            dec_value = dec_value + 2 ^ (i - 1)
        End If
    Next i
    hex_value = Hex(dec_value)
    MsgBox "0x" & hex_value
End Sub

Assumes CheckBox1 is the least significant bit

edited Jul 16, 2012 at 11:37

answered Jul 8, 2012 at 2:16

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

8 Comments

danny Over a year ago

hi chris, the above code didnt work as i expected. If i select i , k, l,n check box it should give the hex value as 0x002D but it is giving 0x2D00 which isnt expected.

chris neilsen Over a year ago

Check the order of your check boxes. As I said in the answer, the code required the checkbox numbers to be in bit order, starting at the least significant bit.

danny Over a year ago

sorry to say, its giving the same result as 0x2D00 & not 0x002D. The 'I' checkbox NAME is CHECKBOX9 ... as 'N'corresponds to CHECKBOX14. Also, if i select A ,B,C ,D,E,F check Boxes then it should give 0x3F00 but it is giving 0x3F only.HOw can i modify the code?

chris neilsen Over a year ago

That not making sense. The check box names should be CheckBox1, CheckBox2, ... , checkBox14. If thats not the case you need to either rename the check boxes or modify the code to use the names you have

danny Over a year ago

it is CheckBox1, CheckBox2, ... , checkBox14 only, in my last comment i wrongly mentioned it as CHECKBOX9. Actually it is like wat u said, CheckBox1, CheckBox2, ... , checkBox14.

|

Collectives™ on Stack Overflow

VBA hex value generation from an array

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related