VBA如何声明波动性 [英] How VBA declared Volatility works

问题描述

我有一种情况，我希望一个函数是部分的挥发性的 - 也就是说，它是不稳定的，直到它不再返回一个错误，在这一点上它将不再被计算，除非它的一个参数改变（即标准非肮脏的波动）。

到目前为止，我已经尝试了三种方法，其中一种都不起作用。任何人都可以提出任何建议？

方法1： Application.Volatile in a IF condition

 公共功能VolTest（）As Long 
静态lngCounter长为
如果lngCounter< 5然后
 Application.Volatile 
 End If 
 lngCounter = lngCounter + 1 
 VolTest = lngCounter 
结束函数
  

结果：计数器不断超过5（我相信应该停在5）

方法2： Application.Volatile在单独调用的函数中，并不总是被调用

  Public Function VolTest（）As Long 
 Static lngCounter as Long 
如果lngCounter < 5然后
 lngCounter = VolTest_VolatileIncrememnt（lngCounter）
 Else 
 lngCounter = VolTest_NonVolatileIncrememnt（lngCounter）
 End If 
 VolTest = lngCounter 
 End Function 
 
公共功能VolTest_NonVolatileIncrememnt（lngCounter As Long）As Long 
 VolTest_NonVolatileIncrememnt = lngCounter + 1 
结束功能
 
公共功能VolTest_VolatileIncrememnt（lngCounter As Long）As Long 
 Application.Volatile 
 VolTest_VolatileIncrememnt = lngCounter + 1 
结束功能
  

结果：作为方法1

方法3： 传入当前单元格，如果尚未到达则设置为脏

 公共功能VolTest（rngThis as Excel.Range）As Long 
静态lngCounter长为
如果lngCounter< 5然后
 rngThis.Dirty 
结束If 
 lngCounter = lngCounter + 1 
 VolTest = lngCounter 
结束功能
  

结果：Excel挂起无限循环

我也试过跟踪rngThis，其中参数是一个字符串而不是一个范围（范围通过 Range（strThisCell）），在作为ThisWorkbook的属性存储的字典中，只有设置为脏（如果还没有函数，它打破了无限循环，但是一旦调用 rngThis.Dirty ，就会返回 #VALUE！。

解决方案

第一个代码在我的结尾工作，但你需要把计数器放在 If Statement 像这样：

 公共功能VolTest（）As Long 
静态lngCounter As Long 
如果lngCounter < 5然后
 Application.Volatile 
 lngCounter = lngCounter + 1 
 End If 
 VolTest = lngCounter 
结束函数
  

它仍然是易失性的，但是值只会变为5。如果您在顶部声明 Application.Volatile ，则再次将计数器放在 If Statement 中。 / p>

编辑1：关闭挥发性

 公共功能VolTest（）As Long 
静态lngCounter As Long 
如果lngCounter < 5然后
 Application.Volatile（True）
 lngCounter = lngCounter + 1 
 Else 
 Application.Volatile（False）
 End If 
 VolTest = lngCounter 
 MsgBoxCalled'来测试是否关闭
结束功能
  

I have a situation where I would like a function to be "partially" volatile - that is, volatile until it no longer returns an error, at which point it would no longer be calculated unless one of its arguments changed (i.e. standard non-dirty volatility).

Thus far I've tried three approaches, none of which work. Can anyone suggest anything else?

Approach 1: Application.Volatile in an IF condition

Public Function VolTest() As Long
    Static lngCounter as Long
    If lngCounter < 5 Then
        Application.Volatile
    End If
    lngCounter = lngCounter + 1
    VolTest = lngCounter
End Function

Result: Counter keeps counting beyond 5 (I believe it should stop at 5)

Approach 2: Application.Volatile in separate called function that isn't always called

Public Function VolTest() As Long
    Static lngCounter as Long
    If lngCounter < 5 Then
        lngCounter = VolTest_VolatileIncrememnt(lngCounter)
    Else
        lngCounter = VolTest_NonVolatileIncrememnt(lngCounter)
    End If
    VolTest = lngCounter
End Function

Public Function VolTest_NonVolatileIncrememnt(lngCounter As Long) As Long
    VolTest_NonVolatileIncrememnt = lngCounter + 1
End Function

Public Function VolTest_VolatileIncrememnt(lngCounter As Long) As Long
    Application.Volatile
    VolTest_VolatileIncrememnt = lngCounter + 1
End Function

Result: As approach 1

Approach 3: Pass in current cell and set dirty if not yet reached

Public Function VolTest(rngThis as Excel.Range) As Long
    Static lngCounter as Long
    If lngCounter < 5 Then
        rngThis.Dirty
    End If
    lngCounter = lngCounter + 1
    VolTest = lngCounter
End Function

Result: Excel hangs in an infinite loop

I've also tried keeping track of rngThis, where the argument is a string instead of a range (range via Range(strThisCell)), in a dictionary stored as a property of ThisWorkbook, and only setting dirty if not already in the function, which breaks the infinite loop, but also returns with #VALUE! as soon as rngThis.Dirty is called.

解决方案

The first code works at my end but you need to put the counter inside the If Statement like this:

Public Function VolTest() As Long
    Static lngCounter As Long
    If lngCounter < 5 Then
        Application.Volatile
        lngCounter = lngCounter + 1
    End If
    VolTest = lngCounter
End Function

It is still volatile but the value changes until 5 only. It is the same if you declare Application.Volatile on top but then again put the counter inside the If Statement.

Edit1: Turn Off Volatile

Public Function VolTest() As Long
    Static lngCounter As Long
    If lngCounter < 5 Then
        Application.Volatile (True)
        lngCounter = lngCounter + 1
    Else
        Application.Volatile (False)
    End If
    VolTest = lngCounter
    MsgBox "Called" ' to test if it is turned off
End Function

这篇关于VBA如何声明波动性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！