Function reading range with formulas
 

Hi all,

I am trying to create a function that has a range as input. It copies the
range data into a local variable, does some manipulation, then outputs a
single value.

If the range I selected contains formulas, then the function is called, the
range recalculated, and the function called again, and so on until all cells
have been recalculated. The values in the local array are all empty, then
fill up one by one each time the function is executed. The range is a column
of cells of some arbitrary length, but long, like several thousand.

How can I prevent re-calculation each time so the function isn't called a
thousand times or more. I tried Application.Calculation = xlManual, but that
had no effect. Or is there a much better and simpler way? The reason I was
using a local array is because the data will be input to several
subroutines. The data is used but not changed by any code. The function is
called from the worksheet, not another macro.

TIA!

-- Dan

Here is the sample test code I am using:

Function ParseCompList2(rangea As Range)
Dim lrangea()
'Application.Calculate
'Application.Calculation = xlManual

iparts = rangea.Count

ReDim lrangea(1 To iparts)

For i = 1 To iparts
lrangea(i) = rangea(i, 1)
Next i
ParseCompList2 = lrangea(2)

'Application.Calculation = xlAutomatic

End Function

Function reading range with formulas
 

Function ParseCompList2(rangea As Range)
Dim lrangea as Variant
'Application.Calculate
'Application.Calculation = xlManual




lrangea = rangea.Resize(,1).Value

ParseCompList2 = lrangea(2,1)

'Application.Calculation = xlAutomatic

End Function

Will at least speed up picking up the array.

--
Regards,
Tom Ogilvy


"Revolvr" wrote:

Hi all,

I am trying to create a function that has a range as input. It copies the
range data into a local variable, does some manipulation, then outputs a
single value.

If the range I selected contains formulas, then the function is called, the
range recalculated, and the function called again, and so on until all cells
have been recalculated. The values in the local array are all empty, then
fill up one by one each time the function is executed. The range is a column
of cells of some arbitrary length, but long, like several thousand.

How can I prevent re-calculation each time so the function isn't called a
thousand times or more. I tried Application.Calculation = xlManual, but that
had no effect. Or is there a much better and simpler way? The reason I was
using a local array is because the data will be input to several
subroutines. The data is used but not changed by any code. The function is
called from the worksheet, not another macro.

TIA!

-- Dan

Here is the sample test code I am using:

Function ParseCompList2(rangea As Range)
Dim lrangea()
'Application.Calculate
'Application.Calculation = xlManual

iparts = rangea.Count

ReDim lrangea(1 To iparts)

For i = 1 To iparts
lrangea(i) = rangea(i, 1)
Next i
ParseCompList2 = lrangea(2)

'Application.Calculation = xlAutomatic

End Function