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
– ApplePie, Commented Jul 14, 2017 at 13:03
You're right, but I forgot to specify the name of sheet and workbook are dynamic. sorry ... — Kalyh
– Kalyh, Commented Jul 14, 2017 at 13:07
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, Commented Jul 14, 2017 at 13:42
@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
– Kalyh, Commented Jul 14, 2017 at 14:10

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

0

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.

answered Jul 14, 2017 at 14:20

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kalyh Over a year ago

You're right, it is my problem! But your solution don't work in my case.

Kalyh · Accepted Answer · 2017-07-27 06:52:32Z

0

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

answered Jul 27, 2017 at 6:52

Kalyh

733 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related