What to do to avoid VBA function executed in the wrong workbook
I have several excel files that run timer and macros. But the big problem is when the macro for workbook A is called while workbook B is active. The macro runs in the wrong workbook and fails.
- Do I need to add windows () to the beginning of each function?
- If I have different modules, how do I pass this book name to all of them?
- It seems overwhelming and wrong. Is there a good solution to this problem?
Looking forward to your answers
source to share
You are on the right track with this
2.If I have different modules how to pass this book name to all of them
I'm assuming your macro is using a property, ActiveWorkbook
or is it just using Worksheet properties such as Range
without qualifying them?
Use instead of ActiveWorkbook
using ThisWorkbook
. Instead of using, Range
use ThisWoorkbook.Worksheets(1).Range
etc. Otherwise, the macro will assume that the active sheet is the one you want.
Sub MyMacro
Range("A1").Text = "Test"
End Sub
Try
Sub MyMacro(ByVal oWorksheet as Worksheet)
oWorksheet.Range("A1").Text = "Test"
End Sub
Then pass the worksheet object as a parameter.
You might also find a useful object ThisWorkbook
- this is the workbook that the macro is in, or the object Application.Caller
that is the object that invokes the current macro, such as a Range object if it's a cell formula, or presumably a timer object in your case.
source to share
If your macros behave the way you described them, they probably depend explicitly or implicitly on
ActiveWorkbook
or
ActiveSheet
Such dependencies should be avoided if possible. The macro recorder generates this kind of code, you must change it immediately when you record the macro.
For example, if you have a code like
s = Range("A1").Value
Excel implicitly changes this value to
s = ActiveSheet.Range("A1").Value
You can avoid this by referring to all cells, ranges, parts of the book, etc., explicitly using the right sheet or book object:
Dim sh as Worksheet
Set sh = .... ' Initialize sh the first time where the sheet is created or loaded
'later on:
s = sh.Range("A1").Value
Using form parameters
sh as Worksheet, wb as workbook
for your subscribers and functions, you can pass the correct sheet and workbook between modules, which answers your second question. And if you need access to the workbook your macro is in use ThisWorkbook
.
source to share