找到你要的答案

Q:resetting variables in excel vba

Q:重置变量在Excel VBA

I am trying to build a model with VBA doing some work for me. Assume I have 4 variables - unit, lease start date, lease p.a, and alternative lease p.a. There are more, but that does not matter for my problem. VBA loops through each line and gets the value for unit i in respective column.

So, logically, I could declare unit as string, lease start date as date, lease p.a as single, and alternative lease p.a. as single. The problem I have is that I need to distinguish between empty entry and 0. The default numerical value is going to be 0. the distinction between 0 and empty is crucial. The only way I found to get around this is to declare everything as Variant and then check if the corresponding range is empty. If it is empty, then lease the Variant default value (Empty), otherwise assign the value.

I have a feeling that this is going to seriously affect my code performance. Ultimately, there will be lots of variables and I want to refer to those variables in the code. Like, if isempty(AltLease) = true then do one thing, otherwise something else.

I also find that I can not empty single or date variables(date is actually not a problem, since it drops to 1900). Can anyone suggest something?

Here is the code:

Dim tUnitName As Variant
Dim tNumberOfUnits As Variant
Dim tLeaseCurLeaseLengthDef as Variant
Dim tLeaseCurLeaseLengthAlt as Variant


Sub tenancyScheduleNew()
Dim lRow As Long
Dim i As Long
lRow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To lRow
    reAssignVariables i
Next i
End Sub

Sub reAssignVariables(i As Long)
tAssetName = checkIfEmpty(i, getColumn("Sheet4", "tAssetName", 3))
tNumberOfUnits = checkIfEmpty(i, getColumn("Sheet4", "tNumberOfUnits", 3))
tLeaseCurLeaseLengthDef = checkIfEmpty(i, getColumn("Sheet4", "tLeaseCurLeaseLengthDef", 3))
tLeaseCurLeaseLengthDef = checkIfEmpty(i, getColumn("Sheet4", "tLeaseCurLeaseLengthAlt", 3))

End Sub

