View Full Version : Solved: VBA Function to Automatically Update

aerodoc

10-27-2010, 05:44 PM

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

Blade Hunter

10-27-2010, 06:25 PM

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

10-27-2010, 06:55 PM

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).

Blade Hunter

10-27-2010, 07:24 PM

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

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

aerodoc

10-27-2010, 07:29 PM

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

10-27-2010, 07:34 PM

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:

Range("A9,A10,B16").Calculate

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

aerodoc

10-27-2010, 08:03 PM

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

10-27-2010, 09:19 PM

I am wondering about this approach by Chip Pearson:

http://www.eggheadcafe.com/software/aspnet/31443314/vba-function-wont-update-in-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

10-27-2010, 09:56 PM

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

10-27-2010, 10:05 PM

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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.