0

I have run into a strange behavior when looping through a range in Excel VBA. This code was previously working, and suddenly has begun to give me errors today. I have list objects/columns that I am referencing and looping through those ranges.

Option Explicit
sub loopThroughARange()
  Dim r as range
  Debug.Print VarType(r) 'returns 9 - 'r' is an object

  For Each r In wb_in.Sheets(optionName & " Option").ListObjects("tableName").ListColumns("tableColumn").DataBodyRange
      Debug.Print r.Address 'prints out the address
      Debug.Print VarType(r) 'returns 8 - 'r' is now a string?!?
      functionWithRangeInput(r) 'throws an error
  Next r
End sub

Private Function functionWithRangeInput(r as Range)
  Debug.Print r.Address 'doesn't even get here
End Function

I don't see any issue with this code and I do not understand why my 'for' loop would suddenly convert 'r' to a string type, even though it is declared as a range and is being looped as a range.

Does anybody have any suggestions about how this transformation is occurring in the first place and how to prevent it?

BigBen
  • 46,229
  • 7
  • 24
  • 40
Andrew
  • 43
  • 1
  • 6
  • 1
    See the remarks in the [`VarType`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function) docs: If an object is passed and has a default property, VarType(object) returns the type of the object's default property. – BigBen May 17 '22 at 19:06
  • You are not assigning the return value of the function to a variable. E.g. `Dim result as String`, `result = functionWithRangeInput(r)`. Within the function, if you let it return e.g. `functionWithRangeInput = r.Address`, this works just fine, so clearly r is still accepted as a Range for the function. – ouroboros1 May 17 '22 at 19:39
  • 1
    Call your function **without** parentheses, i.e. `functionWithRangeInput r` – Spectral Instance May 17 '22 at 19:44
  • 1
    See https://stackoverflow.com/questions/5413765/what-are-the-rules-governing-usage-of-parenthesis-in-vba-function-calls and https://stackoverflow.com/questions/56692769/vba-usage-of-parentheses-for-a-method for example – Tim Williams May 17 '22 at 20:18
  • Wow, can't believe I fell for the dumbest error of all time. Yes, no parentheses (or the 'Call' function) will pass this correctly. I'm surprised I didn't get a compile error, but this fixed it. Thank you for pointing this out. – Andrew May 18 '22 at 20:36

0 Answers0