Explain the range.parent and IIF in this UDF

flora1

New Member
Joined
Nov 27, 2017
Messages
48
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?

Code:
[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]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
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

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
Re: can anyone please explain the range.parent and IIF in this UDF

Further: this line:

Code:
[COLOR=#574123]lTest = IIf(.End(xlToLeft).Column > lTest, .End(xlToLeft).Column, lTest)[/COLOR]
is just a shorter version of:

Code:
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


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

But they all have the same result...
 

flora1

New Member
Joined
Nov 27, 2017
Messages
48
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,168
Members
417,258
Latest member
amk1979

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top