How can I use DEC2HEX in Excel vb macros

[Evan145]

Hi,
Can anyone help please.
Im trying to write a vb macro that converts a number to a hex value.

In an Excel soreadsheet the DEC2HEX() func works fine...as follows

A B C D
------------------------------------------------
No. Decimal DEC2HEX(B2) concate("output_",C2)
------------------------------------------------
2 347682 54E22 output_0x00054e22.html
3 347683 54E23 output_0x00054e23.html


but when I try .Cells(i, 3) = Hex(.Cells(i, 2)) in Vb
my output is this


A B C D
--------------------------------------------
No. Decimal HEX Filename
--------------------------------------------
2 347682 5.40E+23 output_0x005.4E+23.html
3 347683 5.40E+24 output_0x005.4E+24.html


vb doesnt recognise the Excel DEC2HEX function, so is there a way of produce the desired output of 54E22 rather than 5.40E+23?

Thanks,
Evan

 

[mscallisto]

Can you add the length option like shown, to display a limited number of digits?

BIN2HEX(11111011, 4) equals 00FB

I've used bin2hex but don't have dec2hex (is that an excel add-on)?

 

[Evan145]

Yes, mscallisto
DEC2HEX()is an Excel2000 Add-In called Analysis ToolPak.

but VB macro is giving me 5.40E+23 from HEX(347682)....because it doesnt recognise DEC2HEX.

I may have to write a further function to return 54E22 as a string. It has to be concatenated to the

 MySht.Cells(i,3) = "output_0x000" & CStr(HEX(347682) & ".html"

string anyway, to give me output_0x00054e22.html

Any experts up to the challenge? ;-)

Evan

 

[GlennUK]

Excuse me, but this has nothing to do with functions not being recognised, it has to do with you putting a hex value in a cell that can be interpretted as a scientific number entry. Format the cell as text first, and the hex entry will work.

Cheers, Glenn.

 

[Evan145]

Thanks Glenn,

Seems thats answer. Formated the ouput cell as 'text' and violá.

Seems I couldnt see the woods from the trees.

Evan

 

[Evan145]

Heres what I did

 MySht.Cells.NumberFormat = "@"  
 MySht.Cells(i,3) = "output_0x000" & HEX(347682) & ".html"

Works...for me.

Evan

 

[KenWright]

Picky only, but as a habit you should take a look at using With and End With constructs any time you start repeating data in that way. Generally more efficient and makes the code much easier to read:-

With MySht
   .Cells.NumberFormat = "@"  
   .Cells(i,3) = "output_0x000" & HEX(347682) & ".html"
End With

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------