Solved Combination of macro and Excel formula

November 25, 2012 at 19:49:28
Specs: Windows XP
Hi..
I want to combine macro and excel function on a spread sheet but i always have a problem if i combine macro and excel function. if i run only the macro the result is fine but adding excel formulas on the sheet the problem occurs. please see below sample.

Cell A1=Sum(C1,D1)
Cells C1 and D1 are input Values

Private Sub Worksheet_Calculate()
Set Target = Range("A1")
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("A2") = Range("A1") + Range("B1")
Range("A3") = Range("A1") - Range("B1")
End If
End Sub

in the excel sheet cell A4 = sum(A2,A3)

can any one help me with this problem?

Thanks in advance


See More: Combination of macro and Excel formula

Report •


#1
November 26, 2012 at 10:16:47
Your question is not clear.

You said "if i run only the macro the result is fine but adding excel formulas on the sheet the problem occurs"

What problem? It's kind of hard to help you if we don't know what the problem is.

There is one thing with your code that I will point out...

Look at these 2 lines:

Set Target = Range("A1")
  If Not Intersect(Target, Range("A1")) Is Nothing Then

You are setting the target as Range("A1")therefore "Not Intersect(Target, Range("A1")) Is Nothing" will always be TRUE.

That's basically the same thing as saying:

myVariable = 6
 If myVariable = 6 Then...


In fact, I'm not even sure why you are setting the Target to A1 since you don't do anything with A1 anywhere in your code.

As far as I can tell, this code will do the exact same thing as yours:

Private Sub Worksheet_Calculate()
  Range("A2") = Range("A1") + Range("B1")
  Range("A3") = Range("A1") - Range("B1")
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 26, 2012 at 17:13:39
Thank you for the reply and sorry my explanation is not clear.

I want to automatically run macro if a cell value is changed by an excel formula.
my Reference cell is A1.
Excel formula in A1 = Sum(C1,D1,)
Cell C1 And D1 are input values (actually there are more input cells, this is only my example). If i input a value on either cell C1 or D1 values of cell A1 should change and the macro should run automatically.

<prePrivate Sub Worksheet_Calculate()
Set Target = Range("A1")
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("A2") = Range("A1") + Range("B1")
Range("A3") = Range("A1") - Range("B1")
End If
End Sub>

This formula is working fine but if i add and excel formula on Cell A4 ( A4 = Sum(A2,A3 )
my computer lags and i need to press ESC to terminate the program.

Thanks Derby.


Report •

#3
November 27, 2012 at 06:42:58
✔ Best Answer
First, a posting tip:

To use the pre tags to align text in this forum, you have to use a "less than" sign before pre and a "greater than" sign after pre. The same is true for /pre.

It should look like this, but without the asterisks:

<*pre*> Your Text <*/pre*>

Second, I mentioned this in my last response, but you did not comment, so I will mention it again:

I see no reason to set the Target as A1 and then check to see if the Target cell Intersects with A1. Of course it does, because you set the Target as A1. To me, that appears to be a total waste of resources.

Please explain your reason for doing that. I'm willing to learn.

Finally, the reason your code freezes your system with =SUM(A2,A3) in C4 is because each time it places values in A2 and A3 the sheet recalculates, triggering the code to run again, which places values in A2 and A3, causing the sheet to recalculate, triggering the code to run again, and so on forever.

The way to prevent Event triggered code from continuously being triggered is to disable events while the code is running and then enable them again when it is done.

This code should work for you. You'll notice that I eliminated any use of Target because I do not believe that it is needed.

Private Sub Worksheet_Calculate()
  Application.EnableEvents = False
    Range("A2") = Range("A1") + Range("B1")
    Range("A3") = Range("A1") - Range("B1")
  Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 28, 2012 at 01:25:02
Wow this really works.. thank you verry much.
Also thanks for the tips on posting.

I'm just a week learning VBA and i found out that its pretty cool to use, so i decided to mix excel formulas and macro in my calculation sheet.

I previously use the "Change(byVal...) " thats why i misunderstood the use of "calculate ()"
and yes you're right Target cell is not need..

I'm really thankful for your help and i hope you will help me again next time..


Report •

#5
November 28, 2012 at 06:44:24
Using the Change event might be more efficient, since you can designate the specific cells that will trigger the code, e.g. C1 and D1.

When you use the Calculate event, the code will run every single time the sheet calculates, even if you don't change any cells that the code might be referencing.

I had no reason to question your use of Calculate since I don't really know what you are doing with your spreadsheet. You asked about making the Calculate code work, so that's the question I answered.

Whether you should be using the Calculate or the Change event is something I can't answer without more details, but my guess is that the Change event probably won't be triggered as often.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question