Explanation about a specific function

[Edmond]

Hi Everyone,

My issue here is more about understanding. I am using that formula that I have modified:


Function RandCell(Rg As Range) As Range

Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)

End Function


Sub RandCellTest()
Dim Counter As Long
Dim TargetRg As Range, Cell As Range
Dim LastRow As Integer
Dim ws As Worksheet


Set ws = Worksheets("Sheet1")
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set TargetRg = Range(Cells(2, 1), Cells(LastRow - 1, 1))






For Counter = 1 To 50
Set Cell = RandCell(TargetRg)
Range(Cell, Cell.Offset(, 3)).Select

Next


End Sub

It works well. However I have some trouble figuring the logic behind the way is written the function.

1st: Why do we have to put this rg (rg.cells)? Plus we don't define it. Is it related to the VBA Function?
2nd: How Int(Rnd * Rg.Cells.Count) gives a number?

It seems obvious but I can't make it...

Thanks in advance for your help!

[Paul_Hossler]

1st: Why do we have to put this rg(rg.cells)? Plus we don't define it. Is it related to the VBA Function?


2nd: How Int(Rnd * Rg.Cells.Count) gives a number?






Function RandCell(Rg As Range) As Range

Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)

End Function



1. RandCell is the name of the function that takes a range (Rg) as input so it is defined with the Rg as Range in the function definition

2. Work from the inside out

Assume Rg is A1:A100.

So the .Count of the .Cells in Rg = 100 cells

Rnd returns a random floating point (aka Double) number between 0 and 1, say .356666666666

So .356666666666 times 100 = 35.6666666666



Int means to take the integer (whole number) part, or just 35

Rg.Cell(35) means to get the 35th cell in Rg, or A35

and Set the returned value of RandCell to that cell as a Range object

So in this case,

RandCell (Range("A1:A100)).Address = "A35"

and


RandCell (Range("A1:A100)).Value = 12345.6789 (if 12345.6789 is in A35)


Each calculation of the worksheet has the potential to recalculate the function and return a different cell

[Edmond]

Hi Paul,

No the first time you've helped me figuring out what I didn't understand.

I didn't know that "Cell" could contain only one parameter. It disturbed me.

Anyway, thank you so much, very clear now :)