Help with VBASIC!

[Al B. Sure!]

Now I remember what was a problem. Z needs to be defined for each subsequent sub. So having each sub have Z = 10, means it doesn't progress. If I don't define Z for each subroutine, it will not run.

[Al B. Sure!]

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

I never opened the developer tab in Excel until two business days ago. It sounds like those things would be good ideas but I have no idea what the syntax is for stuff like that.

The Z is just the 10th row on an excel spreadsheet after some headers, a button, etc.

[loinburger]

Sub test()

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

End Sub

Sub A(Z As Integer)
      Cells(Z, 3) = "Execute A"
End Sub

Sub B(Z As Integer)
      Cells(Z, 3) = "Execute B"
End Sub

Sub C(Z As Integer)
      Cells(Z, 3) = "Execute C"
End Sub

I haven't used VB in about ten years, but the syntax should be something like that

[Al B. Sure!]

"ByRef Argument Type Mismatch" on the Z on Call A(Z)

[loinburger]

Change the three instances of "Z As Integer" to "ByVal Z As Integer" - apparently VBA passes by reference by default

[Al B. Sure!]

That worked. Thank you. It'll be a lot easier to manipulate now. Having 8 different sets of conditional actions nested into a single subroutine was ridiculous and I had only covered Spots, Forwards, and Hedges!

Now since the Hedges are going to run either the spot procedures (depending on currency type) or the forwards (depending on currency type) depending on whether the settle date minus trade date <= 3, I would like to have a subroutine for hedges then do either the spot or the forward subroutines.

This seems to work:

Sub test()

Z = 10
Do Until IsEmpty(Cells(Z, 1))
    If Cells(Z, 2) = "Spot" Then
        Call Spot(Z)
    ElseIf Cells(Z, 2) = "Forward" Then
        Call Forward(Z)
    ElseIf Cells(Z, 2) = "Hedge" Then
        Call Hedge(Z)
    End If
Z = Z + 1
Loop

End Sub

Sub Spot(ByVal Z As Integer)
      Cells(Z, 3) = "Execute Spot"
End Sub

Sub Forward(ByVal Z As Integer)
      Cells(Z, 3) = "Execute Forward"
End Sub

Sub Hedge(ByVal Z As Integer)
    If Cells(Z, 4) = "3" Then
        Call Spot(Z)
    Else
        Call Forward(Z)
    End If
End Sub

[Sava]

This isn't a [civil] thread, so alby, go eat your own ****

Sent from my XT1254 using Tapatalk

[BlackCat]

This isn't a [civil] thread, so alby, go eat your own ****

And get your indentations fixed

[pchang]

Relax. It's not like he's scripting in Python.

[BlackCat]

Doing that would probably drive him crazy.

I just consider proper indentation one of those small habits that makes life easier for everyone

[pchang]

I do too, but requiring it as part of the language is a bit much for me.

[Hauldren Collider]

It's far from the biggest problem I have with python. Granted I like python but it loses a lot of its charm when your project starts to involve more than maybe 3 people.

[Proteus_MST]

Doing that would probably drive him crazy.

I just consider proper indentation one of those small habits that makes life easier for everyone

Indentation is the Alpha and Omega for proper readability ... especially for longer functions/classes

[Dauphin]

Given Albie's penchant for sharing potentially classified information, I half expected the code he was going to post was related to missile guidance systems or Stuxnet or some such.

[BlackCat]

Considering his security clearance level he probably have