Solved: VBA Function to Automatically Update

[aerodoc]

The following function will not update when I change the cells with the name reference "e_allow_1" and the offsets shown below. The function does not call them directly. Would I need to put them in as function variables? (i.e. Function max(mat,e1,e1t_1,e1c_1,e2t_1,e2c_1,e6s)? This would really be a mess though.

[VBA]Function max(mat, e1)

e1t_1 = Range("e_allow_1").Offset(0, 0).Value
e1c_1 = Range("e_allow_1").Offset(1, 0).Value
e2t_1 = Range("e_allow_1").Offset(2, 0).Value
e2c_1 = Range("e_allow_1").Offset(3, 0).Value
e6s_1 = Range("e_allow_1").Offset(4, 0).Value

If mat = 1 And (e1 > 0) Then
max = e1t_1 / e1 - 1
ElseIf mat = 1 And (e1 = 0) Then
max = 999
ElseIf mat = 1 And (e1 < 0) Then
max = e1c_1 / e1 - 1

ElseIf mat = 2 And (e1 > 0) Then
max = e1t_2 / e1 - 1
ElseIf mat = 2 And (e1 = 0) Then
max = 999
ElseIf mat = 2 And (e1 < 0) Then
max = e1c_2 / e1 - 1

ElseIf mat = 3 And (e1 > 0) Then
max = e1t_3 / e1 - 1
ElseIf mat = 3 And (e1 = 0) Then
max = 999
ElseIf mat = 3 And (e1 < 0) Then
max = e1c_3 / e1 - 1
Else
max = " "
End If

End Function[/VBA]

[Blade Hunter]

The following function will not update when I change the cells with the name reference "e_allow_1" and the offsets shown below. The function does not call them directly. Would I need to put them in as function variables? (i.e. Function max(mat,e1,e1t_1,e1c_1,e2t_1,e2c_1,e6s)? This would really be a mess though.

Function max(mat, e1)

e1t_1 = Range("e_allow_1").Offset(0, 0).Value
e1c_1 = Range("e_allow_1").Offset(1, 0).Value
e2t_1 = Range("e_allow_1").Offset(2, 0).Value
e2c_1 = Range("e_allow_1").Offset(3, 0).Value
e6s_1 = Range("e_allow_1").Offset(4, 0).Value

If mat = 1 And (e1 > 0) Then
max = e1t_1 / e1 - 1
ElseIf mat = 1 And (e1 = 0) Then
max = 999
ElseIf mat = 1 And (e1 < 0) Then
max = e1c_1 / e1 - 1

ElseIf mat = 2 And (e1 > 0) Then
max = e1t_2 / e1 - 1
ElseIf mat = 2 And (e1 = 0) Then
max = 999
ElseIf mat = 2 And (e1 < 0) Then
max = e1c_2 / e1 - 1

ElseIf mat = 3 And (e1 > 0) Then
max = e1t_3 / e1 - 1
ElseIf mat = 3 And (e1 = 0) Then
max = 999
ElseIf mat = 3 And (e1 < 0) Then
max = e1c_3 / e1 - 1
Else
max = " "
End If

End Function

I don't see where you are setting a value for the following:

e1t_2
e1c_2
e1t_3
e1c_3


Also, it looks overly complicated for what you are trying to do, a well placed loop in there would make things much easier to follow.

[aerodoc]

I can't disagree that a loop would be a good idea. For the time being, let me scale it down and determine what can be done with the automatic update. How about this.

[VBA]Function max(e1)

e1t_1 = Range("e_allow_1").Offset(0, 0).Value
e1c_1 = Range("e_allow_1").Offset(1, 0).Value

If (e1 > 0) Then
max = e1t_1 / e1 - 1
ElseIf (e1 = 0) Then
max = 999
ElseIf (e1 < 0) Then
max = e1c_1 / e1 - 1

End Function
[/VBA]
How can you get the function to update when "e_allow_1" changes, but keep in mind that this is a condensed version and the actual version will have more variables (i.e. an elegant solution would be good).

[Blade Hunter]

I can't disagree that a loop would be a good idea. For the time being, let me scale it down and determine what can be done with the automatic update. How about this.

Function max(e1)

e1t_1 = Range("e_allow_1").Offset(0, 0).Value
e1c_1 = Range("e_allow_1").Offset(1, 0).Value

If (e1 > 0) Then
max = e1t_1 / e1 - 1
ElseIf (e1 = 0) Then
max = 999
ElseIf (e1 < 0) Then
max = e1c_1 / e1 - 1

End Function

How can you get the function to update when "e_allow_1" changes, but keep in mind that this is a condensed version and the actual version will have more variables (i.e. an elegant solution would be good).

Make it volatile

[vba]
Function max(e1)
Application.Volatile
e1t_1 = Range("e_allow_1").Offset(0, 0).Value
e1c_1 = Range("e_allow_1").Offset(1, 0).Value

If (e1 > 0) Then
max = e1t_1 / e1 - 1 'Divide by zero error when e1 = 1
ElseIf (e1 = 0) Then
max = 999
ElseIf (e1 < 0) Then
max = e1c_1 / e1 - 1

End Function

[/vba]

[aerodoc]

Well, that is certainly an option. But won't that be computationally expensive? Is there not a more elegant solution than the brute force approach?

[Blade Hunter]

You could have a macro set at the worksheet level on the worksheet_change event which would recalculate only the cells you are interested in like so:

[vba]
Range("A9,A10,B16").Calculate
[/vba]

This way you can query Target.Address in an if statement and have complete control over when and when not to calculate.

[aerodoc]

Thanks, that is another option. I remember having issues with the worksheet_change event approach before. I forget exactly what it was, but it does not always actually update at times when you think it should. It was something like a copy/paste in certain versions. Since then I have stayed away from that approach. I will look into it a littler further.

[aerodoc]

I am wondering about this approach by Chip Pearson:

http://www.eggheadcafe.com/software/...worksheet.aspx

But it looks like I would have to pass the variables into the function? If so, that would not be very practical.

[Blade Hunter]

Your call really, I think between the ideas above and Chips post that pretty much shows the options you have to pick from . I am a long running fan of Chip's solutions though I must say

[aerodoc]

Just to be clear, since I am still learning, is Chip's solution what I think it is? It would appear that I would need to pass each of the variables in the function to force it to "look" for updates. From my example on the first post, there would be quite a few and it would probably defeat the purpose of using a function in the first place. I just want to make sure I am interpreting it properly before I go do a certain path.

Thanks for the help, there is enough here to work from for sure.