20 thoughts on “Compiling Excel VBA”

1. 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
   Dim appXL
   Dim objWb
   Dim path
   path = CreateObject(“WScript.Shell”).CurrentDirectory
   Set appXL = CreateObject(“Excel.Application”)
   Set objWb = appXL.Workbooks.Open(path & “dbexcel.xls”)
   appXL.Application.Run objWb.Name & “!” & “showform”

2. 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…

3. Thanks for interesting story Mike,
   As 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.

4. 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.

5. 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/

6. 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.

7. 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

8. 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.

9. 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.

10. Zach,
    Calc4Web is one of those products I am aware of, but have never actually used. To get full VBA functionality is fairly expensive.

11. Brad,

    I see your comment about not selecting anything. What’s up with the following?

    Sub WillRun()

        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

12. Brett –

    Use the “deprecated” checkbox object:

    Sub WillSoRun()
        With ActiveSheet.CheckBoxes(“Check Box 1”)
            .Value = xlOn
            .LinkedCell = “$F$11”
            .Display3DShading = False
        End With
    End Sub

    This worked for the first two properties:

    Sub WillPartlyRun()
        With ActiveSheet.Shapes(“Check Box 1”).ControlFormat
            .Value = xlOn
            .LinkedCell = “$g$11”
            ‘ error next line
           .Display3DShading = False
        End With
    End Sub

13. 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

14. please tell me how to compile my excel-vba project. Thanks

15. What can and cannot be converted in Calc4Web

    The types of VBA code that can be converted are limited. Please see the limitations
    chapter.
    What can be converted are:
    • Mathematical operations
    • Calls into built-in VBA functions (as opposed to methods of built-in objects)
    • Calls into user-defined VBA subs and functions (provided the caller and callee are
    converted together)
    • Calls into user-defined methods of class modules
    • Declare statements to allow calling into functions inside DLLs
    • If/Then/Else
    • Goto
    • Iterative constructs such as for-next and while-wend, but not Do While-Loop
    • With blocks
    • Text file I/O
    • Collections
    • Select/Case (not for user-defined data types)
    • Comments
    • Calling into functions via Tools References
    • Data types: integer, long, double, string, array and variant, but not Date.
    Some but not all built-in VBA objects and methods are supported. These include Range
    object methods:
    • Value
    • Formula
    • Select
    • Calculate
    • Cells
    • Offset
    • End
    • Address
    • EntireColumn
    • EntireRow
    • FormulaArray
    • Hidden
    This allows you to get values out of the spreadsheet, and, in subs, put values into the
    spreadsheet.
    Also, the following are defined for the Application class:
    • Excel worksheet functions
    • Calculate
    • Goto
    • ActiveCell
    • ActiveSheet
    • Cells
    • Selection
    • Wait
    • StatusBar
    • ScreenUpdating
    • Worksheets
    One great benefit of VBA conversion is to complement Calc4Web’s spreadsheet
    programming capabilities. The product allows you to use Excel as a programming
    environment, and the spreadsheet is a very simple paradigm for entering complex
    algorithms. It’s very much of a visual programming environment, where the user doesn’t
    need to be concerned about what order to write the code in, and formulas can be created
    by pointing and clicking.
    But as nice as spreadsheets are for programming, they lack two basic features found in
    “real” programming languages: looping, and conditional branching (if then else). But
    these vital programming tools are available in Visual Basic. And, since Visual Basic
    allows users to call into DLL functions via the Declare statement, it’s possible to use a
    spreadsheet, and Calc4Web, to write a complex function that does not require looping or
    if then else, and then call into that function in a loop in VBA. And then, use Calc4Web to
    convert the VBA function to C++.

16. I have recently tried out the trial version of Calc4Web from SavvySoft.
    The VBA compilation would not work for anything other than simple arithmetic.
    So despite what the manual and the above list from sZx on 31 August claim my experience with the trial version is that most useful features do not compile.
    Perhaps this is just a limitation of the trial version but as SavvySoft will not answer my questions about this I do not know.

17. Hi,

    I am importing a new encrypted module from a file as a new component and deleting the old one to update my excel to a new version.

18. Thanks, Colo. I think VBS will be a best i this case.
    To make a VBS file: create blank txt file – put your code in it – save it as a .vbs file – run (or execute using cscript.exe or wscript.exe).

19. Ok. Small example:
    – in thw folder C:Test you have Excel file with the macros, named macros “UF” and let he call userform.
    – now, create text file on the desktop, name it “run.vbs”. Click right mouse button and select “Edit”, you will see notepad. Copy and Paste that code:

    Dim objExcel
    Dim objWorkbook
    Set objExcel = CreateObject(“Excel.Application”)
    objExcel.Visible = False
    set objWorkbook = objExcel.Workbooks.Open(“C:Test1.xlsm”)
    objExcel.Run “‘1.xlsm’!UF”
    objWorkbook.Close True
    objExcel.Quit
    Set objWorkbook = Nothing
    Set objExcel = Nothing

    – Save and Run

    objExcel.Visible = False ‘Hide Excel, change it to see Excel.

    That was a simple exsample how to use VBS. I show you how to combine VBS and VBA.
    Sorry for my english, i’m russian.
    Have a good days, guys.

20. I think maybe you can compile, as an Office Add-In. It may make a .xla file that is compiled. Also, I think you can password-protect the code (in the Add-In) so without the password maybe other people can’t view it.

Posting code? Use <pre> tags for VBA and <code> tags for inline.