Excel Macros

[cathym]

How do I take a complicated excel macro, compile it with a visual basic 6.0 compiler to an .exe file, and then get the spreadsheet to execute it?

 

[cthaxter]

I would copy all the modules and forms into VB and change the code so that you create an Excel.Application object and call it with With clauses. Then, just run the exe. You may need to alter the code slightly in order to create a startup form and include a button or something to run the excel macro.

Another possibility may be to run the excel macro from an exe by simply clicking on a button from a form in the exe, which will open the Excel workbook, which contains a macro that executes at startup. I haven't tested this, though, so I'm not sure how well it will work.

The only problem you may come across is that you can't use Excel add-ins like the Solver from any other application, including VB. I haven't found a way to get around this.

What I mean in my first paragraph by converting the Excel macro's code to be surrounded by With clauses that contain the Excel.Application is as follows. In Excel, your code might be something like:

Sub myExcelMacro()
Worksheets(1).Select
Range("A1&quot.Select
ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Value = 3
ActiveCell.Offset(2, 0).Value = 2
ActiveCell.Offset(3, 0).FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Worksheets(2).Select
End Sub

Which would need to be converted in VB as follows (being sure to include Excel as a reference):

Sub myVBModule()
Dim objXL As Excel.Application
Set objXL = CreateObject("Excel.Application&quot

With objXL
.Visible = True
.Workbooks.Open ("C:\My Workbook.xls&quot
.Worksheets(1).Select
.Range("A1&quot.Select
.ActiveCell.Value = 1
.ActiveCell.Offset(1, 0).Value = 3
.ActiveCell.Offset(2, 0).Value = 2
.ActiveCell.Offset(3, 0).FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
.Worksheets(2).Select
End With
End Sub

 

[gswetzel]

I am curious about why you would want to compile an Excel VBA program? Added security?

For either suggested approach in the reply, you would need the Excel object library. I have used the 'alternative' suggested with success, and secured the Excel VBA with passwords.