How to create a function from an existing table

[zinzolin]

Hello,

On a excel sheet, I created a table that, from two values (heat and building) calculates the running time of a cogeneration unit. To do this, I created a table with various columns, references to tables in the same sheet and to tables in other sheets. The table is 19 rows by 11 columns.
But I have many builiding/heat combination to calculate. And I would like to avoid having to duplicate this table hundred times.

What I would like to do is use this table as a function. Something in VBA for example that I could use as =runningtime(heat,building). Please find an example attached. Table as a function.xlsx

Does somebody habe an idea on how to do this?

Thanks a lot for the help,

Nicolas

[zinzolin]

I tried to simplify my needs and decided, instead of creating a function, that a regular macro could also do it.

Instead of having the building and the heat as parameter, I took the cogeneration unit. I am then filling a table with where one column is the cogeneration unit (fixed list) and the other is the calculated runningtime.

Sub RunningTime()

    Dim i As Integer
    Dim cogen As String
        
    cogen = Worksheets("Berechnung").range("G4").Formula 'Backup of the current bhkw
    i = 0 'vertical offset = 0
    
    'Fill the table
    Do
    Worksheets("Berechnung").range("G4") = Worksheets("Berechnung").range("AJ10").offset(i, 0)
    Calculate
    Worksheets("Berechnung").range("AJ10").offset(i, 1) = Worksheets("Berechnung").range("I34") 'value in next column equals running time
    i = i + 1
    Loop Until IsEmpty(Worksheets("Berechnung").range("AJ10").offset(i, 0))
    
    'Restore backup
    Worksheets("Berechnung").range("G4") = cogen
    
End Sub