Newton Excel Bach, not (just) an Excel Blog

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