Problem with Public Function

jferri

Board Regular
Joined
Jun 24, 2003
Messages
58
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Board Regular
Joined
Jun 24, 2003
Messages
58
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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
Code:
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.
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,273
Messages
5,577,144
Members
412,769
Latest member
VK12345
Top