What to do to avoid VBA function executed in the w

Question

I have several excel files that have timer and macros executing. But a big problem is when workbook A's macro is called, while workbook B is active. The macro is executed in the wrong book and failed.

1. Do I need to put windows().active at the beginning of every function?
2. If I have different modules how do I pass this workbook name to all of them?
3. This seems excessive and not right. Is there any good solution to this problem?

Looking forward to your answers

Answer 1

You are on the right track with this

2.If I have different modules how do I pass this workbook name to all of them

I assume that your macro is using the ActiveWorkbook property, or just using Worksheet properties like Range without qualifying them?

Instead of using ActiveWorkbook use ThisWorkbook. Instead of using Range use ThisWoorkbook.Worksheets(1).Range and so forth. Otherwise the macro will assume that the active worksheet 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 may also find the ThisWorkbook object useful - it is the workbook the macro resides in, or the Application.Caller object, which is the object calling the current macro, for example the Range object if it is a cell formula, or presumably the timer object in your case.

Answer 2

If your macros behave the way you described it, they probably depend explicitly or implicitly on

ActiveWorkbook

or

 ActiveSheet

Those kind of dependencies should be avoided, if possible. The macro recorder produces such code, you should change it immediately whenever you have recorded a macro.

For example, if you have some code like

s = Range("A1").Value

Excel implicitly changes that to

s = ActiveSheet.Range("A1").Value

One can avoid that by accessing all cells, ranges, workbook parts etc. by explicitly using the right sheet or workbook 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

By using a parameters of the form

sh as Worksheet, wb as workbook

for your subs and functions, you can pass the right sheet and workbook between modules, which answers your second question. And if you need access to the workbook where your macro resides, use ThisWorkbook.

Answer 3

I'd go one further... make sure your code doesn't have Selection or ActiveCell

objects within them. You would need to rewrite these using the Range object.