Linking or autocalculation for references in VBA functions

**Mick S** · 03-21-2011, 06:31 PM

See my partial code below. I have made references to a table on a worksheet for various cost assumptions. However if I change an assumption in my table any cells where I have used my VBA function do not reflect the change in assumption. Is there any way I can build into this code a way for that to occur? or do i need to do it in its own seperate sub instead of a function?

Please Login or Register  to view this content.

**TMS** · 03-21-2011, 06:51 PM

se Named references rather than hard coding the cell references.

You can probably get away with a refernce to the first group of values and offsets for the others.

You can limit the number of string tests you do if you say, for example:

Please Login or Register  to view this content.

It would help others to help you if you posted a sample workbook with some typical data rather than just the code out of context.

Regards

**Mick S** · 03-21-2011, 07:09 PM

I would try to post a file but I'd get in big trouble if I posted our cost data externally lol.

I have already tweaked the string test with an option compare text so that mess is fixed.

I apologize since I don't think I asked my question in an easy to understand way. My problem is not the references within VBA to a cell (ie Rebates = wks.Range("e23").Value), but its in the cell where I am using my total_cost function where I have the problem.

If say theres a price of 100, landed cost of 50 and rebates from the price of 10%, my total cost is $60 and it is returned in cell Z10 (where my function is). However if I change the rebate to 20% in Range e23, my function still outputs $60 in z10 unless I hit the f2 key in z10 and then enter. I'd like cell z10 to simply change automatically if I change cell e23's value.

**TMS** · 03-21-2011, 07:15 PM

Put:

Please Login or Register  to view this content.

at the start of the function.

I think that if your function call refers to a field (cell) that changes, it will recalculate.

Regards

**shg** · 03-21-2011, 07:46 PM

Mick, you should pass all of the parameters required to compute the function's result to the function, both because Excel does not otherwise see any dependency, and because the hard-coded ranges in the function are sure to break someday.

**Mick S** · 03-21-2011, 07:48 PM

That did it. Thanks.

**Mick S** · 03-21-2011, 07:51 PM

shg -
I thought about having the parameters within the function itself but there are so many of them and they change on a 6 month basis. It is much easier to update the table in the worksheet that only I have access to (at least for me at this point) than to go back into the VBA code and do it.