Help getting trailing stop loss UDF to work?

[hadamhiram]

Hello VBA experts!

I'm not familiar with VBA at all, and my attempt to cobble together a function has been an abject failure

All the function needs to do is go through the next 50 rows in an adjacent column and test each cell in turn to see whether its value is: (a) higher than the previous max (and if so, reset the max to that new value); and (b) X units less than the previous max. The function can stop running once it finds the first (if any) cell that meets condition (b).

I think I'm getting stuck because I don't quite understand how VBA instructs excel to "move" from one cell to the next. Do cells have to be selected/activated before they can be analyzed as part of a loop? It seems like that would be very slow, and I have millions of rows of data to populate with this function. It seems like it would be better for a loop to simply use a counter variable to look at the next row each cycle...

So the logic of the function extremely straightforward:

- Is CurrentCell > Previous_Max? If true, then Previous_Max = CurrentCell. If False, then ...
- Is CurrentCell < (Previous_Max - 3)? If true, then StopLossAddress = CurrentCell's address (i.e. the cell's range), and Exit loop. If false, continue loop.
- After loop is finished, if StopLossAddress <> 0, then function = StopLossAddress (as a string). If no StopLossAddress was found, function = "no stop loss found".

But I just don't have enough experience with VBA to get it to work. I just keep getting errors!

Can anyone have a look at my code and see what's wrong? (Annotated code attached).

Thanks!

[xladept]

Please Login or Register  to view this content.

But it needs to be in a Standard Code Module in a Macro-Enabled Workbook:

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Open your Workbook

Save your Workbook or Another Workbook designated for Macros as Macro_Enabled

Press ALT + F11 to open the Visual Basic Editor.

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

[hadamhiram]

Unfortunately it still doesn't work! I get an "argument not optional" error when I try to compile, in the following line:

ActiveCell.Range = Application.Caller.Offset(0, -1)

[xladept]

Try:

Please Login or Register  to view this content.

[hadamhiram]

That works! Thank you!

[xladept]

You're welcome!