我在Excel VBA中收到冲突/无用的消息 [英] I am getting conflicting / unhelpful messages in Excel VBA

问题描述

好吧，这是我的困境：



如果我运行我的VBA程序，我收到错误消息Argument Not Optional...但如果我调试我得到消息无法在休息模式下执行代码!!



我的主要VBA在Thisworkbook中，新代码如下：

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 :

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

模块Blinking_Text包含以下代码（这是根据我在网上找到的代码改编的，我不知道这个RunWhen的东西是什么）：

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

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 ?

推荐答案

您收到Argument not optional消息，因为 StartBlink 子例程需要一个参数传递给它并且你没有在 OnTime的调用中包含 BlinkCell



通常，要使用 OnTime 传递参数，您可以将子名称和参数列表括在单引号中，例如

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

但是当参数为 Range 时，这不是一件容易的事。您可以传入单元格名称并在 StartBlink 子区域内构建范围，但我发现将范围拉出到公共变量更容易

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

所以例程签名变为

Sub StartBlink()

现在有效。

That now works.

Gary Heath写道：

Gary Heath wrote:

我不确定这个RunWhen的东西是什么

I am not sure what this RunWhen stuff does

Chip Pearson写了原始代码并在此< a href =http://www.cpearson.com/excel/OnTime.aspx>文章 [ ^ ]他解释了为什么RunWhen被抽象为公共变量 - 请参阅停止计时器的部分。

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写道：

Gary Heath wrote:

如果我调试我收到消息无法在中断模式下执行代码!!

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

该消息是从挂起的OnTime调用生成的。基本上它是说它无法执行调用，因为你在调试器中停止了程序并且它每秒都尝试运行它。如果您暂时更改间隔，例如

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)

你将有机会（好吧，15秒的机会！）进行调试。或者，您可以从立即窗口调用 StopBlink 例程来取消任何待处理的计时器。

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.

这篇关于我在Excel VBA中收到冲突/无用的消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！