I am getting conflicting / unhelpful messages in Excel VBA

Question

0.00/5 (No votes)

See more:

OK, this is my predicament :

If I run my VBA program I get an error message "Argument Not Optional" ... but if I debug I get the message "Can't execute code in break mode" !!

My main VBA is in Thisworkbook and the new code is as follows :

VB

Range("B11").Activate
    
    Set myRange1 = Range("B" & (10 + mySession))
    Set myRange2 = Range("H" & (10 + mySession))
    
    If myRange1 = "" Then
        Call StartBlink(myRange2)
    End If
        
    Application.ScreenUpdating = True

The Module Blinking_Text contains the following code (this is adapted from code I have found online, I am not sure what this RunWhen stuff does) :

VB

Option Explicit
Public RunWhen As Double
 
Sub StartBlink(BlinkCell As Range)
    
    If BlinkCell.Font.ColorIndex = 1 Then
        BlinkCell.Font.ColorIndex = 2
    Else
        BlinkCell.Font.ColorIndex = 1
    End If
    
    RunWhen = Now + TimeSerial(0, 0, 1)
    
    Application.OnTime RunWhen, "StartBlink", , True
    
End Sub
 
Sub StopBlink(BlinkCell As Range)

    BlinkCell.Font.ColorIndex = xlAutomatic
    
    Application.OnTime RunWhen, "StartBlink", , False
    
End Sub

Any ideas ?

Posted 25-Mar-15 14:31pm

Gary Heath

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-03-26T03:53:00

You are getting the "Argument not optional" message because the StartBlink sub-routine expects a parameter to be passed to it and you have not included the BlinkCell in the call to OnTime

Normally, to pass a parameter using OnTime you would enclose the sub name and parameter list within single-quotes, such as

Application.OnTime RunWhen, "'StartBlink H,10'", , True

but that is not an easy thing when the parameter is a Range. You could pass in the cell name and construct the range within the StartBlink sub, but I found it easier just to pull the range out to a public variable

Public BlinkCell As Range

so the routine signature becomes

Sub StartBlink()

That now works.

Gary Heath wrote:
I am not sure what this RunWhen stuff does

Chip Pearson wrote the original code and in this article[^] he explains why RunWhen is abstracted out to a public variable - see the section on stopping the timer.

Gary Heath wrote:
if I debug I get the message "Can't execute code in break mode" !!

That message is generated from the pending OnTime call. Basically it is saying that it can't execute the call because you have the program stopped in the debugger and it is trying to run it every second. If you temporarily change the interval e.g.

RunWhen = Now + TimeSerial(0, 0, 15)

you will get an opportunity (well, a 15 second opportunity!) to debug. Alternatively you can call the StopBlink routine from the Immediate window to cancel any pending timers.