VBA - how to stop Excel from recalculating UDF's multiple times when used in namedrange/chart

[RafalKowalski]

Hi,

I have a serious problem, which I cannot solve for quite a while.


I have three UDF's:

Private Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

This function checks if something is in the array.

Private Function data_to_array(data As Range)

Dim arrArray As Variant
Dim cell As Range
Dim z As Integer

z = 0

ReDim arrArray(1 To data.Cells.Count)
For Each cell In data
z = z + 1
arrArray(z) = cell.Value
Next cell

data_to_array = arrArray
End Function

This function extracts selected range values and puts them into an array.

Private Function plot_vals(data As Variant, custom_arr As Variant)

Dim arrPlot As Variant
ReDim arrPlot(1 To UBound(data)) As Variant
Dim c As Integer
Dim cl As Integer

cl = 0

For c = 1 To UBound(data)
cl = cl + 1
If IsInArray(cl, custom_arr) Then
arrPlot(cl) = data(cl)
Else
arrPlot(cl) = CVErr(xlErrNA)
End If
Next c

plot_vals = arrPlot
End Function

The last UDF loops through the data array from second UDF and if index/position of value in data_array is in custom_array, then it returns its value. Otherwise it puts an error into an array.
Data looks like this:


These functions are used like this in Excel:
data_to_array(A1:A5) - this UDF creates an array(1 to 5) with values from cells A1:A5.
plot_vals(data_to_array(A1:A5), {1,5}) - this UDF creates an array(1 to 5), and uses second argument to retrieve first and fifth values while putting errors in the other indexes. The result is array of for example: {5,error,error,error,1}
If I used the function on above data like this: plot_vals(data_to_array(A1:A5), {1,2}) then the result would be an array {5,4,error,error,error}

That plot_vals UDF is used in named range and that named range is used to plot values on the chart. Data is stored in named range myData and the function in the second named range is used like this: plot_vals(myData,{1,5}).

Everything works, I can plot it on a chart, all is good but when the named ranges are used on charts, every time I change something in my workbook all functions are recalculated like... 10 times each one, instead of once. It causes Excel to slow down/freeze if those functions are used many times. I have tried to search about function volatility and how to turn it off (it should be turned off by default?), but nothing seems to be working and I do not know how to stop that from happening. I have tried to recreate this in Excel using standard Excel functions in named ranges, but I cannot find a correct function to do what I want. UDF is exactly what I need.

When these named ranges are NOT used in charts then nothing happens, but as soon as I use the named range on a chart then it recalculates everything on even a minor change of a workbook. Minor change meaning - copying / pasting / adding rows etc.

How can I stop that from happening? How to recalculate UDF's only once?

 

[offthelip]

Private Function data_to_array(data As Range)

Dim arrArray As Variant
Dim cell As Range
Dim z As Integer

z = 0

ReDim arrArray(1 To data.Cells.Count)
For Each cell In data
z = z + 1
arrArray(z) = cell.Value
Next cell

data_to_array = arrArray
End Function

this function is a very inefficient way of loading a range into an array.
the really quick way of of doing this is to load the range straight into the array with a statement like this:

data_to_array=range("A1:A5")

So I don't know why you are using this function at all
I think if you want to retain the function it is a one line function

data_to_array=data