Using User-Defined Functions in Cells

[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
' CORTPAC.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)]

 

[NickHK]

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

 

[Dave Peterson]

UDF's called from a worksheet cell can't change other cells. It can only return
a value to the cell with the formula in it. (With very minor exceptions.)

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
' CORTPAC.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)]

 

[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)]

 

[NickHK]

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

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)]

 

[Trent Argante]

Because you guys told me that Excel doesn't support changing other cells
& manipulating the environment through in-cell formulae, I quit pursuing
that route and solved it through detecting the change via the 'Change'
event, testing the 'Target' parameter and calling my 'PPAPNumber'
function. And, it works!

Thank you guys very much! I was banging my head against the wall.
Aaah, relief; 'tis so sweet.

Trent Argante
[DC.J(510)]