How to break up a long formula in VBA editor?

Question

I have a macro that adds a very long formula to one of the cells.

Is there a way to break up this formula in the VBA editor to make it easier to view and edit.

Sheet3.Select

Dim lastrow As Long

Range("D2").Formula = "=SUM(IFERROR(VLOOKUP(E2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(H2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(I2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(J2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(K2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(L2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(M2,Scores[[Values]:[Score]],2,FALSE),0))"

Range("D2").AutoFill Destination:=Range("D2:D" & lastrow), Type:=xlFillDefault

It looks like this:

I'm trying to get it to look more like this:

A space and underscore didn't work.

I could add a carriage return but that just adds it to the formula, I'm trying to make it easier to view inside the VBA editor.

Might some kind of CONCAT do it?

If you're on Office 365 then these nested IFFERROR lookups could be simplified with `XLookup` — Mathieu Guindon, Dec 30 '22 at 22:21
Best way to break down a large formula remains to add helper columns IMO — Mathieu Guindon, Dec 30 '22 at 22:22

topsail · Accepted Answer · 2022-12-30T23:09:26.430

The simple, direct answer is to build your formula first, by itself. Below is an artificial and contrived example but it should show the main idea.

Clearly you might better find a different way to write that formula as it seems repetitive which might mean there are ways to improve it, but I thought to start with this basic answer to your question about what your were trying to do that wasn't working.

dim myFormula as string
myFormula = "=SUM("
myFormula = myFormula & "A2"
myFormula = myformula & ",B2"
myFormula = myFormula & ",C2"
myFormula = myFormula & ")"

Range("A3").Formula = myFormula

This will also work in VBA if you prefer to use line continuations:

Dim myFormula As String
myFormula = _
    "=SUM(A2" _
    & ",B2" _
    & ",C2" _
    & ")"

Range("A3").Formula = myFormula

VBasic2008 · Answer 2 · 2022-12-30T23:09:27.907

'Split' a Long Formula

Option Explicit

Sub WriteFormula()
    
    Const LastRow As Long = 20 ' just to make it compile
    
    Dim Formula As String: Formula = "" _
        & "=IFERROR(VLOOKUP(E2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(H2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(I2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(J2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(K2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(L2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(M2,Scores[[Values]:[Score]],2,FALSE),0)"
        
    'Debug.Print Formula
    
    Sheet3.Range("D2:D" & LastRow).Formula = Formula

End Sub

Result in the Formula Bar For Cell D2

=IFERROR(VLOOKUP(E2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(H2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(I2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(J2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(K2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(L2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(M2,Scores[[Values]:[Score]],2,FALSE),0)

How to break up a long formula in VBA editor?

2 Answers2

'Split' a Long Formula