Function will not calculate as desired reliably.

DannyLee

New Member
Joined
Aug 7, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
This function's behavior has me stumped. I want it to run and recalculate whenever a cell in rng changes. Sometimes it does, sometimes it doesn't. It depends on how I enter a new value in rng (copy/paste, keyboard, another UDF, etc.). The result is that the function isn't updating to the most recent value in rng reliably. It displays the last (old) value until I do something manually in one of the cells in rng to kick off the macro. So, I opted for Application.Volatile to have it recalc each time anything changes in the workbook. That's not working as hoped either. If any worksheet in the workbook changes, then the worksheet that uses this function sets itself to zero in all cells that use it. If I save the workbook, or on opening it, zeros as well. F9 has no affect unless I make the worksheet that uses the function active first and press F9, then the function calculates as expected showing the most recent value. Afterward, if I make an unrelated change anywhere else in the workbook, these cells set themselves back to zero on the worksheet where the function is used.

I need it to behave like a normal Excel function. That is not setting itself to zero, and reliably running to select the last cell value in rng when a cell's value changes from zero to non-zero (which doesn't happen reliably) and presenting that newest value in Rng that changed from zero to non-zero. Note that the range can have non-contiguous sets of values, with some of the values in between remaining zero. The range of cells doesn't change once set, only the cell values are updated over time. When this UDF runs, it provides the correct result. I can't seem to make this run reliably so that it is always presenting the most recent non-zero cell value (the UBound of the below array).

All help appreciated! Thank you.

VBA Code:
Public Function LastMoneyValue(rng As Range) As Single
Dim v As Variant
Dim count As Integer
Dim i As Variant
Dim row As Long
Dim column As Long
Dim aMoneyValues() As Variant
Dim vLastMoneyValue As Variant
Dim money As Single
Dim counter As Long

Application.Volatile

count = rng.Cells.count
row = rng.row
column = rng.column

counter = column      'use counter to increment through the array. Hold the column variable constant.

'Note ranges should be an even number. In this case 26 representing 13 months of the POP.

money = count

ReDim aMoneyValues(money)

'fill the money array. Increment the starting column by 1 the first time as the money is in the adjacent cell to the hours.

For i = 0 To money - 1
    aMoneyValues(i) = Cells(row, counter).value
    counter = counter + 1
Next i

'evaluate the money array. Return the last cell in rng that has a non-zero value in it.

For i = LBound(aMoneyValues) To UBound(aMoneyValues)
    v = aMoneyValues(i)
    If v > 0 Or v < 0 Then
        vLastMoneyValue = aMoneyValues(i)
   End If
Next i

LastMoneyValue = vLastMoneyValue

End Function
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,732
Office Version
  1. 2010
Platform
  1. Windows
I think your function is not working quite the way you want it to. In detail it is accessing cells which are NOT within the range specified in rng range statement at the top.
You appear to be trying to load the values in your range into aMoneyValues however you are doing something very strange with this code:
VBA Code:
For i = 0 To money - 1
    aMoneyValues(i) = Cells(row, counter).Value
    counter = counter + 1
Next i
You loop goes from 0 to the number of rows in your range, counter starts off with the column number of the range passed to the function, and then increments once for each of the loops.
So if you select range range D2 to D27 this means the cells you load are cells D2 to AE2. Is this really what you want.
There is a very easy way to load the values of the range passed to the function, I am not quite sure what you are trying to do with your function but try modifiying something like this:
Code:
Public Function LastMoneyValue(rng As Range) As Single
Dim aMoneyValues() As Variant
Dim vLastMoneyValue As Variant
Dim v As Variant



'Note ranges should be an even number. In this case 26 representing 13 months of the POP.


aMoneyValues = rng

'fill the money array. Increment the starting column by 1 the first time as the money is in the adjacent cell to the hours.


'evaluate the money array. Return the last cell in rng that has a non-zero value in it.

For i = LBound(aMoneyValues) To UBound(aMoneyValues)
    v = aMoneyValues(i)
    If v > 0 Or v < 0 Then
        vLastMoneyValue = aMoneyValues(i)
   End If
Next i

LastMoneyValue = vLastMoneyValue

End Function
 

DannyLee

New Member
Joined
Aug 7, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you. I got her fixed with this help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,610
Messages
5,625,800
Members
416,138
Latest member
Pizzaman22

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