Odd #Name? Error in Excel

[Neil4Speed]

Hey Guys,

Hope someone could help give me a bit of guidance on a spreadsheet I am working on - I have a feeling that it could be a quick fix but have been having a bit of a problem figuring it out.

I have a speadsheet running the following UDF (Kindly created for me by SHG) over many cells. The spreadsheet contains a huge amount of Data and is currently around 1GB running in Microsoft Excel 2010

Function Xing(r As Range, dMin As Double, dMax As Double) As Long
    Dim cell As Range
    Dim sSta As String
    Dim sInp As String

    For Each cell In r
        sInp = IIf(cell.Value <= dMin, "0", IIf(cell.Value >= dMax, "2", "1"))
        Select Case sSta & sInp
            Case "0":  sSta = "1"
            Case "12": sSta = "2": Xing = Xing + 1
            Case "20": sSta = "1": Xing = Xing + 1
        End Select
    Next cell
End Function

The code ran perfectly in the spreadsheet for a few days - it did take a bit of time to calculate when I added in new data (about 2 hours) (as expected).

Now however, when I run the worksheet, and enable Macros (therefore enabling UDF's) it gives me a #Name? error. I checked and the module is still there, and it recognizes it (ie. if I start typing in "=xing" it comes up in the list), however still gives me the #Name? Error.

Anyone had a problem like this before where Excel all of a sudden wouldn't allow the UDF to run?

Any assistance would be super appreciated.

Thanks!!

[shg]

The code is in a code module, not a Sheet module or ThisWorkbook?

[Cheeky Charlie]

it did take a bit of time to calculate when I added in new data (about 2 hours) (as expected).

That's because iifs are slow and rubbish, looking at you, shg

You can also get significant speed improvements by readings large data sets into arrays then manipulating the data in the array - xing2, below, runs about 11 times faster than the original on my laptop, and xing3 (if you can guarantee the data set starts at or below dMin) about 1.2 times faster again - 13.5 times faster than original. Give it a go.

Function xIng2(rIn As Range, dMin As Double, dMax As Double) As Long

    Dim vRin
    Dim lArrIndex As Long
    Dim sState As String
    
    vRin = rIn.Value
    
    For lArrIndex = LBound(vRin) To UBound(vRin)
        Select Case vRin(lArrIndex, 1)
        Case Is <= dMin
            If sState = "Over" Then xIng2 = xIng2 + 1
            sState = "Under"
        Case Is >= dMax
            If sState = "Under" Then xIng2 = xIng2 + 1
            sState = "Over"
        End Select
    Next lArrIndex

End Function

Function xIng3(rIn As Range, dMin As Double, dMax As Double) As Long

    Dim vRin
    Dim lArrIndex As Long
    Dim sState As String
    
    vRin = rIn.Value
    sState = "Under"
    
    For lArrIndex = LBound(vRin) To UBound(vRin)
        Select Case vRin(lArrIndex, 1)
        Case Is <= dMin
            If sState = "Over" Then xIng3 = xIng3 + 1: sState = "Under"
        Case Is >= dMax
            If sState = "Under" Then xIng3 = xIng3 + 1: sState = "Over"
        End Select
    Next lArrIndex

End Function

NB, I hope this helps, but this is mainly about me learning and experimenting with calculation speed, shg's solution is (of course) perfectly good.

[shg]

I think CC's suggestion to read the data into memory is good if you're referencing a lot of data (as you apparently are).

Function Transitions(r As Range, _
                     dMin As Double, dMax As Double, _
                     Optional iOpt As Long = 0) As Variant
    ' UDF wrapper for nTransitions

    If r.Columns.Count > 1 And r.Rows.Count > 1 Or _
       dMin >= dMax Then
        Transitions = CVErr(xlErrNum)
    Else
        Transitions = nTransitions(r.Value2, dMin, dMax, iOpt)
    End If
End Function

Function nTransitions(av As Variant, _
                      dMin As Double, dMax As Double, _
                      Optional iOpt As Long = 0) As Long
    ' shg 2010
    ' VBA only
    
    ' Counts transitions in r between dMin and dMax
    '   iOpt < 0 => count negative transitions only (dMax to dMin)
    '   iOpt = 0 => count transitions in either direction
    '   iOpt > 0 => count positive transitions only (dMin to dMax)

    Dim iPos        As Long
    Dim iNeg        As Long

    Dim v           As Variant
    Dim iOld        As Long

    If dMin >= dMax Then Exit Function
    
    iPos = -(Sgn(iOpt) >= 0)
    iNeg = -(Sgn(iOpt) <= 0)

    For Each v In av
        If VarType(v) = vbDouble Then
            If v <= dMin Then
                If iOld = 1 Then nTransitions = nTransitions + iNeg
                iOld = -1
            ElseIf v >= dMax Then
                If iOld = -1 Then nTransitions = nTransitions + iPos
                iOld = 1
            End If
        End If
    Next v
End Function

[Neil4Speed]

Thanks for the help SHG - I am actually pretty embarrassed to admit this but the problem was that in the 50,000 cells of data that I added - there was one cell, one measly cell, that contained a "?" - for some bizarre reason and decided to conk out on me. When I revised it - it worked perfectly.

Regarding the code - that looks positively epic! I assumed that it would be possible to speed up the calculation especially over so much data, I just didn't know how!

Please forgive me as I am a total noob, but how do I substitute your new code it into my worksheet to replace the Xing UDF?

[shg]

Copy the code from the post and paste it over the top of the Xings function in your workbook.

Then do find and replace, look in: Formulas, Find: Xings( Replace with: Transitions(