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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,491
    Thanks
    212
    Thanked 852 Times in 784 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


  3. #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

  4. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,351
    Thanks
    49
    Thanked 275 Times in 253 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

  5. #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

  6. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 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

  7. #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

  8. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,351
    Thanks
    49
    Thanked 275 Times in 253 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
  •