Problem with Public Function

[jferri]

Love this forum. Quick question guys. I am writing a public function, and I can't figure out how to select the approprate cell to start the loop. - i.e., when i step through the code, it looks like cell A2 in the "Options" worksheet is not being selected.

Any ideas? Thanks!



Public Function optionStatus(symbol As Variant, thedate As Date)

Dim statusFlag As Variant

statusFlag = 1
Sheets("Options").Select
Range("A2").Select

Do
If ActiveCell.Value = symbol Then
If thedate > ActiveCell.Offset(0, 1) And thedate <= ActiveCell.Offset(0, 2) Then
statusFlag = 0
ActiveCell.Offset(1, 0).Select
Else:
End If
Else: ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""

optionStatus = statusFlag

End Function

 

[tusharm]

A function (and I assume you mean you intend using this as a function in the worksheet) cannot change anything in the worksheet. All it can do is return values that are shown in the cells in which the function is entered.

Also, it is very bad design to refer to cells in the function that are not passed to it through the argument list.

 

[jferri]

Tushar,

Thanks. You are correct, I am looking to use this as a function in a workbook. Is there an appropriate way to itterate through data in a function, as i am only looking to reference data, not change it.

 

[tusharm]

Don't select/activate anything in a function and don't refer to a cell that is not directly passed as an argument to the function. In addition, there are some capabilities that commonsense would indicate change nothing in the worksheet but XL still doesn't allow in a UDF. I believe Range.Find is one of those methods.

As a first pass

Public Function optionStatus(SearchRng As Range, _
        symbol As Variant, thedate As Date)
    Dim statusFlag As Byte, I As Long
    statusFlag = 1
    I = 1
    Do
        If SearchRng.Cells(I, 1).Value = symbol Then
            If thedate > SearchRng.Cells(I, 2) _
                    And thedate <= SearchRng.Cells(I, 3) Then
                statusFlag = 0
                End If
            End If
        I = I + 1
        Loop Until statusFlag = 0 Or I > SearchRng.Rows.Count _
            Or SearchRng.Cells(I, 1).Value = ""
    
    optionStatus = statusFlag
    
    End Function

You can streamline it further with something like the below but that has nothing to do with writing a proper UDF. It simply illustrates writing more obvious code.

Public Function optionStatus2(SearchRng As Range, _
        symbol As Variant, thedate As Date)
    Dim ItemFound As Boolean, I As Long
    ItemFound = False
    I = 1
    Do
        ItemFound = SearchRng.Cells(I, 1).Value = symbol _
            And thedate > SearchRng.Cells(I, 2) _
            And thedate <= SearchRng.Cells(I, 3)
        I = I + 1
        Loop Until ItemFound Or I > SearchRng.Rows.Count _
            Or SearchRng.Cells(I, 1).Value = ""
    
    optionStatus = IIf(ItemFound, 0, 1)
    
    End Function

 

[jferri]

Tushar - You are an Excel god, thanks!

 

[tusharm]

You are welcome.