1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro engaging but not working, and produces error message after running thorough the entire macro.

Discussion in 'VBA Macros' started by Eloise T, Aug 13, 2018.

  1. Eloise T

    Eloise T Active Member

    Messages:
    812
    I am trying to understand why the following error message appears after seemingly "successfully" stepping through the "StartFlashing()" macro with the VBEditor focused on the SEVENTH tab in the attached file. Running the macro directly results in the same error message. Either the flashing is happening too rapidly to see the flashing square root symbols or it's not working at all.

    upload_2018-8-13_13-44-41.png

    If anyone can explain why this happens and a suitable remedy I would certainly appreciate it.

    Attached Files:

  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,380
    The last line of your code says this
    Code (vb):

    Application.OnTime xTime, "StartFlashing", , True
    Which is saying that after finishing, it will run again at a future time. It's this second time that's failing. Reason is that XL expects regular macros to be stored in code modules, not sheet or ThisWorkbook module. If you cut and paste all your code into one of your regular code modules, it'll work.
    Chirag R Raval likes this.
  3. Eloise T

    Eloise T Active Member

    Messages:
    812
    You hit the nail on the head. I don't know the difference between a code module and a sheet module or any other kind of module. How do I tell the difference? ...

    When I "engage" VBA code, I hit ALT+F11, then F7, then paste the code there.

    What should I do differently?

    Thank you.
    Last edited: Aug 14, 2018
  4. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    Clicking on F7 puts the code into the Sheet module. If you wish, as generally is the practice, to put it into the code module, click on the Insert button on the menu line and select module. Sheet and Workbook modules should generally be used for events that are triggered by actions, ie. Open, change, etc.
    Chirag R Raval likes this.
  5. Marc L

    Marc L Excel Ninja

    Messages:
    4,257

    With a non general module you must insert the module name
    and a dot before the procedure name …​
  6. Eloise T

    Eloise T Active Member

    Messages:
    812
    We've now discovered "general modules" in addition to code modules and sheet modules? Marc, please explain non general, etc. and give examples so my ignorant VBA mind can absorb this new information.

    Here's what I've discovered...
    I feel like I'm trying to help a friend trying to get over a bad cold.
    I have to have the macros StartFlashing and StopFlashing in BOTH the ThisWorkbook module AND the Module 1 before they "work" without causing an error or other undesirable result. Removing the (identical) code from either ThisWorkbook module OR Module 1 causes convulsions, sneezing, wheezing, and uncontrolled coughing.

    THAT'S the good news. The Bad news is the wrong cells in Column K are flashing. The VBA code (in the StartFlashing macro) is ignoring the s and changing the otherwise black dashes to red and flashing the dashes and not the s. I'm pulling my hair out.

    Updated file uploaded.

    Help...please.

    Attached Files:

    Last edited: Aug 15, 2018
  7. Luke M

    Luke M Excel Ninja

    Messages:
    9,380
    Sorry for confusion Eloise. If you have the Project expolorer open in the VBE, it should look like this. If not, go to View - Project Explorer.

    upload_2018-8-15_8-10-13.png

    This is a list of all the "things" you have in your project. You can faintly see that the ThisWorkbook module is highlighted, indicating that this would be the module I'm looking at (not shown in picture). This is where you code is currently sitting. There's also sheet modules, for each of the Sheet1, Sheet2, etc. You don't have anything in there currently.

    You'll see there's another group called Modules, with 3 regular code modules (Module1, Module2, etc.). You need to use one of these for your code. Double-click on the item in the Project explorer, and you'll go to that corresponding module.

    Quick run down:

    ThisWorkbook module should be used for Workbook level macros, stuff that's triggered by saving, closing, etc.

    Sheet modules should have sheet dependent macros, like change events, or selection changes.

    Regular modules (aka general modules, aka standard code modules) should have more generic code, and anything that needs to be referenced by other code.

    additional reading: http://www.cpearson.com/excel/codemods.htm
    Eloise T and AlanSidman like this.
  8. Eloise T

    Eloise T Active Member

    Messages:
    812
    Luke,
    The additional reading helped. Thanks.

    Please read (fully) response #6 and note updated upload.


    I had to put the macros StartFlashing and StopFlashing in BOTH
    the ThisWorkbook module AND the Module 1 before StartFlashing and StopFlashing "work" without causing an error or other undesirable result.

    Removing the code from either ThisWorkbook module OR Module 1 caused Excelerated convulsions, sneezing, wheezing, and uncontrolled coughing.

    I'll figure out the module game eventually. Thanks.

    Any idea why I can't get the s to flash? It doesn't seem to matter if they are "generated" by the underlying formula and purposely placed there by hand, the VBA doesn't seem to recognize them.
    Last edited: Aug 15, 2018
  9. Eloise T

    Eloise T Active Member

    Messages:
    812
    Please see attachment to follow up with the above #8 reply.
    Thank you.

    Code (vb):
    Sub StartFlashing()
        Dim Ws As Worksheet, xCell As Range, c As Range, t As Double
        For Each Ws In ThisWorkbook.Worksheets
        If Ws.Index > 6 Then
                Set xCell = Ws.Range("K3:K15")
                For Each c In xCell

                    If c = WorksheetFunction.Unichar(8730) Then 'a square root symbol
    '                  THE ABOVE LINE SEEMS TO BE IGNORED.

                       If c.Font.Color = vbRed Then
                          c.Font.Color = RGB(238, 236, 225) 'tan background color
                       Else
                          c.Font.Color = vbRed
                       End If

                    End If

                Next c
            End If
        Next Ws
        xTime = Now + TimeSerial(0, 0, 1)
        Application.OnTime xTime, "StartFlashing", , True
    End Sub

    Attached Files:

  10. Debaser

    Debaser Active Member

    Messages:
    436
    Your conditional formatting rule is preventing the flashing - remove it.

    The only code that should be in the ThisWorkbook module, is this:

    Code (vb):

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        On Error resume next
        StopFlashing
    End Sub

    Private Sub Workbook_Open()
        Call StartFlashing
    End Sub
     
    All the rest of it should be in a normal module.

    Attached Files:

    Eloise T likes this.
  11. Eloise T

    Eloise T Active Member

    Messages:
    812
    Debaser,

    *B*R*I*L*L*I*A*N*T*!* Thank you!

    [​IMG]

    ...and to all the other contributors, a big thanks as well.
    Last edited: Aug 16, 2018
    Chirag R Raval likes this.
  12. Debaser

    Debaser Active Member

    Messages:
    436
    I still feel slightly unclean helping with such things, but you're welcome.
  13. Eloise T

    Eloise T Active Member

    Messages:
    812
    I don't understand "unclean." Why so?

    Some background...I was thrust into a new position at work about three years ago (to date) with just about zero Excel experience (no, I had zero previous Excel experience) and stumbling onto Chandoo.org saved my bacon (read: my job). The company would not pay to send me to any Excel classes to get me up to speed.

    My entire Excel experience has come from contributors like yourself and reading other bloggers problems exclusively at this website.
    Last edited: Aug 16, 2018
    Chirag R Raval likes this.
  14. Debaser

    Debaser Active Member

    Messages:
    436
    Anyone who sent me a workbook with flashing cells in it would come to regret that decision. I can't abide them! Worse even than pie charts. Though maybe not 3D pie charts.
    Chirag R Raval and Stevie like this.
  15. Eloise T

    Eloise T Active Member

    Messages:
    812
    I understand. That seems to be a general consensus...and understandably so.

    There should only be zero to three flashing cells (s) per week alerting me to a potential and nasty faux pas and once they are noted, they are deleted as they have served their purpose.

    It's a job-saving safety check and control device to make sure nothing slips through the cracks which has happened once or twice in the past.
    Last edited: Aug 16, 2018
    Chirag R Raval and Debaser like this.
  16. Stevie

    Stevie Active Member

    Messages:
    116
    I think Debaser is referring to the idea of flashing cells in general being unattractive and a visually offensive solution, an opinion I find myself agreeing with - I tend to get a headache.
    With regards to your question, how you framed your question, the pursuit of learning, or the source of your learning material, I have no issues there, and I don't think Debaser has either.

    I'm glad you found a solution from an problem solving perspective, I hope you don't have to work with repeatedly flashing cells again in the future - I'm not sure I could handle it!

    Stevie

    Edit: I believe we replied almost simultaneously. Your response renders mine unnecessary, I'm relieved that you aren't subjected to flashing cells all day!
    Chirag R Raval likes this.
  17. Eloise T

    Eloise T Active Member

    Messages:
    812
    I greatly appreciate your link on troubleshooting/debugging VBA!
    http://www.cpearson.com/excel/DebuggingVBA.aspx
  18. Debaser

    Debaser Active Member

    Messages:
    436
    It's my issue, not yours, so don't worry about it! :)
  19. Eloise T

    Eloise T Active Member

    Messages:
    812
    I fully understand. Thanks for your input.
  20. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    My 2 cents ppv : permanent flashing in Excel is a mess
    as it was not designed for that.
    I just use a background color to highlight any cell
    (whatever via a conditional formatting or via a procedure)
    and / or even a popup message.
    In a few cases I have designed a temporary flashing
    but never stay with a permanent one
    as it could raise too many cases of issue …
    Chirag R Raval likes this.
  21. Eloise T

    Eloise T Active Member

    Messages:
    812
    2 cents noted.
    Last edited by a moderator: Aug 16, 2018

Share This Page