Can I use a worksheet as a function?

[RobertBobert]

Hi,
I would like to use the calculations on one worksheet as a function in another worksheet.
To explain, let's say I have two input values in Sheet1, Number_of_staff in A1 and Hourly_rate in A2.
Then, the total cost for my project appears in A3.
But calculating the total cost might use 100 different calculations within Sheet1. For instance, the rent stays the same as the number of staff increases, but the number of uniforms I have to buy goes up with the number of staff. All of this complexity is easy to hadle in a sheet, but now, if I want to optimise, I can't easily graph or explore what happens to A3 as I change cells A1 and A2.
What I want is to create a function that I can call like this: =total_cost(num_staff,hourly_rate).

This function would return whatever value would appear in A3 if my two arguments were placed in A1 and A2. I could then have a bunch of variations of values for A1 and A2 to see the effect on A3.

Now, chatGPT insists this is possible, and wrote me the following code (that doesn't work):

Function TotalCost(num_staff As Double, hourly_rate As Double) As Double
Dim ws As Worksheet

' Referencing the worksheet where your calculations are
Set ws = ThisWorkbook.Sheets("Total_cost")

' Disabling events and setting calculation to manual
' to prevent continuous recalculations
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

' Update your input cells
ws.Range("A1").Value = num_staff ' For number_of_staff
ws.Range("A2").Value = hourly_rate ' For hourly_rate

' Let Excel calculate the worksheet
Application.Calculate

' Get the value from your output cell
TotalCost = ws.Range("A3").Value ' Where the final result is

' Resetting calculation and enabling events back
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Function

I so want this to be a thing, but I just get value errors when I run the function.

Can anyone tell me if this is possible, and if so, what is wrong with the code above?

 

[RoryA]

ChatGPT is dim, which is why it is not allowed here. That will not work.

What you are describing sounds like a 2 variable data table, which does not require VBA.

 

[RobertBobert]

ChatGPT is dim, which is why it is not allowed here. That will not work.

What you are describing sounds like a 2 variable data table, which does not require VBA.

Thanks. It has its moments of briliance, but in most cases just gives something like an average of all the answers everyone has ever written.
I can see how a 2 variable data table would work with a n existing function. I'm not sure how it would work with a worksheet of calculations. i.e. I am not sure what function I would be calling to put the two values in. But I will investigate that further. In the meantime I just wrote a macro that works through a list of input values in two columns. Then it copies the output cell(s) to a new list beside those two columns, so whatever the output is gets captured for whatever combination of input values I choose.
This "works", but it requires me to run the macro to update it.
I'll look a bit deeper into the 2 vaiable table...
Thank you!

 

[RoryA]

Based on your simple example, the top left cell of the data table would just be a link to A3 (your result cell). The row and column input cells would be A1 and A2. The result table will essentially substitute each combination of the row and column inputs into A1 and A2 and output the relevant results that would be calculated in A3 based on all the calculations on your sheet.