Help with VBASIC!

[Al B. Sure!]

I'm trying to make a macro check for a value in a cell and then do a procedure when that value is present and a different procedure when it is absent down a series of rows. Endstate is for the macro to select one of about a dozen different procedures (so a dozen different values) each with about 50 steps in an external program. These procedures would correspond to the letter in the independent cell for each row.

Below is a simplified version of the code I wrote to try the concept. The problem is that it only checks for the value once and doesn't switch procedures based on the presence or absence of the value. I've also tried If... Then... Else but had the same problem. Given what I intend to do, I think If... Then... Else would be preferable but I'm not sure.

Sub test()

Z = 10
Do Until IsEmpty(Cells(Z, 1))
    Do While Not IsEmpty(Cells(Z, 2))
    Cells(Z, 3) = "YES"
    Z = Z + 1
    Loop
    Do While IsEmpty(Cells(Z, 2))
    Cells(Z, 3) = "NO"
    Z = Z + 1
    Loop
Z = Z + 1
Loop

End Sub

These are the results I get for different values:

1		NO
2	Y	
3		NO
4	Y

1	Y	YES
2	Y	YES
3		NO
4	Y

So it doesn't loop back to check the first Do While. I need it to keep checking for an unknown number of rows.

Can anyone help me?

Thanks

[Proteus_MST]

Looks to me like the outer line with Z = Z +1 is the problem.

You always switch to the next line in the 2 inner do ... while loops ...
so, you would already be in the perfect condition for the next run through of the outer loop,
but thanks to the additional Z = Z + 1 before the outer "loop" it skips one line., every time that it entered the 2nd inner loop (i.e. the one that checks for empty cells in (Z, 2))

Therefore I assume, if you remove the outer Z = Z + 1 it should work
(after all every possible condition of (Z, 2) is already covered in the 2 inner loops)

[loinburger]

Sub test()

Z = 10
Do Until IsEmpty(Cells(Z, 1))
    If Not IsEmpty(Cells(Z, 2))
      Cells(Z, 3) = 'YES"
    Else
      Cells(Z, 3) = "NO"
    Endif
    Z = Z + 1
Loop

End Sub

[Hauldren Collider]

edit: wrong thread

[Kuciwalker]

implicit variable declaration ftl

[Al B. Sure!]

Looks to me like the outer line with Z = Z +1 is the problem.

You always switch to the next line in the 2 inner do ... while loops ...
so, you would already be in the perfect condition for the next run through of the outer loop,
but thanks to the additional Z = Z + 1 before the outer "loop" it skips one line., every time that it entered the 2nd inner loop (i.e. the one that checks for empty cells in (Z, 2))

Therefore I assume, if you remove the outer Z = Z + 1 it should work
(after all every possible condition of (Z, 2) is already covered in the 2 inner loops)

Nope. That would cause it to loop ad infinitum (and crash the macro) regardless of the Do Until condition.

Sub test()

Z = 10
Do Until IsEmpty(Cells(Z, 1))
    If Not IsEmpty(Cells(Z, 2))
      Cells(Z, 3) = 'YES"
    Else
      Cells(Z, 3) = "NO"
    Endif
    Z = Z + 1
Loop

End Sub

That worked with a few changes. You forgot the Then after the If. I changed it to an If for a value because I'll have many non-numeric possible independent values.

So all further nested If's should work as long as I keep the Z = Z + 1 and Loop at the end?

Thanks, Loinburger.

[loinburger]

Yup, in your loop you want to do [stuff] for each row, so don't increment the counter until you've done [stuff] i.e. don't increment it until the end

In general, if you've got a loop with a counter (either as a terminating condition i.e. "terminate when counter >= constant" or in this case as an index) then you don't want to modify the counter more than once per loop iteration unless you know what you're doing. If you're tempted to modify the counter more than once then see if you can avoid this by introducing a second counter and/or using an inner subroutine. Note that it sometimes make sense to change the counter using an if-else (e.g. increment on an "if" and don't increment it on the "else", so you're still only modifying the counter once per iteration), but your code will make a lot more sense if the counter is monotonic (always increases or stays the same, or always decreases or stays the same; if it increments on the "if" and decrements on the "else" then your code will look like crap).

[kentonio]

Also you're in the military now Albie, so you should be using acronyms for everything. In this case the one you need is VBA (Visual Basic for Applications).

[N35t0r]

yeah, what Loin said. You should only increment loops once. Also, don't use while-loops to check for a single cell's value.


Also, what you are doing here can be done using a function.

[Al B. Sure!]

I want to split the procedures into different subroutines to make it less cumbersome to manipulate and adjust. I've tried something like the below, but there's no mechanism for it to check values in subsequent cells. I've tried different things but they didn't work. How can I make it progress to Z + 1 after calling back to the initial sub?

Sub test()

Z = 10
Do Until IsEmpty(Cells(Z, 1))
    If Cells(Z, 2) = "A" Then
    Call A
    Else
         If Cells(Z, 2) = "B" Then
         Call B
                Else
                       If Cells(Z, 2) = "C" Then
                       Call C
                       End If
          End If
     End If
Z = Z + 1
Loop

End Sub

Sub A()
      Cells(Z, 3) = "Execute A"
      Call test
End Sub

Sub B()
      Cells(Z, 3) = "Execute B"
      Call test
End Sub

Sub C()
      Cells(Z, 3) = "Execute C"
      Call test
End Sub

Eventually subroutine A, for example, will have subroutines within it, so I'll need it to call back multiple layers and still progress through cells.

[loinburger]

You don't need to call "test" at the end of "A"/"B"/"C" - after the subroutine is finished executing it automatically pops back up to the call point. Right now you've got infinite recursion because the functions are going to keep calling each other forever.

[loinburger]

Wiki explanation on the call stack. If you're in subroutine A and you call subroutine B at line N, then all of subroutine A's data gets pushed onto the stack and subroutine B gets called; when subroutine B is finished then subroutine A is popped off the stack, its data is restored, and it resumes at line N+1. (There are languages/optimizations/whatever that eliminate the call stack, but for now you don't care about them.)

[Al B. Sure!]

What I'm using this for is booking accounting transactions.

For example,

If "FX/SPOT" in column 1 then if currency is Malaysian, run subroutine AA; else, run subroutine AB

If "FX/FORWARD" in column 1 then if currency is Malaysian, run subroutine BA; else, run sub BB

If "FX/HEDGE" in column 1, then if settle - trade date <= 3, then if Malaysian, run sub AA; else, run sub AB; else if Malaysian, run BA; else BB

If "TBA/MBS" in column 1, then if Buy, run CA; else, run CB

etc.

Column 1 will have many different security/trade types. The procedures are fairly long (30+ keystrokes for each transaction) so I would like to break out the different subroutines.

[Al B. Sure!]

You don't need to call "test" at the end of "A"/"B"/"C" - after the subroutine is finished executing it automatically pops back up to the call point. Right now you've got infinite recursion because the functions are going to keep calling each other forever.

Interesting. I thought I tried this and it just ended. I'll check it out. I don't have my macros with me but I'll open up excel real quick.

[loinburger]

I just noticed that you're referencing Z inside of the A/B/C subroutines, but your Z from the test subroutine isn't visible there - I have no idea what your program is using there, probably some global variable floating around somewhere. Two options to fix this:

1. Pass in Z as a parameter to the subroutines
2. Turn the A/B/C subroutines into functions that return their string (e.g. "Execute A"), then have the test subroutine call the function and use the returned string in the assignment