Function getColumn(sh As String, wh As String, colNo As Long) As Long
Dim refSheet As Worksheet
Dim rFound As Range
Set refSheet = Sheets(sh)
With refSheet
    Set rFound = .Columns(1).Find(What:=wh, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
    On Error GoTo 0
    If Not rFound Is Nothing Then
        getColumn = rFound.Offset(0, colNo - 1).Value
    Else
    End If
End With

End Function

This is the way I am doing it now, which I think will slow the performance down. This is only small part of variables that I have made - there is going to be much more. I just need to understand how to construct it correctly in the first place. More specifically, if there is a value in tLeaseCurLeaseLengthAlt, then code should use that, alternatively, use default value.

我试图建立与VBA为我做一些工作模型。假设我有4个变量的单位,租赁开始日期、租赁和租赁P.a,每年有更多的选择,但这并不为我的问题的问题。VBA循环通过每个线得到单位值我在各自的列。

所以,从逻辑上讲,我可以申报单位为字符串,租赁开始日租赁日期,每年单,和其他租赁每年单。我的问题是,我需要区分空条目和0。默认数值为0。0和空的区别是至关重要的。我发现绕过这个的唯一方法是声明所有的变量,然后检查相应的范围是否为空。如果它是空的,则租用变量默认值(空),否则指定值。

我觉得这会严重影响我的代码性能。最终,会有很多变量,我想引用代码中的变量。喜欢,如果是空的(altlease)=真的再做一件事,否则别的东西。

我还发现,我不能清空单个或日期变量(日期实际上不是一个问题,因为它下降到1900)。有人建议什么吗?

这里是代码:

Dim tUnitName As Variant
Dim tNumberOfUnits As Variant
Dim tLeaseCurLeaseLengthDef as Variant
Dim tLeaseCurLeaseLengthAlt as Variant


Sub tenancyScheduleNew()
Dim lRow As Long
Dim i As Long
lRow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To lRow
    reAssignVariables i
Next i
End Sub

Sub reAssignVariables(i As Long)
tAssetName = checkIfEmpty(i, getColumn("Sheet4", "tAssetName", 3))
tNumberOfUnits = checkIfEmpty(i, getColumn("Sheet4", "tNumberOfUnits", 3))
tLeaseCurLeaseLengthDef = checkIfEmpty(i, getColumn("Sheet4", "tLeaseCurLeaseLengthDef", 3))
tLeaseCurLeaseLengthDef = checkIfEmpty(i, getColumn("Sheet4", "tLeaseCurLeaseLengthAlt", 3))

End Sub

Function getColumn(sh As String, wh As String, colNo As Long) As Long
Dim refSheet As Worksheet
Dim rFound As Range
Set refSheet = Sheets(sh)
With refSheet
    Set rFound = .Columns(1).Find(What:=wh, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
    On Error GoTo 0
    If Not rFound Is Nothing Then
        getColumn = rFound.Offset(0, colNo - 1).Value
    Else
    End If
End With

End Function

这就是我现在这样做的方式,我认为这会减慢性能。这只是我所做的变量中的一小部分--会有更多的变数。我只需要知道如何正确地构建它。更具体地说,如果有tleasecurleaselengthalt值,然后代码应该使用,另外,使用默认值。

answer1: 回答1:

You can't empty a variable of type integer, since empty is not an integer. If you have a variant variable which is currently of subtype integer you can reset it to empty:

Sub test()
    Dim v As Variant
    Debug.Print TypeName(v)
    v = 1
    Debug.Print TypeName(v)
    v = Empty
    Debug.Print TypeName(v)
End Sub

output:

Empty
Integer
Empty

Also, the performance hit of using variants might not be as great as you fear. An informal test:

Sub InformalTest(n As Long)
    Dim i As Long, sum1 As Double
    Dim j As Variant, sum2 As Variant
    Dim start As Double, elapsed1 As Double, elapsed2 As Double

    start = Timer
    For i = 1 To n
        sum1 = sum1 + 1#
    Next i
    elapsed1 = Timer - start

    start = Timer
    For j = 1 To n
        sum2 = sum2 + 1#
    Next j
    elapsed2 = Timer - start

    Debug.Print "Nonvariant time: " & elapsed1 & ", Nonvariant sum: " & sum1
    Debug.Print "Variant time: " & elapsed2 & ", Variant sum: " & sum2
End Sub

Sample output:

InformalTest 1000000
Nonvariant time: 0.060546875, Nonvariant sum: 1000000
Variant time: 0.099609375, Variant sum: 1000000

InformalTest 10000000
Nonvariant time: 0.521484375, Nonvariant sum: 10000000
Variant time: 0.599609375, Variant sum: 10000000

不能清空类型整数的变量,因为空不是整数。如果你有一个变量变量当前是子类型的整数你可以重置为空:

Sub test()
    Dim v As Variant
    Debug.Print TypeName(v)
    v = 1
    Debug.Print TypeName(v)
    v = Empty
    Debug.Print TypeName(v)
End Sub

输出:

Empty
Integer
Empty

此外,使用变种的性能打击可能没有你担心的那么大。非正式测试:

Sub InformalTest(n As Long)
    Dim i As Long, sum1 As Double
    Dim j As Variant, sum2 As Variant
    Dim start As Double, elapsed1 As Double, elapsed2 As Double

    start = Timer
    For i = 1 To n
        sum1 = sum1 + 1#
    Next i
    elapsed1 = Timer - start

    start = Timer
    For j = 1 To n
        sum2 = sum2 + 1#
    Next j
    elapsed2 = Timer - start

    Debug.Print "Nonvariant time: " & elapsed1 & ", Nonvariant sum: " & sum1
    Debug.Print "Variant time: " & elapsed2 & ", Variant sum: " & sum2
End Sub

Sample 输出:

InformalTest 1000000
Nonvariant time: 0.060546875, Nonvariant sum: 1000000
Variant time: 0.099609375, Variant sum: 1000000

InformalTest 10000000
Nonvariant time: 0.521484375, Nonvariant sum: 10000000
Variant time: 0.599609375, Variant sum: 10000000
answer2: 回答2:

Maybe you could create your own classes? Example for Single.

Class Single2

Private m_value As Single
Private m_hasValue As Boolean

Public Property Let Initialize(ByVal source As Range)
    m_hasValue = False
    m_value = 0

    If source Is Nothing Then _
        Exit Property

    ' add any checks you need to recognize the source cell as non-empty
    ' ... to distinguish between empty entry and 0
    If Trim(source.Value) = "" Then _
        Exit Property

    If Not IsNumeric(source.Value) Then _
        Exit Property

    m_value = CSng(source.Value)
    m_hasValue = True

End Property

Public Property Get Value() As Single
    Value = m_value
End Property

Public Property Get HasValue() As Boolean
    HasValue = m_hasValue
End Property

And use the class like this:

Module:

Dim lease As Single2
Set lease = New Single2
lease.Initialize = Range("a1")

If lease.HasValue Then
    Debug.Print "lease has value ... " & lease.Value
Else
    Debug.Print "lease hasn't value ... "
End If

也许你可以创建自己的类?单例。

等级2

Private m_value As Single
Private m_hasValue As Boolean

Public Property Let Initialize(ByVal source As Range)
    m_hasValue = False
    m_value = 0

    If source Is Nothing Then _
        Exit Property

    ' add any checks you need to recognize the source cell as non-empty
    ' ... to distinguish between empty entry and 0
    If Trim(source.Value) = "" Then _
        Exit Property

    If Not IsNumeric(source.Value) Then _
        Exit Property

    m_value = CSng(source.Value)
    m_hasValue = True

End Property

Public Property Get Value() As Single
    Value = m_value
End Property

Public Property Get HasValue() As Boolean
    HasValue = m_hasValue
End Property

使用像这样的类:

模块:

Dim lease As Single2
Set lease = New Single2
lease.Initialize = Range("a1")

If lease.HasValue Then
    Debug.Print "lease has value ... " & lease.Value
Else
    Debug.Print "lease hasn't value ... "
End If
excel  vba  variables