Excel VBA: how can I restrict code execution to the sheet

I have a sheet with a function called in a cell in the sheet1.
When I change to the sheet2, edit something and go back to sheet1, I see the value change (because I use ActiveWorkbook and ActiveSheet). If I do something in sheet1 the value come back.

I can have multiple workbook with the same data too ...

Edit: I forgot to specify the name of workbooks and sheets are not static, all is dynamic.

Edit 2: I think Excel do a refresh of all sheet when editing a sheet and VBA code is execute, but the activesheet is not the Sheet1 where the data is ... So, VBA code run in the wrong sheet.

Edit 3: The sheet have "Calculation Options" to "Automatic" and I have a button in the bottom of my Excel page "Calculate" to force refresh of all formulas and VBA code.

Excel cell content:

=IF(BD66;MainFunction(BJ66);"")

Main Function:

Function MainFunction(var)
    MainFunction = (var * Test()) / (...)

End Function

Sub Function is use in several functions:

Private Function Test()
    Test = ActiveWorkbook.ActiveSheet.Range("BE50")
End Function

How can I do for execute code only on the active sheet and not on all sheet ?

Or what is the best way for do that ?

Thanks for your help.

Answer

I've find a workaround for my problem ...
I add parameters in my main function for replace all sub function which use "Active..."

My formulas are less simple to build, but it works in the sheet which contains formula ...

Thanks for your helps