VBA Function. Running Cumulative Costs across multiple sheets

Question

Long time, first time.

I am creating a sheet for workover rigs in Oil and Gas field work. Each sheet is identical in structure but represents a different day of work. I have buttons that add and subtract sheets as new days are required, which complicates my main problem:

I need to create a function that calculates the cumulative cost of various categories up to that sheet in the workbook. I need a way to reference the cell adjacent to the cell containing the function (containing the current day's cost), as well as the previous sheet's cumulative cost (or each previous sheet's daily cost, in the same cell one sheet back) to create a new cumulative value. I want each sheet's cumulative to be cumulative up to that day.

A subroutine would be much more conducive to this, but I do not want someone to have to click a button to generate a cumulative in these cells. It needs to automatically calculate as information is added.

What I have so far is below. The sheets are named by the numbered day on the job (1,2,3,4 etc) and Cell AD7 in each sheet contains the value of the current day on the job as well. Basically what I need is a way to extract and use the address of the input cell "daily".

Function Cat_Cum(daily)
Dim day, i As Integer
day = Cells(7, 30)

For i = 1 To day
    If i = 1 Then
        Cat_Cum = Sheets(i).Cells(daily.Address)
    Else
        Cat_Cum = Sheets(i).Cells(daily.Address) + Cat_Cum
    End If

Next i

End Function

Recap worksheet structure:

2 columns: daily cost, cumulative cost. Each sheet is identical in structure. Cumulative should be the total of each previous sheet's daily value in that same cell.

Thanks for the help!

Your function name is one of the many many reasons I recommend developers not abbreviate identifiers. With auto-complete/intellisense/whatever_you_want_to_call_it, long (descriptive) names are no longer a problem. — Lews Therin
Ha, I didn't even notice that. Cum with a long u is a word used very often in the oil and gas industry as a replacement for cumulative. It is a funny combination though. — Travis Preston

A.S.H A.S.H · Accepted Answer · 2017-06-20T20:19:55

IIUC you want your UDF to be like this:

Public Function Cat_Cum(daily As Range)
  Cat_Cum = daily.Value2
  If Application.caller.Parent.Name = "1" Then Exit Function

  Cat_Cum = Cat_Cum + _
    Worksheets(CStr(Application.caller.Parent.Name - 1)).Range(Application.caller.Address).Value2
End Function

so in each worksheet named "1", "2", "3" etc.., in the cumulative column you type, =Cat_Cum(A2) in row 2 and fill down. Replace the column of the daily stuff in place of A if it is another column.

VBA Function. Running Cumulative Costs across multiple sheets

1 Answers