Macro getting confused differentiating ActiveWorkbook and ThisWorkbook

1
votes

1st post, but not 1st time visitor - this forum has been an invaluable help in developping my VBA coding skills over the last 3 years - so a big thank you.

Here's a weird issue I've encountered.

I've created a month end template for my team to use. This template stores a number of Macros, including some activated by keyboard short cuts. Those short cuts activated macros are all run on other reports in CSV format.

I've ensured that the macro would stop running if the active workbook was the main workbook, as it does a lot of formatting and would seriously damage the main workbook.

So this fail safe short code will be run hundreds of time a day..... and occasionally, once or twice a month, excel gets confused. Even though the ActiveWorkbook isn't ThisWorkbook, it acts like it is and aborts the macro.

'Prevents from running the macro on the Month End Allocation Template

Dim w As Workbook

Set w = ActiveWorkbook

If w Is ThisWorkbook Then
Exit Sub

End If

I can't really get my head around it, the code is pretty simple, and has 100% prevented unwanted damages to the main workbook (the main thing really), but occasionally prevents the formatting macro to be run other workbooks.

In those rare instances it happens, the user has to close all excel workbooks, and once reopened, it will work again just fine. :|

Is it just an excel bug? Is there something here I didn't consider and should this code be improved? I love its simplicity, and it has 100% avoided damages to the main workbook, I'm just annoyed that excel seemingly gets confused by what ThisWorkbook is/should be, and that i cannot explain it.

1
excelvba
Why don't you define the other workbooks? Set w = Workbooks.Open(Path & name) or Set w = Workbooks(Name & extension) the use of the ActiveWorkbookshould be avoided as much as possible. - Damian
The same way you want to avoid relying on the .Selected cell or range, you will want to avoid relying on the .Active workbook &/or worksheet. Relying on these methods leaves room for user error which is no good. There is no need to do this when you can explicitly state what your two books are as noted by @Damian - urdearboy
@Damian this would be challenging. The workbook name is variable and comprise the month year (format MMYYYY), which would be easy, but also a store number which could be any of 830 stores numbered between 1 and 2000. The store number would be a big stumbling block. I'll look into it, but since the user manually opens the workbook on which they will run the macro, I'm not sure I'll be able to define the store number. I'm might be able to go around itwith comparing just part of the workbook's name though, as this is predictable. - Antoine
@urdearboy, I thought ThisWorkbook was pretty solid? The reference to the active workbook has worked very well to avoid the macro being run on ThisWorkbook, but for some reason I can't understand, the Active Workbook is confused with Thisworkbook. It will happen once or twice a month, and i can see it - in debug mode, starting from the target workbook (what should be the active workbook), the VBA codes ends up exiting the sub thinking it is ThisWorkbook. If the user closes all excel windows and starts again, it then works fine. - Antoine
@urdearboy you are correct though, and i've been pretty specific with most of my coding lately. - Antoine

1 Answers

0
votes

Any main workbook that you don't want users to mess up should have protections that are turned off and on by your macros. Separate your data from your views. Lock the views and modify only the data. For you this means the CSV would import in to columns on a hidden sheet and then the data on your protected sheet with the linked data table would just magically update. There should be no formatting macro. The format is static. The linked data is what changes.