Function Array Running Repetitively

[JTBorton]

Excel 2007.
I'm writing a quick function to take random samples from a population range. The sample size is determined by the number of cells selected by the user by inputting an array formula (the user selects the range they want the output in, types the formula and presses Shift+Ctrl+Enter).

The code runs fine, but if I add a second sample range, the first sample range runs again and selects a new samples points. If I add a third sample range, the first two re-run, and so on. I modified the code to insert a static variable that counts the number of times the function has run, which is placed in the last row of the calling selection range.

The population I'm using to test this is a few hundred cells with the formula "=RANDBETWEEN(1,500)"

Option Explicit
Option Base 1
Public Function RandomSample(rngPopulation As Range) As Variant
    
    Static intFunctCount    As Integer 'Counts the number of times the function has been called.
    Dim intRows             As Integer 'The number of rows selected by the user for output.
    Dim intCols             As Integer 'The number of columns selected by the user for output.
    Dim intArraySize        As Integer 'The number of cells in the population.
    Dim N                   As Integer 'A counter
    Dim M                   As Integer 'A counter
    Dim vntSamples()        As Variant 'The array of samples being collected
        
    intArraySize = rngPopulation.Count
    
    If intArraySize Then
        
        intFunctCount = intFunctCount + 1
        
        With Application.Caller
            intRows = .Rows.Count
            intCols = .Columns.Count
        End With
        
        ReDim vntSamples(1 To intRows, 1 To intCols)
        
        For M = 1 To intRows - 1
            For N = 1 To intCols
                vntSamples(M, N) = rngPopulation.Item(Round(Rnd * intArraySize, 0))
            Next N
        Next M
        
    End If
    
    vntSamples(intRows, 1) = intFunctCount
    RandomSample = vntSamples

End Function

Why does the code re-run every time I set up a new population sample? How can I stop this?


-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Hi,

The code runs fine, but if I add a second sample range...

Could you explain what you mean by that? There is no means to "add a second range" or am I missing something?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

In other words I am collecting multiple sample populations by adding a second range to the spreadsheet that calls the function.

The first call may come from cells B2:C4, the second time the function is called may be from cells B6:E5, etc.

Each time I call the function to collect a new sample population (to be displayed in a different range from the previous), all preceding ranges are recalculated.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[PHV]

You may try this:
Application.Volatile False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

PHV,

I'm reluctant to use the volatile method because there may be other functions that I do want to recalculate as other cells change. Is there a way to disable volatility for only a single function?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]