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

RafalKowalski

New Member
Joined
May 30, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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


I have three UDF's:

VBA Code:
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.

VBA Code:
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.

VBA Code:
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:
enter image description here

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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,644
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
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:
Code:
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
Code:
data_to_array=data
 

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,118
Members
414,043
Latest member
thomas Stein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top