CODEWITHSUNDEEP
excelvba
Richard Barnes
Richard Barnes

Reputation: 55

Should you use Absolute References in VBA code

While cleaning up and optimizing a large bit of VBA code I thought about something which I never had before. As you can see in the following code block, I always use Absolute cell references for "Static" cells which must always refer to the same address when writing a formula in VBA. I know the importance of using Absolute references in formulas, but is there any need to always refer to such cells in this manner? For example, does the first reference (of cell G12) ".Range("G12").Formula ="=SUM(J16:J" & LastCORow) * $G$12" need to be "$G$12 if it is a cell value used in the formula. I am asking if it is either necessary or good a coding practice.

' refresh the formulas
       ' G8  Materials   =SUM(F16:F34)
       ' G9  Labor       =SUM(H16:H34)
       ' G10 Hours       =SUM(I16:I34)
       ' G11 SubContracts    -->depends on G2 (Markup)
       ' G12 Profit Margin   -->depends on G3    (Profit/Overhead)

       ' set the subcontracts formula and label
        If Len(.Range("E11").value) Then  ' if the Invoice was set to markup the Sucontracts
            .Range("G11").Formula = "=SUM(G16:G" & LastCORow & ")*(1+$G$2)"
            .Range("E11").value = "Subcontracts (" & (.Range("G2") * 100) & "% Markup):"
        Else
            .Range("G11").Formula = "=SUM(G16:G" & LastCORow & ")"
            .Range("E11").value = "Subcontracts:"
        End If

        ' G8  Materials
        .Range("G8").Formula = "=SUM(F16:F" & LastCORow & ")"
        ' G9  Labor
        .Range("G9").Formula = "=SUM(H16:H" & LastCORow & ")"
        ' G10 Hours
        .Range("G10").Formula = "=SUM(I16:I" & LastCORow & ")"
        ' G12 Profit Margin   -->depends on G3    (Profit/Overhead)
        .Range("G12").Formula = "=SUM(J16:J" & LastCORow & ",K4,J8:J10,($G$2*SUM(G16:G" & LastCORow & ")))* $G$3"
        ' Lastly the job total
        .Range("C" & LastCORow + 4).Formula = "=ROUND(SUM(J16:J" & LastCORow & ",K4,J8:J10, ($G$2*SUM(G16:G" & LastCORow & ")))*(1+$G$3),0)"

In the previous code, all of the values in column G are used as calculation variables in formulas, their address never changes. So the question simply put is do they all need Absolute references..?

Upvotes: 2

Views: 1017

Answers (2)

D_Bester
D_Bester

Reputation: 5911

My preference is to leave them relative. That way if someone copies a sum formula to the right it'll then sum up that column.

The second reason to keep it relative is that if the user creates a sum formula manually it'll be relative. So it makes sense to do the same in the code.

Upvotes: 0

Chris Melville
Chris Melville

Reputation: 1518

If your reference cells never change address, then yes: absolute references are correct.

There's no single answer as to whether to use absolute or relative addresses in formulæ. It all depends on how you're using the data. In your case, it makes good sense to have them absolute.

Upvotes: 1

Related Questions