One of the difficulties with Excel routines to solve differential equations, or do numerical integration, is that the equations to be solved must either be coded as VBA functions (which is time consuming), or solved using the evaluate function (which is slow and limited). An alternative is to automatically generate the VBA function, using data on the spreadsheet; a process which can easily be fully automated so that it only requires the entry of the function to be solved in a suitable text format. The code generation process is not difficult, but strangely there is little written about it. The best source of information I have found is Chip Pearson’s site, where he provides detailed instructions, and many detailed examples. In this post I will describe the essentials to get the process working, and provide a working example (with open source code). Refer to Chip’s site for more details, and lots more open source code.
A setting in the Visual Basic Editor (VBE), and an Excel option need to be changed to allow the code generation routines to work:
- In the VBE choose Tools-References and select the Microsoft Visual Basic For Applications Extensibility 5.3 check box.
- From the spreadsheet, select the “Trust access to the VBA project object model” check box. This is under the Developer Tab (macro security) in Excel 2007 and 2010, and under Tools-Macros-Security in 2003 and earlier versions.
Having done that, the code shown below should work. I have adapted four procedures from Chip’s site:
- Sub AddProcedureToModule()
- Sub DeleteProcedure(ModName As String, ProcName As String)
- Sub AddModuleToProject(ModName As String)
- Function VBComponentExists(VBCompName As String, Optional VBProj As VBIDE.VBProject = Nothing) As Boolean
The basic is procedure is:
- Prompt for a selected spreadsheet range containing the code module name, procedure name, and procedure code.
- If the code module does not exist, create it.
- If the procedure already exists, delete it
- (Re)create the code
Sub AddProcedureToModule()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long, CLine As Long
Dim CodeRange As Variant, i As Long, ModName As String, ProcName As String
Dim DefaultRange As String
If Selection.Rows.Count > 1 Then
DefaultRange = Selection.Address
End If
On Error GoTo Cancelled
Set CodeRange = Application.InputBox _
(Prompt:="Code range:", Title:="Select code range", Default:=DefaultRange, Type:=8)
CodeRange = CodeRange.Value
ModName = CodeRange(1, 1)
ProcName = CodeRange(2, 1)
If VBComponentExists(ModName) = False Then
AddModuleToProject ModName
End If
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(ModName)
Set CodeMod = VBComp.CodeModule
With CodeMod
On Error Resume Next
If .ProcCountLines(ProcName, vbext_pk_Proc) > 0 Then
DeleteProcedure ModName, ProcName
End If
LineNum = .CountOfLines + 1
For i = 1 To UBound(CodeRange) - 2
CLine = LineNum + i
.InsertLines CLine, CodeRange(i + 2, 1)
Next i
End With
Cancelled:
End Sub
Sub AddModuleToProject(ModName As String)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = ModName
End Sub
Sub DeleteProcedure(ModName As String, ProcName As String)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim StartLine As Long, NumLines As Long
Dim CodeRange As Variant, i As Long
CodeRange = Range("coderange").Value
ModName = CodeRange(1, 1)
If VBComponentExists(ModName) = False Then
Exit Sub
End If
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(ModName)
Set CodeMod = VBComp.CodeModule
With CodeMod
StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
.DeleteLines StartLine:=StartLine, Count:=NumLines
End With
End Sub
Public Function VBComponentExists(VBCompName As String, Optional VBProj As VBIDE.VBProject = Nothing) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This returns True or False indicating whether a VBComponent named
' VBCompName exists in the VBProject referenced by VBProj. If VBProj
' is omitted, the VBProject of the ActiveWorkbook is used.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim VBP As VBIDE.VBProject
If VBProj Is Nothing Then
Set VBP = ActiveWorkbook.VBProject
Else
Set VBP = VBProj
End If
On Error Resume Next
VBComponentExists = CBool(Len(VBP.VBComponents(VBCompName).Name))
End Function
The screenshots below show this code used to create a user defined function (UDF) to solve quadratic equations (included in the download file). Click on any image for full size view.
Code Generator
Function code
Code range selection
Newton Excel Bach, not (just) an Excel Blog 