Why Syntax Error, cant find why?

[ilcaa]

I cant understand why it is is telling me Syntax Error, maybe i just cant see it...The syntax error occurs at Process_Trades(Trade, TradeNumber) bolded below...

Sub Get_Trades(TradeFile)

Dim Trade As String
Dim TradeNumber As Integer

TradeNumber = 1

'Get 1 Trade from trade file
        Trade = Mid(TradeFile, CharCount, 2000)
              
'Process trades
        If Left(Trade, 1) = "A" Or Left(Trade, 1) = "P" Then
            [B]Process_Trades(Trade, TradeNumber)[/B] 
           TradeNumber = TradeNumber + 1
        End If

-----------------------------------------------------------
Sub Process_Trades(Trade As String, TradeNumber As Integer)

Range("a7").Value = "Trades"
Set TradeRow = Range("a65000").End(xlUp).Offset(1, 0)
           
           'Add Trade Data to Column A, Format as Text
                TradeRow.Value = Trade
                TradeRow.NumberFormat = "@"
                    
            'Add Trade Number to Column B
                If Len(TradeRow.Value) > 1 Then
                    TradeRow.Offset(0, 1).Value = TradeNumber
                End If

 

[RoryA]

Yep - so do I!

 

[Rick Rothstein]

Hmm, thanks.
I still don't really understand it.

This is how I have been explaining it since back in my volunteering days for the compiled version of Visual Basic, see if it helps you any (it is an actual response I gave to someone once, hence the reference to an "original question" and the ChDrive/ChDir functions)...

I'll address your original question shortly, but first, let me address a habit that you must learn to break or you will end up with what seems like unexplainable errors as you go forward in your programming career. When you call a subroutine that takes parameters, you can do so two different ways...

SubroutineName ArgumentList

or

Call SubroutineName(ArgumentList)

The syntax requires the parentheses only for the second method. If the argument list consists of only one argument, then you can surround the argument with parentheses BUT if the argument list consists of two or more individual arguments, then surrounding the list with parentheses will generate an error. Why? It has to do with how VB treats parentheses that are not required by syntax... it considers them to contain an expression that it must evaluate. A single argument (which can even possibly be an expression) can be evaluated (if it is not an expression, it just evaluates as itself, otherwise the expression is evaluated); however, a comma separated list of items or expressions has no meaning to VB's evaluation engine and so it will generate an error if asked to evaluate it (in order for VB to evaluate multiple items, there must be an operator between those values and the comma is not an operator). Now, to get to the above two code lines from the code you posted... ChDrive and ChDir are technically built-in VB statements, but underneath it all, the are subroutines that have arguments (it is just VB provided them to you instead of you having to create them), so they follow the same rules as for subroutines that I outlined above. The bad habit I am attempting to break you of... do not use parentheses around "things" in VB unless required by syntax or unless needed to clarify the order that parts of an expression need to be calculated in.

 

[Jonmo1]

Thanks Rick, that definately helps.

The part causing my confusion though, is
When you say something is "Not Required", that in itself does not imply that it is actually "wrong". Quite the opposite.
Normally when I hear "Not required", that means that it's ok to still do it anyway, it's just not necessary.
Furthermore, it's 'usually' a best practice to still do the 'not required' parts, just as a force of habbit.
To develop the habbit of always doing it, so you don't forget it when it IS required.

Like many formula arguments

=A1+(B1*C1)
The parens there are not required, because we know the underlying order of calcultions.
But, it's not 'wrong' to put them there anyway. It will not cause an error.

 

[Rick Rothstein]

The part causing my confusion though, is
When you say something is "Not Required", that in itself does not imply that it is actually "wrong". Quite the opposite.
Normally when I hear "Not required", that means that it's ok to still do it anyway, it's just not necessary.

I see what you are saying, but I do not know how else to state it. Yes, parentheses can be used, but only for single arguments (because they can be evaluated). Maybe saying it this way would be clearer... if the syntax requires parentheses, use them (obviously), otherwise (for all other situation except for clarifying the order the parts of a single expression should be evaluated in) don't use parentheses at all.

To develop the habit of always doing it, so you don't forget it when it IS required.

That is what I am recommending against... it can only get you in trouble... better to understand the syntax of the commands you use and abide by their requirement for parentheses if needed.

Like many formula arguments

=INDEX(A:A,5,1)
The 1 is definitely not required, but the formula will not produce an error if you put it there anyway.

Functions, when used as a function (I'll explain that in a minute), always require parentheses around its argument list no matter what; hence, the parentheses are "required by syntax" (a function call won't work without them). Unlike VB, some other languages make no distinction between a subroutine and a function... for those languages, what VB calls a subroutine, they call a function (like any other function) except that it returns no value. VB actually can do that also... you can call any function (that performs an action) in the same way as you call a subroutine and it will work. Take the MsgBox function. If you use it as a function, it returns a value...

Answer = MsgBox("Answer ""Yes"" or ""No"".", vbYesNo, "Make User Answer Yes Or No")

However, VB allows you to call this like a subroutine as well, either like this...

Call MsgBox("Answer ""Yes"" or ""No"".", vbYesNo, "Make User Answer Yes Or No")

or like this...

MsgBox "Answer ""Yes"" or ""No"".", vbYesNo, "Make User Answer Yes Or No"

What happens to the return value? It just gets "lost"... actually, it gets assigned to a memory location just like above when I assigned it to the Answer variable (remember, variables are just high level names for a memory location), but that memory location is not assigned to any variable name, so the memory location gets cleared when VB's garbage collection routines activate.

 

[Rick Rothstein]

Like many formula arguments

=A1+(B1*C1)

The parens there are not required, because we know the underlying order of calcultions.
But, it's not 'wrong' to put them there anyway. It will not cause an error.

Hey, you changed the formula! My response above was for your original formula. I would point out, though, that for Excel worksheet formulas, none of the discussion about parentheses applies... there are no such thing as subroutines in worksheet formulas... everything that is not expression or comparison is an function and those require parentheses. Our discussion only applies to VB code lines where subroutines, methods and statements can exist.

 

[Jonmo1]

yeah, sorry about the change.
I thought the new formula made my point better.
And my point only being that "Just because it's not required, doesn't mean it's wrong to do it anyway"
Generally speaking that is, not just in Excel. It's a real world life thing.
Usually it's better to still do the 'not required' things, often it's like extra credit or something.
Do it the right way, then learn how to eliminate the not required parts.

I understand what you're saying now, it still just seems backwards to me.

 

[Rick Rothstein]

And my point only being that "Just because it's not required, doesn't mean it's wrong to do it anyway"

By the way, I did cover parentheses for evaluation purposes in my response in Message #14 when I said...

"if the syntax requires parentheses, use them (obviously), otherwise (for all other situation except for clarifying the order the parts of a single expression should be evaluated in) don't use parentheses at all."

 

[Jonmo1]

Yes you did.

Don't get me wrong, I understand and accept your explaination. Thanks.

 

[Rick Rothstein]

Yes you did.

Don't get me wrong, I understand and accept your explaination. Thanks.

I figured you did... I just wanted to keep the thread as complete as possible for those who might read this thread in the future

 

[RoryA]

Also, remember that using parentheses around even a single argument can get you into trouble. If the routine you are calling expects an object as its argument, you will get an error if you enclose it in parentheses (unless it's a function whose return value you are storing).