Using User-Defined Functions in Cells
 

UDFs cannot manipulate the Excel environment (mostly), hence that .Select
line will fail.
Most actions do not require you to activate/select them before you use them
anyway.

NickHK

"Trent Argante"
...
In cell A4 I have the the user-defined function of
=IF(B4<"", PPAPNumber(F4),"")
The user-defined procedure "PPAPNumber", in turn, calls this:

Private Function f_GetSeqNumber(iYear As Integer) As Integer
' CORTPA:DC.J(510):20070612T2107E:20070612t2210e
Dim r As Range
Dim rngSeq As Range
Set rngSeq = Range("PPAPSeqYear")

' Find the passed year in named-range 'PPAPSeqYear'
For Each r In rngSeq.Cells
' Test for matching year
If r.Value = iYear Then
' Select cell in named-range that has matching year
'Placing the Stop command here works.
r.Select 'This line is ignored by the procedure for unknown
reason
'Placing a the Stop command here DOES NOT work! WHY???
' Assign the value of the cell, _
one column to the right of the matching cell, _
to the function
'This line is ignored by the procedure for unknown reason
f_GetSeqNumber = r.Offset(0, 1).Value
' Incrementthe value of the cell, _
one column to the right of the matching cell, _
by 1
'This line is ignored by the procedure for unknown reason
r.Offset(0, 1).Value = r.Offset(0, 1).Value + 1
Exit For
End If
Next
End Function

My dilemmas are stated in the code's comments.
TIA
Trent Argante
[DC.J(510)]

*** Sent via Developersdex http://www.developersdex.com ***

Using User-Defined Functions in Cells
 

NickHK,
It's the

f_GetSeqNumber = r.Offset(0, 1).Value
-AND-
r.Offset(0, 1).Value = r.Offset(0, 1).Value + 1

lines that I need the most.

1. Are these two lines considered as being in the "Excel environment?"

2. Can I get around this by accessing the cells via Activecell?

Trent Argante
[DC.J(510)]

*** Sent via Developersdex http://www.developersdex.com ***

Using User-Defined Functions in Cells
 

Trent,
Just delete the .Select as it achieve nothing, then this will work:
f_GetSeqNumber = r.Offset(0, 1).Value

However, you cannot change a value, so this is not possible from a UDF:
r.Offset(0, 1).Value = r.Offset(0, 1).Value + 1

NickHK

"Trent Argante"
...
NickHK,
It's the

f_GetSeqNumber = r.Offset(0, 1).Value
-AND-
r.Offset(0, 1).Value = r.Offset(0, 1).Value + 1

lines that I need the most.

1. Are these two lines considered as being in the "Excel environment?"

2. Can I get around this by accessing the cells via Activecell?

Trent Argante
[DC.J(510)]

*** Sent via Developersdex http://www.developersdex.com ***