You are here:

Excel/change by val target

Advertisement


Question
QUESTION: Hi
I am using excel 2003 using VBA.

In sheet1 a third party program is feeding a value into A1.

I would like to store the current A1.value in B1 so when the A1.value changes I can tell if the  B2.value > or < A2.value and store the difference in C2.

I believe this has to be a private sub but I'm not sure.

Kind regards,

ANSWER: Hi Dave,

I'm a bit confused.  I'm wondering if you meant to say "... if the B1.value > or < A1.value ..." in your problem statement.

Damon

---------- FOLLOW-UP ----------

QUESTION: Yes,

Maybe this is simpler.

The third party software stores a value in A1. I would like to know when that value is changed again (by the third party software) whether it is greater or less than the previous value in A1 and by how much.

Hope that helps.

ANSWER: Hi Dave,

Thank you, that helps.  Here is some code that should do it.  I assumed as you stated previously that the value you want to monitor is in A1 of sheet Sheet1 and the difference should be stored in C2, but I think you can see where to change these cell designations in the code if you need to.

Firstly, this code should go in the worksheet's event code module.  To do this, right-click on the worksheet's tab and select View Code.  Then paste this code in the Code pane.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$1" Then
     If Target.Value <> ThisWorkbook.PreviousValue Then
        Application.EnableEvents = False
        Range("C2") = Target.Value - ThisWorkbook.PreviousValue
        ThisWorkbook.PreviousValue = Target
        Application.EnableEvents = True
     End If
  End If
End Sub

Finally paste the code below into the Workbook's event code module.  To do this, while still in the Visual Basic Editor type Ctrl-r (to bring up the Project pane) and double-click the ThisWorkbook object icon.  Then paste this code into the Code window.

Public PreviousValue       As Double

Private Sub Workbook_Open()
  PreviousValue = Worksheets("Sheet1").Range("A1").Value
End Sub

Let me know if any problem.

Damon

---------- FOLLOW-UP ----------

QUESTION: Your code works great when I enter in a number manually but does not work when the third party pushes in the value. Is their API doing something different. Is there a way to compensate without seeing their code? I know I am not giving you much to go on but I thought I would ask.

dave

Answer
Hi again Dave,

Yes, some functions that push data to worksheets do not trigger the worksheet change event.  Let's hope that yours does trigger the worksheet Calculate event when A1 changes.  Then the following code should work.  This code should replace the code I provided before for the worksheet.  The code in the ThisWorkbook code module should remain.

Private Sub Worksheet_Calculate()
  If Range("A1").Value <> ThisWorkbook.PreviousValue Then
     Application.EnableEvents = False
     Range("C2") = Range("A1").Value - ThisWorkbook.PreviousValue
     ThisWorkbook.PreviousValue = Range("A1")
     Application.EnableEvents = True
  End If
End Sub

This method requires that you have a cell or cells on you worksheet that are dependent on the value in A1.  If you don't, simply add the formula =A1 to any cell on your worksheet to create such a dependency.

This method is in general not quite as efficient as the one based on worksheet Change because it runs whenever the worksheet calculates for any reason, not just cell A1 changing.  But in this case I suspect the difference is undetectable.

Let me know if this solves the problem.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.