excelvbavbe

How to break up a long formula in VBA editor?

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:
enter image description here

I'm trying to get it to look more like this:
enter image description here

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?

Solution

  • 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