How to break up a long formula in VBA?-CodePudding

I have a macro that's adding a very long formula to one of the cells.

I'm wondering if there's a way to break this formula up in the VBA editor up to make it easier to view and edit for other users the road.

Here's the code:

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.

I've also tried yelling at it but that hasn't worked either.

I'm wondering if some kind of CONCAT might do it? I'm pretty new to VBA (this is someone else's work that I'm modifying) so I'm not too well versed in what options are available.

Appreciate any insights!

CodePudding user response：

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

CodePudding user response：

'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)