DEC2HEX with VBA in Excel 2007

[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

 

[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.

 

[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

 

[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

 

[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 !

 

[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 !