title: resetting variables in excel vba
body: <p>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. </p>
  
  <p>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. </p>
  
  <p>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, <code>if isempty(AltLease) = true then</code> do one thing, otherwise something else. </p>
  
  <p>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?</p>
  
  <p>Here is the code:</p>
  
  <pre><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
  </code></pre>
  
  <p>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 <code>tLeaseCurLeaseLengthAlt</code>, then code should use that, alternatively, use default value. </p>