View Full Version : Problem using arbitrary column length in a user defined function

07-23-2007, 10:52 AM
Hi everyone, this is my first post. Here I am working with "Bootstrap" method, which is used to non-parametric confidence interval. To do this I need to draw samples from a population.

To do this I used "sample" function, created by Patrick Matthews. However I could not post the exact address, as I need to post at least five threads (ridiculous isnt it?) to do that.

I used that by following way:
Sample(Sheets("temp3").Range("A1:A500"), no, True, False), which is working fine.

But the problem is, my last row, here, A500 is not fixed, i.e length of column is determined by a variable say "no". Therefore I tried following code:

Sample(Sheets("temp3").Range(cells(1,1), cells(no,1))

But unfortunate it is not working.

Can anyone tell me how to solve this problem?

Your help will be highly appreciated.


Bob Phillips
07-23-2007, 10:56 AM
Create a dynamic range name.

Insert>Name>Define... with a name of rngPop, and a Refersto value of


and then use this in the function call

Sample(rngPop, no, True, False)

07-23-2007, 11:18 AM
I did following in purely VBA:

Sheets("temp3").Range(Cells(1, 1), Cells(no, 1)).Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET($A$1,,,COUNTA($A:$A),1)"

But unfortunately, it is not working. I am fetting type mismatch error.

Any solution?


Bob Phillips
07-23-2007, 11:37 AM
Why VBA. Select the sheet and do it in Excel.

07-23-2007, 11:45 AM
Yes I know definitely i can do it in excel. But this calculation is a part of some other calculations which was written in VBA. Therefore I have no other way but to do it in VBA.

Any idea how I can do it in VBA?


Bob Phillips
07-23-2007, 12:17 PM
Activeworkbook.Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET(temp3!$A$1,,,COUNTA(temp3!$A:$A),1)"

07-23-2007, 12:31 PM
Really very frustrating for me..........still not working

I did followings:

ReDim port_PL(no) As Double
For i = 1 To no
Sheets("temp3").Cells(i, 1) = ((-2 * Application.WorksheetFunction.Ln(Rnd(1))) ^ 0.5) * Cos(2 * 22 * Rnd(1) / 7)
Next i
ActiveWorkbook.Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET(temp3!$A$1,,,COUNTA(temp3!$A:$A),1)"

port_PL = Sample(rngPop, no, True, False)

However I am getting following error:
compile error:
ByRef argument type mismatch

07-23-2007, 01:12 PM
Actually it is not a problem of that function. It is the problem of assigining name to that range. When I put your formula no name is assigned to that range. However if I assign that name manually and then run that function, then it is working fine.

Therefore what will be correct way to assign a name to that range?


Bob Phillips
07-23-2007, 01:24 PM
My mistake, I din't use R1C1 notation

ActiveWorkbook.Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET(temp3!R1C1,,,COUNTA(temp3!C1),1)"

07-23-2007, 01:30 PM
DONE!!! :beerchug:

Thank you very much