Accessing class instance from worksheet

Question

I made a class module ComplicatedCalculation.

I have several worksheets which need their own instance of ComplicatedCalculation, initialized to slightly parameters in each sheet.

Each ComplicatedCalculation has a function function do_calc(rng as Range) which outputs an answer based on additional inputs from the worksheet.

Public cc_for_Sheet1 as New ComplicatedCalculation
Public cc_for_Sheet2 as New ComplicatedCalculation
Public cc_for_Sheet3 as New ComplicatedCalculation

How can each worksheet access its own cc?

Solutions I considered seem ugly

I don't like this because I don't want the user to fuss about specifying the correct worksheet when trying to call do_calc. It always has to be the worksheet they are using.

Function do_calc(rng as Range, ws_name as String)
    Dim cc as ComplicatedCalculation
    if ws_name = "Sheet1" then
        cc = cc_for_Sheet1
    elif...
    end if
    do_calc = cc.do_calc(rng)
End Function

I don't like this because I'm worried the code will break when someone does Ctrl+Alt+F9 (workbook refresh), or innocently changes the worksheet name.

Function do_calc(rng as Range)
    Dim cc as ComplicatedCalculation
    if Application.ActiveSheet.name = "Sheet1" then
        cc = cc_for_Sheet1
    elif
    end if
    do_calc = cc.do_calc(rng)
end function

Answer 1

Application.ThisCell or Application.Caller (in a function called from a worksheet) both return the cell where the UDF was entered. You should always use one of those methods and not ActiveSheet if you want to know where your UDF code is getting called from.

If you store your calculation objects in a global dictionary keyed to each worksheet, you can do something like this

Public calcs As Object

'Set up calculation class instances
Sub InitCalcs()
    Dim cc as ComplicatedCalculation
    Set calcs = CreateObject("scripting.dictionary") 'initialize dictionary

    'Initialize and configure each instance of `cc` and
    ' store in the dictionary like
    calcs.add ws, cc
    '...where ws is the associated worksheet reference and cc the configured object
    
End Sub

'UDF called from worksheet
Function do_calc(rng As Range)
    Dim ws As Worksheet
    
    Set ws = Application.ThisCell.Worksheet 'calling worksheet
    
    If calcs.Exists(ws) Then  'calculation object for this sheet exists?
        do_calc = calcs(ws).docalc(rng)
    Else
        do_calc = "not configured!"
    End If
   
End Function