DEC2HEX with VBA in Excel 2007


R

Rishi

I have a program working with Excel 2003 with VBA that uses the DEC2HEX
function within the VB routine (not in the Excel sheet)

I have turned the Analysis and the Analysis VBA add-in on.

I have also checked the atpvbaen.xls reference on in the VBA tools section.

The Dec2Hex (and Hex2Dec) routines return sub or function not found error.

The help section says it does exist.. However I am not able to use it. Any
ideas on what can be done ?

----------- routine enclosed -------------
Private Sub CommandButton_ChangeSlaveAddress_Click()
Dim SlaveAddress As Integer
SlaveAddress = Hex2Dec(Range("SlaveAddressInput").value)

If SlaveAddress < 0 Or SlaveAddress > 255 Then
MsgBox ("Invalid slave address entered!")
Else
Range("SlaveAddressInput").value = Dec2Hex(SlaveAddress, 2)
End If

End Sub
 
Ad

Advertisements

D

Dave Peterson

Your subject says xl2007, but your message says xl2003?

If you're using xl2003, then make sure you add a reference to the analysis
toolpak VBA in the VBE.

Open your workbook
Open the VBE
select your project
Tools|References|Check atpvbaen.xls

You need the addin (excel's Tools|addins) and the reference for this to work.

The addin for the "analysis toolpak" isn't required for this. You may need it
for other stuff in the worsheets, though.
 
R

Rishi

Dave

Thanks. Part of the post got deleted. As I mentioned in my post atpvbaen.xls
is on in VBA.

The part that got deleted it "The program works in 2003. But I dont get it
to work in 2007 ".. I took it off and forgot to reinsert it. My bad.

The Dec2Hex (and Hex2Dec) routines return sub or function not found error.

Any ideas ?
Thanks
 
D

Dave Peterson

xl2007 removed all those worksheet functions from the addin and moved them into
excel itself.

That means you don't need the reference within the VBE.

This worked ok for me in xl2007:

Option Explicit
Sub testme()
MsgBox Application.Dec2Hex(100)
MsgBox Application.Hex2Dec(64)
End Sub


Dave

Thanks. Part of the post got deleted. As I mentioned in my post atpvbaen.xls
is on in VBA.

The part that got deleted it "The program works in 2003. But I dont get it
to work in 2007 ".. I took it off and forgot to reinsert it. My bad.

The Dec2Hex (and Hex2Dec) routines return sub or function not found error.

Any ideas ?
Thanks
 
R

Rishi

Great. Adding the Application. to the Dec2Hex worked. I tried using the
Worksheetfunction.Dec2Hex earlier but that had not worked. I was thinking
they are the same thing..

Thanks much !
 
Ad

Advertisements

D

Dave Peterson

They're not quite the same thing, but this worked for me in xl2007:

Option Explicit
Sub testme()
MsgBox WorksheetFunction.Dec2Hex(100)
MsgBox WorksheetFunction.Hex2Dec(64)
End Sub

Maybe you did something else when you were testing????
Great. Adding the Application. to the Dec2Hex worked. I tried using the
Worksheetfunction.Dec2Hex earlier but that had not worked. I was thinking
they are the same thing..

Thanks much !
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top