Hi,
I am looking to create a simple VBA macro for Excel but have no programming skills beyond simple formulae.
I have a spreadsheet that receives measurement data from an external source. This data populates column A and automatically updates every second by adding the latest value to the next row in column A. All data previously populated remains unchanged.
The macro needs to look for 2 scenarios and return a flag (different for each scenario). Full details are set out below.
James
## Deliverables
Hi,
I am looking to create a simple VBA macro for Excel but have no programming skills beyond simple formulae.
I have a spreadsheet that receives measurement data from an external source. This data populates column A and automatically updates every second by adding the latest value to the next row in column A. All data previously populated remains unchanged.
The macro needs to look for 2 scenarios and return a flag (different for each scenario):
The first scenario is that the latest measurement (+/- 1 increment) (called the "Latest Value") equals the lowest measurement (called the "Trigger Value") in the preceding X number of values (called the "Lookback Period") and in between these two measurements the measurements had increased to X increments above (called the "Critical Level") the Trigger Value before hitting the same level again at the Latest Value. The macro should also check that the subsequent X values (known as the "Following Period") increase more than X increments (known as the "Danger Level") from the Latest Value before returning the flag. The flag can be as simple as returning a "1" in a cell.
The second scenario is a mirror image of the first scenario. The latest measurement (+/- 1 increment) (ie the Latest Value) equals the highest measurement (the Trigger Value) in the preceding X number of values (the Lookback Period) and in between these two measurements the measurements had decreased to X increments below (the Critical Level) the Trigger Value before hitting the same level again at the Latest Value. The macro should also check that the subsequent X values (the Following Period) decrease more than X increments (the Danger Level) from the Latest Value before returning the flag. The flag can be as simple as returning a "0" in a cell, but the cell used should be different from the first scenario.
The sheet contains some code defining the increments because the size of the increments varies according to the level of measurement. For example, below 2.0 increments are 0.01 but at 2.0 and above increments are 0.02. This code will be provided to the successful bidder.
In all cases X should be definable by the user entering the value in another cell and the macro should not trigger until column A contains at least the same number of measurements as the Lookback Period.
Explaining a bit more about the measurements may help to understand the purpose of the macro. The measurements are engineering tolerances derived from testing metals in a laboratory using our own incremental scale. Once the highest or lowest measurement has been recorded (ie the Trigger Value) we then need to look for a situation where the measurements go up or down to the Critical Level before going back to the Trigger Value on the Latest Value and then continuing to the Danger Level in the Following Period.
On a line chart it looks like there is an invisible line drawn horizontally from the Trigger Value and this acts like a ceiling or a floor (depending on whether the Trigger Value was the highest or lowest value in the Lookback Period) so that the measurements may not go past this level (+/- 1 increment) and the line just looks like it is bouncing off the ceiling/floor and not able to penetrate through. On occasion it will penetrate through and that is why we need to ensure the Danger Level is reached during the Following Period to ensure this does not happen. Once the macro has checked the Danger Level has not been reached we need the flag to appear for a few seconds to show that the criteria has been met whilst the macro carries on monitoring the Lookback Period for the next time one of the two scenarios occurs. For the subsequent scenarios it may be that the previous Latest Value becomes the next Trigger Value.
Once the criteria are met the line chart will look like either a W or an M depending on whether the Trigger Value is the highest or lowest in the Lookback Period.
The W shape occurs when the Trigger Value is the lowest value in the Lookback Period and the Latest Value equals the Trigger Value. The first of the bottom points of the W represents the Trigger Value, the top point in the middle represents the Critical Level, the second of the bottom points is the Latest Value and the final diagonal of the W would be the Danger Level. When the line on the chart looks like a W it looks like the bottom two points are bouncing of an invisible floor.
The M shape occurs when the Trigger Value is the highest value in the Lookback Period and the Latest Value equals the Trigger Value. It looks like a mirror image of the W scenario.
The attached Excel file contains a line chart showing how the "W" scenario looks in graphical form. Ignore the fact that the data is in column B, this file is just to illustrate the example.
So in the "W" scenario:
1. Lookback Period is set by the user to 30
2. The macro determines that the lowest value during the Lookback Period (the Trigger Value) is 2.0
3. Critical Level is set by user to 5 increments (which at the 2.0 level would be 0.02 per increment or 0.10 for 5 increments)
4. Following Period is set by the user to 4 measurements
5. Danger Level is set by the user to 3 increments (which at the 2.0 level would be 0.02 per increment or 0.6 for 3 increments)
The Trigger Value of 2.0 was the lowest value and was recorded at measurement number 100, the Critical Level of 2.10 (2.0 + 0.1) was exceeded at measurement number 110 and at measurement number 120 the Latest Value is 2.0. In the Following Period the Danger Level of 2.06 is reached so the macro can return the flag.
If anyone has any questions please ask.
James