resetting variables in excel vba

Question

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.

Answer 1

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

Answer 2

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