DEC2HEX with VBA in Excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rishi - 07 May 2008 23:38 GMT

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
--------------------------
Thanks in advance

Reply to this Message

Dave Peterson - 08 May 2008 00:28 GMT

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.

> I have a program working with Excel 2003 with VBA that uses the DEC2HEX
> function within the VB routine (not in the Excel sheet)
[quoted text clipped - 22 lines]
> --------------------------
> Thanks in advance

Signature

Dave Peterson

Reply to this Message

Rishi - 08 May 2008 17:27 GMT

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

> Your subject says xl2007, but your message says xl2003?
>
[quoted text clipped - 37 lines]
> > --------------------------
> > Thanks in advance

Reply to this Message

Dave Peterson - 08 May 2008 19:09 GMT

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
>
[quoted text clipped - 54 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rishi - 08 May 2008 22:46 GMT

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 !

> xl2007 removed all those worksheet functions from the addin and moved them into
> excel itself.
[quoted text clipped - 67 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 08 May 2008 23:59 GMT

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
[quoted text clipped - 77 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rate this thread: