Refactor Excel Worksheet Formulas containing VBA function calls to depend upon a "formula facade"...

[jim-oflaherty-jr-qalocate-com]

Justification By creating one level of indirection via a "formula facade", the goal is to reduce, inhibit, and eventually eliminate change propagations in both directions between Excel formulas and the VBA code upon which the formula depends.

Directions:

1. Edits of an Excel formula (ex: adding, changing the type of, and/or removing parameters) impacting the underlying VBA code
2. Refactoring of underlying VBA code impacting formulas (like renaming, rearranging of parameter lists, etc.)

Description Offer a mechanism ("Tools" Sub-Menu?) to enable the software engineer to request a facade be generated which automates the process of fetching all VBA function calls from within a Worksheet's formulas, refactors each formula to route the VBA call to a new code-generated facade within the Worksheet's Code item within VBE, of which said code generated facade implements the forwarding call into the VBA code base.

Additional context Editing formulas within Excel can be an excruciatingly painful and slow process, especially when the formula has been copied to a large number of cells. If the VBA entry point the formula is using happens to change for any of a number of valid refactoring reasons, updating all the formulas is not just tedious, but highly error-prone. Missing a cell that should have been updated, but wasn't is AT BEST a (hopefully obvious) erred value. At worst, it is a silent failure.

By using the OOP "Facade Pattern" (a.k.a "Interface" lifted away from "Implementation") and adding the one level of indirection between the Excel formulas and their dependency on the underlying VBA code, a change on one side can be quickly updated within the facade without requiring the change make it through to the other side. This enables smaller iteration stepped refactorings on both sides. It also enables decoupled development between both sides which is very valuable if there happens to be a different team member working on one side from a team member working on the other side.

SIDENOTE: I did Google to try and find this somewhere, anywhere. I couldn't imagine I was the first to think of this. Here's all I could turn up that was even close:

• https://github.com/rubberduck-vba/Rubberduck/issues/3936
• https://stackoverflow.com/a/27899568/501113

[Greedquest]

Could you possibly add a concrete example of what happens to your VBA and worksheet formula before and after? I'm struggling to visualise it sorry. (Ps I'm not an RD official maintainer/🦆, just interested)

[jim-oflaherty-jr-qalocate-com]

Could you possibly add a concrete example of what happens to your VBA and worksheet formula before and after? I'm struggling to visualise it sorry. (Ps I'm not an RD official maintainer/🦆, just interested)

Absolutely.

I have received a workbook containing VBA code (across +30 modules) that has 11 worksheets. 8 of the worksheets contain thousands of formulas. These formulas all call a small number of functions in the VBA code. These 8 worksheets also each have 4 buttons that call into the VBA code as Events.

The goal would be to pop a refactoring within Rubberduck that surfaced a dialog identifying the Worksheets containing formulas and/or objects making calls into the VBA modules. And for each Worksheet, for each VBA function, enable specifying whether an entry to be processed or not. And if processed, whether the facade point is placed in the Workbook "code" or the specific Worksheet "Code". Once the user has specified what they desire to "shim", for each function, the refactoring would grab a copy of the "existing VBA function call" from within the worksheet, replace it with a "TargetCode.facadeFunction" having exactly the same signature, would then create that function signature in the Target code point on the VBE side filling in the implementation with the "existing VBA function call".

Without this process, I must actually go to each function and manually replace the "existing VBA function call" with the "TargetCode.facadeFunction". And then go into the VBE and manually implement the TargetCode.facadeFunction. And I have to repeat this process for every unique VBA function call from an Excel formula.

Here's a vastly over-simplified visualization:

Before:

  Workbook.Worksheet.A2 contains [=getCountByDelimiter(A1, "|")]
  VBAProject.Modules.Module1:
    Public Function getCountByDelimiter(ByVal values As String, ByVal delimiter As String) As String
      ... 'Implementation details not relevant
    End Function

Within the proposed Rubberduck refactoring dialog, the user indicates all Facade Functions are to be placed in the Workbook "Code" which, in a default Excel .xlsm file, is at VBAProject.MicrosoftExcelObjects.ThisWorkbook (under "View Code").

After:

  Workbook.Worksheet.A2 contains [=ThisWorkbook.functionFacadeGetCountByDelimiter(A1, "|")]
  VBA.MicrosoftExcelObjects.ThisWorkbook:
    Public Function functionFacadeGetCountByDelimiter(ByVal values As String, ByVal delimiter As String) As String
      functionFacadeGetCountByDelimiter = Module1.getCountByDelimiter(values, delimiter)
    End Function
  [Unchanged]VBA.Modules.Module1:
    Public Function getCountByDelimiter(ByVal values As String, ByVal delimiter As String) As String
      ... 'Implementation details not relevant
    End Function

Does that help clarify?