Gentlemen, I am new to Excel VBA a

[athomas236]

Gentlemen,
I am new to Excel VBA and have got to the stage when I write my own function procedures and make available to all my spreadsheets as addins. I have about 40 engineering functions/addins and the number is growing.

I am now wandering if there is any advantage in compiling the VBA functions or addins in terms of increasing speed.

If there is, is there a good book that would tell me how to proceed.

Regards,
athomas236

 

[dsi]

I don't really think that speed will be a major factor in either path you choose. It really only affects how you want to distribute the functions. If it's all in-house, you can simply add the module to personal.xls for each system. But, if you go this route, you may want to add a digital signature to avoid the constant macro virus warnings. For in-house projects, you can sign your own programs. See the following MSDN article for more info.

http://msdn.microsoft.com/library/d.../en-us/odeopg/html/deovrsigningvbaproject.asp

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[athomas236]

dsi,
Thanks for advice.
athomas236

 

[morrow]

Try one of John Walkenbach's books

http://www.j-walk.com/ss/.

I use his book Excel 2000 Power Programming With VBA constantly. If you are new to Excel/VBA, you will find his books extremely useful anyway.

My experience is that VBA codes can be sped up a lot. I would also suggest you use the timer function to see where your time consuming subroutines are.

 

[athomas236]

Thanks morrow

athomas236

 

[dongxiao]

I have moved chunk of my VBA code to ActiveX DLL before and did a comparison of the speed. Surprisingly the executing speed of the two are almost the same. To my own understanding, VBA code cannot be compiled. The way to speed it up is code optimization in the Loop, With block etc.

dongxiao PE

http://www.cadtool.net

 

[athomas236]

Thanks dongxiao PE