0

I have a custom Excel function that is supposed to take a range and count the number of values, where some cells may contain multiple /-delimited values, each of which must be counted. Here's the function:

Option Explicit

Public Function COUNT_RACKS(rack_range As Range) As Integer
    Dim total As Integer
    Dim cell As Range
    Dim split_str As Variant
    Dim len_str As Integer
    total = 0
    For Each cell In rack_range.Cells
        If Len(cell.Value) > 0 Then
            If InStr(cell.Value, "/") <> 0 Then
                split_str = Split(cell.Value, "/")
                len_str = Len(split_str)
                total = total + len_str
            Else
                total = total + 1
            End If
        End If
    Next cell
    COUNT_RACKS = total
End Function

The trouble is, it's throwing #VALUE! errors. In stepping through the function in the debugger, it seems like there's a problem with the line len_str = Len(split_str). I say this because whenever I get to this line in the debugger, it immediately stops debugging and does, essentially, an F5. Obviously there's one or more bugs in my code, but without my debugger being willing to show me the errors, how can I debug it?

What's wrong with the code?

Scott Severance
  • 943
  • 10
  • 27
  • 2
    See here for how to the number of elements in an array: https://stackoverflow.com/questions/37124669/number-of-elements-in-a-single-dimension-variant-array-in-excel/ – tigeravatar May 03 '19 at 16:55
  • Thanks. It seems that VBA terminology is so different that I couldn't find it when searching using more normal methods. And who would have thought that the Len function would work on strings but not arrays? – Scott Severance May 03 '19 at 17:13
  • 1
    Careful with the snarks. Javascript isn't any more "normal" than VBA is. The `Len` function is found in the `VBA.Strings` module of the standad library (its members have global scope, that's why you don't need to qualify them). You can browse these modules and their members using the IDE's *Object Browser* (F2). Being a `VBA.Strings` function, makes complete sense that it works on... strings. – Mathieu Guindon May 03 '19 at 18:09

0 Answers0