Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a function
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
Please Login or Register to view this content.
Does anyone have any thoughts on why this is happening?
Check whether the ThisWorkbook Module consist any Sheet_Activate event
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Sixthsense,
Thanks for your rapid response - 7 minutes!
I assume when you say ThisWorkbook Module, you mean the ThisWorkbook entry under the Microsoft Excel Objects folder. I checked and there is no code in that object. I also checked the objects associated with all my tabs - no code in any of them.
Without any running event it's not possible to call another function I think...Originally Posted by mycroft
Can you please share the code of that function?
FOLLOW UP
I just tried an experiment. I made a copy of my file and then deleted all the modules except the one with my Sheet Copy code. The pointer now steps through to the next line as expected. So there must be something wrong with one of the other modules, possibly the one containing the function that is stepped to. I'll do some experimenting.
Sixthsense,
Here is what is happening:
After executing the Sheet Copy code line, the excution jumps to the first line of a user-defined function in another module, in the same project (not in personal.xls). This module contains many user-defined functions that I have created, but most are not called in this workbook.
The function that the execution jumps to IS a function that I call from one of my worksheets. If I delete this function, then the execution jumps to another user-defined function that I call from a worksheet.
Thanks for the brief info and still curious to see the coding of that UDF
Here's the code:It is part of a set of functions that I wrote many years ago to do map projection calculations. I use these functions all the time and they work consistently and reliably, when called from a cell.Please Login or Register to view this content.
I'm going to keep deleting functions until the error goes away.
I am really confused with your UDF
Because you are creating an UDF
and Calling the same UDF within that UDFPlease Login or Register to view this content.
Totally confusedPlease Login or Register to view this content.
No, they are different.
UDF1: TMtoLL_VN49
UDF2: TMtoLL
UDF1 calls UDF2 with some hard-wired parameters. The code works just fine. :-)
Hi,
Any chance that there is some custom class and possibly some property declared as with events in it?
Last edited by buran; 04-08-2014 at 04:23 AM. Reason: wording
If you are pleased with a member's answer then use the Star icon to rate it.
Oops... Sorry for the oversight error
Hi buran,
I doubt there is a custom class, since I wrote all the code and I don't know what a custom class is.
Where would I look?
It was a small chance, but anyway - it was the other option, apart of ThisWorkbook module where a event driven code may be.
In a normal VBA project you have few modules under Microsoft Excel Objects - i.e. Worksheets, ThisWorkbook. If you add module - you get section/folder Modules with your module(s) in it. If you add UserForm you get section/folder Forms and if you add custom class - you get Custom Class section/folder in your project. If you don't see such folder in the project tree - you don't have custom class modules.
But if it's your code and you don't know of custom class - then I doubt you will have such folder
Mystery solved - perhaps!
I've done some more testing and what is happening is that the Sheet Copy command is triggering a re-calculation of all of the spreadsheets in my workbook, which of course includes re-calculating my UDF calls.
I tested this by putting breakpoints on the line with the Sheet Copy code and on the next line, which renames the copied worksheet. When the code reaches the first breakpoint, I press F8 (Step Into) and a copy of the worksheet is created. The pointer is then at the beginning of one of the UDFs that I call from a cell in one of my worksheet and a message "Calculating: 0%" appears at the bottom right of the window. I then press F5 (Continue) and the code then stops at the second breakpoint that I set and the Calculate message disappears.
I've set calculation to manual at the beginning of my code and then to automatic at the end, not so much for speed, but to avoid confusion when debugging in the future.
I've never seen this behaviour before in over ten years of working with Excel VBA, but then I have never created a copy of a worksheet before. Is this behaviour to be expected?
Last edited by mycroft; 04-09-2014 at 08:20 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks