Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Functions | Excel Discussion (Misc queries) | |||
User Defined Functions - Help Text - Make it Easy for the User | Excel Programming | |||
User defined functions | Excel Programming | |||
User Defined Functions | Excel Programming | |||
excel functions and User defined functions | Excel Programming |