Compiling Excel VBA
You can’t do it, plain and simple. VBA code lives in an Excel workbook and there is no way, that I know, to compile that code outside of a workbook. The long and the short of it is: The users of your Excel programs must have Excel installed.
(I love making unequivocal statements like this. If you want to know how to do something, just say that it can’t be done and someone will be there to show you the error of your ways.)
Colo:
Just thought…
I was asked exactly same questions for many time.
Most of the people who asked this wants to make an application with using userforms only.
My answer was..why don’t you use VB, VC or something like that?
But in many case, they knows Excel VBA only. An easy way to make Excel like an original application is call the Excel file from VBS file. I like this way!
‘VBS CODE
15 April 2004, 12:36 amDim appXL
Dim objWb
Dim path
path = CreateObject(”WScript.Shell”).CurrentDirectory
Set appXL = CreateObject(”Excel.Application”)
Set objWb = appXL.Workbooks.Open(path & “\db\excel.xls”)
appXL.Application.Run objWb.Name & “!” & “showform”
Mike Woodhouse:
This is rather more one of those “sorta mostly true” kinds of things and it’s probably completely true for the majority of user/developers. Actually, if you stipulate that *all* the VBA has to be extracted and compiled then I’d also be prepared to agree for (probably) all onn-trivial cases.
Ignoring the cases above, it _is_ possible to extract (most easily) VBA classes, compile them to COM DLLs in VB and use them by simply adding a reference to the DLL so created in your VBA project.
I’ve done this in the past to speed up financial calculations that were extremely complex and long-running. While I can’t say that it would be worth it now (the last time I used the technique was in XL97), the speed increase achieved (about 7-fold) was more than satisfactory.
The benefit of sharing functionality by commonly accessible compiled libraries trather than copied VBA modules is also nice to have.
Of course, I then went on and rewrote the DLL in Delphi for another massive performance increase but that’s another story…
15 April 2004, 4:10 amColo:
Thanks for interesting story Mike,
15 April 2004, 5:20 amAs for DLL, XLL type is also good way for speed up.
A XLL type dill can be called from Excel directry so it seems very handy way, but it needs to be compiled and must be witten in C language. and it can be used in Excel only.
Deb S:
Compiling VBA? Yes you’re correct in saying it’s not compiled like C but then again why, the novice would say, is there a ‘compile’ option in the Debug menu? You should just say that it’s used to sort of ‘pre-test’ your code for bad usage of variables (if ‘Option Explicit’ is used) and other things. It does have its uses and should be pointed out.
13 June 2004, 11:39 pmJonathan Rynd:
I just wanted to point out that my company, Savvysoft, has released a product called TurboExcel that will take your Excel VBA macros, convert them to C++, and compile them into a DLL (so you can use them outside Excel) or an XLL (so you can get the vast speedup of compiled code inside Excel). It has some limitations, but should work in very many cases. It will save you from having to rewrite (and redebug) the application in another language.
The website is http://www.turboexcel.com/
30 September 2004, 11:56 amchand:
i got a strange problem. Iam using Excel 2003-Professional Edition with sp1.and Microsoft Visual Basic 6.3.
I declared couple of variables(as integers), when ever i modify the values and save the vba & Excel applications and try to run, some of the changes are not taking into effect. its taking the old values for the variables. How to deal with this.
Is there any means to recompile the application so that the changed values can get effected.
12 October 2005, 5:09 pmWilliam Sears:
I want to thank everyone involved with placing (and leaving) these comments on this website until now (November, 2007). I am relatively new to VBA for Excel and have never programmed in anything else except FORTRAN. I have asked this “Can one compile VBA for Excel” question to many people and find that no one, until this site could even give an opinion. I am currently working in Office 2007. Do the comments above remain the same? Thank you, Bud Sears
27 November 2007, 7:14 amBrad Yundt:
AFAIK, the answer is the same. You can compile into a .COM add-in (using Visual Basic). Or you can use a third-party product like Calc4Web (the new name for TurboExcel) http://www.calc4web.com/index.html
I will add that my experience with scientific/engineering programs suggests that you can gain more from optimizing your algorithm and taking advantage of the objects and methods of Excel than the ten-fold improvment in speed promised by compilation. If you have a lot of data to move around, keep the interaction between worksheet and VBA to a minimum by using array transfer. Learn to user Solver. Declare all your variables, preferably as something other than a Variant. Never Select or Activate. Time your code’s execution when evaluating different alternatives (the Timer function gives you milliseconds).
My first VBA program in 1995 involved 50 pages of code with several nested layers of iterative calculations. The first version took 5 minutes to converge. After optimizing the algorithm and using array transfer, I got it down under 10 seconds. With proper variable declaration and faster computers today, the solution now converges almost as fast as you can type.
27 November 2007, 10:54 pmZach:
Brad,
Do you have any experience with this Calc4Web? It sounds interesting. I tried to check out the demo, but the file they have for download doesn’t do anything.
28 November 2007, 3:23 pmBrad Yundt:
Zach,
29 November 2007, 7:26 amCalc4Web is one of those products I am aware of, but have never actually used. To get full VBA functionality is fairly expensive.
Brett:
Brad,
I see your comment about not selecting anything. What's up with the following?
ActiveSheet.Shapes("Check Box 1").Select
With Selection
.Value = xlOn
.LinkedCell = "$F$11"
.Display3DShading = False
End With
End Sub
Sub WillNotRun()
With ActiveSheet.Shapes("Check Box 1")
.Value = xlOn
.LinkedCell = "$F$11"
.Display3DShading = False
End With
End Sub
Jon Peltier:
Brett -
Use the "deprecated" checkbox object:
With ActiveSheet.CheckBoxes("Check Box 1")
.Value = xlOn
.LinkedCell = "$F$11"
.Display3DShading = False
End With
End Sub
This worked for the first two properties:
With ActiveSheet.Shapes("Check Box 1").ControlFormat
.Value = xlOn
.LinkedCell = "$g$11"
' error next line
.Display3DShading = False
End With
End Sub
Lee:
Hi,
I love the idea of compiling VB! Question, can I use Web4calc and put the complied code back into the spreadsheet? I only want to protect and speed up the code, not require a link to a DLL. I.e. I want to be able to email the spread sheet across the world and not figure out how to get it to reference back to the DLL on a different server. Is this possible?
Thanks!
Lee
26 March 2008, 10:07 pm