Explain the range.parent and IIF in this UDF

[flora1]

hello, i found this code somewhere written by jon peltier

i am curious to learn, what is the role .Parent here what exactly parent do here. if it was used without .parent wouldn't it work?

also my second question, why IIF with double i is used here. what is the difference between if with one i and with double ii

wouldn't this work if it was if with single i?

[COLOR=#333333][FONT=inherit]Function LastCol(rTest As Range) As Long[/FONT][/COLOR]  Dim lTest As Long
  Dim iRow As Range
  For Each iRow In rTest.Rows
    With rTest.Parent.Cells(iRow.Row, "IV")
      lTest = IIf(.End(xlToLeft).Column > lTest, .End(xlToLeft).Column, lTest)
    End With
  Next
  LastCol = lTest [COLOR=#333333][FONT=inherit]End Function[/FONT][/COLOR]

 

[gallen]

Re: can anyone please explain the range.parent and IIF in this UDF

Parent just returns the Parent object of the object in question. Just another way of qualifying which cells you are dealing with. So in this case the 'Parent' would be the worksheet that contains rTest...

As for IIF it is just a handy function that evaluates an expression and returns what you need dependant on if the expression is true or false

More about that here

 

[gallen]

Re: can anyone please explain the range.parent and IIF in this UDF

Further: this line:

[COLOR=#574123]lTest = IIf(.End(xlToLeft).Column > lTest, .End(xlToLeft).Column, lTest)[/COLOR]

is just a shorter version of:

If .End(xlToLeft).Column > lTest Then
    lTest = .End(xlToLeft).Column
Else
    lTest = lTest
End If

Which is a little confusing as lTest only changes if: ".End(xlToLeft).Column > lTest"

So I'd have written it as

If .End(xlToLeft).Column > lTest Then lTest = .End(xlToLeft).Column

But they all have the same result...

 

[flora1]

Re: can anyone please explain the range.parent and IIF in this UDF

gallen,

this is excellent explanation. thank you so much. it could not have explained better.