Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    34
    Thanks
    2
    Thanked 1 Time in 1 Post

    Angry Excel macro function firing when it shouldn't

    Hi all,
    This has been bugging me for quite some time and can't make sense of it. I have an excel 2000 spreadsheet with a number of sheets. One sheet holds demographic info on members of an organisation. I made use of excels ability to use vba functions in cells e.g. one macro calculates whether the member is => 65 years old (OAP), another reduces middle names to initials. The trouble is that they (and others) run whenever ANY sheet is changed. I've tried the code in Thisworkbook Workbook_Sheetchange, the Worksheet module Worksheet_Change, and it currently resides in a standard module - the only place where it works. I've tried it with application.volatile (true and false), Application.EnableEvents (true and false). If I had any hair left, I'd tear it out! Does anyone else recognise this sort of behaviour or got something similar to work?



    DougieW

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,157
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Dougie,

    Could you post a sample workbook with some test data and the macros?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    34
    Thanks
    2
    Thanked 1 Time in 1 Post

    Red face Thanks for your message

    Hi RetiredGeek (so am I)

    I was afraid someone would ask for this but I will send something. I'll need to anonymise (is that a word?) the data but I'm sure I can, and I've got baby sitting duties today and tomorrow so it may be a few days. Thanks for your interest.

    DougieW

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,192
    Thanks
    43
    Thanked 226 Times in 210 Posts
    Do you by chance have any code residing in the Private Sub Worksheet_Calculate() event subroutine of any of the sheets? I would comment out your macros one at a time (or Exit Sub after the procedure's header) and see when the mal-behavior stops. Start with the worksheet and workbook event subroutines as well as any Auto_xxxx routines.

    HTH,
    Maud

  6. #5
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    34
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Maudibe,
    I haven't used Worksheet_Calculate() event in any of the sheets. I'll try your other suggestion though it will take some time as there are quite a number of them.

    Dougiew

  7. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,485
    Thanks
    26
    Thanked 171 Times in 167 Posts
    Hi Dougie

    Have you considered NOT using VBA, and just using formulas instead???

    For example, although you can create an Excel function to calculate whether a member is =>65 years, you can do this more easily just using a formula. You can also use formulas to reduce middle names to initials.

    In general, doing things in VBA is probably 100 times slower than doing it directly in the sheet using formulas.

    But if you were to post a sample extract of your workbook, I'm sure the experts here will guide you to a solution.

    zeddy

  8. #7
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    34
    Thanks
    2
    Thanked 1 Time in 1 Post

    Talking Problem Solved

    Hi All,
    Thanks all, particularly to Maud - you put me on the right track. It was the method of calculation, which is a bit bizarre, but well explained in http://support.microsoft.com/kb/214395 - Description of how Excel determines the current mode of calculation. I remembered previously trying to fix this problem using manual calculation but it was inconsistent. I could never understand why manual mode seemed to revert to auto and vice versa. Finding and reading that KB article explained why. I'll now write some code to control it - once I figure out the logic I need. :

    Onward and Upward

    DougieW

  9. #8
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,192
    Thanks
    43
    Thanked 226 Times in 210 Posts
    Glad to hear you solved your problem. Thanks for the update and link.
    Maud

Tags for this Thread

Posting Permissions

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