Converting Hex to Dec in VBA and making code efficent

Hi,

Wondering where this code can be adjusted to be much more efficient. Right now it is going through 1000 rows and 14 columns and it is taking about 10 minutes. I eventually need it to go through 6000 rows and 32 columns..

I already stored the HexToDec(Cells(x, 1)) result to variable H2D and it didn't really make too much of a noticeable difference.

Please Login or Register  to view this content.

I am using this Function to convert Hex to Dec since there is no VBA built in function, which I got from VBAExpress.com: http://www.vbaexpress.com/kb/getarticle.php?kb_id=307

Please Login or Register  to view this content.

Thanks

I tried further reducing calculations required by doing this... but to no further noticeable avail.

Please Login or Register  to view this content.

What does the code do?

Hi Shg,

Kind of difficult to explain...

This is something given to me by our Electrical Engineering Dept. They are trying to reduce a large workbook to a smaller one. Currently the workbook is over 43 MB large and they want it to be as small as possible in order for them to save and give to customers, etc...

The code here is meant to replace formulas already in columns K to AP with values only.. this is just a first step.. I will need to replace formulas in column G, AR and AS too and other stuff in other sheets in the original workbook.

So if you look in those columns K:AP and run the code you will see they get replaced with values.

this might help a little

its 33% faster than your routine

timing them as a worksheet function

Cell Time % total Time Function length Function Comment
$F$4 0.0002732 54.6589% 13 HexToDec1(D4) original vba
$G$4 0.0001627 32.5402% 13 HexToDec2(D4) My VBA
$E$4 0.0000640 12.8008% 11 HEX2DEC(D4) Analasys pack

Please Login or Register  to view this content.

you can also :

Val("&h" + Hex)

but it has 2 problems

7fff - ffff are negative

7fffffff - ffffffff are negative

ffffffff is the maximum!

Please Login or Register  to view this content.

Hi squiggler47,

I tried both those function alternatives.

They both still did not seem to speed up the process much, though.

Hmm. Try this.

Please Login or Register  to view this content.

I also realized my original code was giving incorrect results ... this is the revised code:

Please Login or Register  to view this content.

After seeing spreadsheet it seems that you are trying to convert the hex values to binary

Please Login or Register  to view this content.

will create a 16 bit binary string, you can then use val(mid(Binstring,j,1)) to read the values and put in the apropriate cells!

That should work a lot faster!

hi all,

Would/Does the use of variant arrays to perform the calculations "in memory" before writing back to the spreadsheet speed the macro up much (ignoring the content of the question)?
(I'm currently working on a reasonably old laptop with excel 2000 so I haven't tried testing the concept myself)

However, I've just read about a size or transfer limitation of variant arrays to ranges & have created a thread (& here's my attempt to increase thread exposure & get some knowledgeable feedback while linking it to your question )
http://www.excelforum.com/excel-prog...ml#post2091436

Rob

shg,

Your code worked nicely.. thank you very much.

It took about 2 minutes to run 6000 rows (columns K:Z).. and I guess it would take about 2 more to run for columns AA:AP. I only hope all the other alterations I need to do don't add much more time.

As you can tell, I am sure, I am not a programmer, so wasn't sure of how to improve on the Hex2Dec conversions... but you did it nicely.

Now, how would I convert the results to number, currently they are being relayed back to the sheet as text. I tried adding +0 to this line:

Please Login or Register  to view this content.

but gave error.

Squiggler47, I tried that function and I got an overflow error...

What do I need to change in my Sub HexConversion() code to make it work with your function?

I just changed this:

H2D = HexToDec(Cells(x, 1))

to

H2D = HexToBIN(Cells(x, 1))

... how would I convert the results to number ...

Please Login or Register  to view this content.

Thank you, shg.

That did the trick... and it seems to be alot faster now

probably a little late but i ran out of time last night before i could post it :--

Please Login or Register  to view this content.

Running timings on it it takes about 2/3 of the time of the other routine

My Routine 0.29296875
My Routine 0.28515625
My Routine 0.27734375

SHG 0.48828125
SHG 0.47265625
SHG 0.44921875

3 timings of each

squiggler47,

Thanks very much..

Your routine is indeed faster.

I increased the loop to 6000 and added the other 16 columns to update and it still ran quite fast:

My Routine 1.328125

Thanks again. I appreciate the help of both yourself and shg.