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

Question

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.

Your question is not very clear to me but you can specifically target a worksheet using either Worksheets("Sheet1") or Sheets("Sheet1"), with or without specifying ActiveWorkbook, depending on your usage. — ApplePie
You're right, but I forgot to specify the name of sheet and workbook are dynamic. sorry ... — Kalyh
Can you post your actual code and functions you are trying to call? Even though they are dynamic, you only want them to work on the current sheet you are on, so the ActiveSheet.Range should work fine, as it's only on that sheet. Without seeing your cell information/real code, I'm not sure how much assistance I can be. — Busse
@Busse : I've add information in my post. Indeed, only sheet1 is update, yes. But, the function retrieve wrong data. If I edit sheet2, ActiveSheet is Sheet2 and not Sheet1. So, when I go back to Sheet1, I have "#VALUE!", because the VBA function run with Sheet2 as ActiveSheet ... I just want run VBA code only on the current sheet and not on all sheets or workbooks. It would save me if I can do that. — Kalyh

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2017-07-14T14:20:32

From what I can see - your Test function causes the problem by always looking at the activesheet rather than the sheet that contains the =IF(BD66;MainFunction(BJ66);"") formula.

To look at this sheet you need to look at the cell that called the function using Application.Caller:

Public Function MainFunction(Target As Range) As Double

    MainFunction = Target * Test(Application.Caller)

End Function

Private Function Test(Target As Range) As Double
    Test = Target.Parent.Range("BE50")
End Function

I've updated var to Target as that's what I'm used to seeing in worksheet events.

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

2 Answers