+ Reply to Thread
Results 1 to 14 of 14

Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a function

  1. #1
    Registered User
    Join Date
    05-27-2008
    Location
    Saigon
    MS-Off Ver
    Windows Excel Professional Pro 2010
    Posts
    11

    Question 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:

    Please Login or Register  to view this content.
    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.

    Does anyone have any thoughts on why this is happening?

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,668

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    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

  3. #3
    Registered User
    Join Date
    05-27-2008
    Location
    Saigon
    MS-Off Ver
    Windows Excel Professional Pro 2010
    Posts
    11

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    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.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,668

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    Quote Originally Posted by mycroft View Post
    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.
    Without any running event it's not possible to call another function I think...

    Can you please share the code of that function?

  5. #5
    Registered User
    Join Date
    05-27-2008
    Location
    Saigon
    MS-Off Ver
    Windows Excel Professional Pro 2010
    Posts
    11

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    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.

  6. #6
    Registered User
    Join Date
    05-27-2008
    Location
    Saigon
    MS-Off Ver
    Windows Excel Professional Pro 2010
    Posts
    11

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    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.

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,668

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    Thanks for the brief info and still curious to see the coding of that UDF

  8. #8
    Registered User
    Join Date
    05-27-2008
    Location
    Saigon
    MS-Off Ver
    Windows Excel Professional Pro 2010
    Posts
    11

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    Here's the code:
    Please Login or Register  to view this content.
    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.

    I'm going to keep deleting functions until the error goes away.

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,668

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    I am really confused with your UDF

    Because you are creating an UDF

    Please Login or Register  to view this content.
    and Calling the same UDF within that UDF

    Please Login or Register  to view this content.
    Totally confused

  10. #10
    Registered User
    Join Date
    05-27-2008
    Location
    Saigon
    MS-Off Ver
    Windows Excel Professional Pro 2010
    Posts
    11

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    No, they are different.

    UDF1: TMtoLL_VN49

    UDF2: TMtoLL

    UDF1 calls UDF2 with some hard-wired parameters. The code works just fine. :-)

  11. #11
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    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.

  12. #12
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,668

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    Quote Originally Posted by mycroft View Post
    No, they are different.
    UDF1: TMtoLL_VN49
    UDF2: TMtoLL
    Oops... Sorry for the oversight error

  13. #13
    Registered User
    Join Date
    05-27-2008
    Location
    Saigon
    MS-Off Ver
    Windows Excel Professional Pro 2010
    Posts
    11

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    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?

  14. #14
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    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

  15. #15
    Registered User
    Join Date
    05-27-2008
    Location
    Saigon
    MS-Off Ver
    Windows Excel Professional Pro 2010
    Posts
    11

    Re: Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a functi

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Problem with worksheet Move/Copy function and Paste Special/Columns widths in Excel 2010
    By SPRoyLLC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2016, 05:41 AM
  2. Replies: 9
    Last Post: 09-11-2013, 07:07 AM
  3. Replies: 1
    Last Post: 05-23-2013, 02:25 PM
  4. [SOLVED] Code execution jumps from "end function" to the code beginning
    By Alexander_Golinsky in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-29-2012, 04:40 AM
  5. [SOLVED] Importing CSV file in 2010. The Filesearch function works in 2003, but not in 2010.
    By Xrull in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-13-2012, 06:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1