Why only sometimes does VBA require a return value for a Sub?

[bklabel1]

I have a sub with two string inputs variables. When I write a call to it and move to the next line I get an error saying that = is expected. I don't get this error when writing code to call other subs. The callED function is a Sub not a function so why is it demanding a return value on the call only in some places. I have run into this with VBSCript also.

Thanks,

Kevin

 

[Norie]

Can you post the code?

 

[bklabel1]

Norie,
First I want to say I appreciate the assistance. I understand the more complex part of VBA and now I'm stuck on getting a Macro started and not knowing why I get a compile error of Expected: =

My work PC wont let me use Mr. Excel so I will use a subset of it here.
Two issues I have are 1) How do I get this into a Macro and replay it? 2)

Function SearchVerify()
  CopySheetFromCloseWB("H:\mywork","myTab")
End Function

Sub CopySheetFromCloseWB(strWBPath As String, strTab As String)
   Applicatoin.ScreenUpdating = False

  Set Closed book = Workbooks.Open(strWBPath)
  closedBook.Sheets(strTAb).Copy Before:= ThisWorkbook.Sheets("Sheet1")
  closedBook.Close SaveChanges:=False

  Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

First, I would note that your function is not really a function... it does not return anything. On top of that, there is a problem of syntax with the line of code inside your function. There are two possible ways to call a subroutine...

Call YourSubroutineName(ArgumentList)

YourSubroutineName ArgumentList

The first method (using the Call keyword) requires the parentheses around the argument list... the second method should not put parentheses around the argument list. I know, people use parentheses all the time with the second method, but in each case I guarantee there is only a single argument for the subroutine. If you have a subroutine with a single argument and type it this way...

YourSubroutineName(SingleArgument)

if you look carefully, you will see VBA inserts a space between the subroutine name and that single argument in parentheses (virtually turning it into the second method's structure). Now, about those parentheses. Whenever you place parentheses that are not required by syntax, VB sees that as a signal to evaluate whatever is within the parentheses. For a single argument in parentheses, VB simply evaluates this as the argument itself... so, no problem. However, if you encase two or more argument values in parentheses, VB does not know what to do with it... using a numeric example, (2,3) cannot be evaluated because VB is not being told what to do with those two values. That is the problem with your code line inside your "function"... it contains two argument values inside parentheses that are not required by syntax, hence, an error is generated (you get the missing equal sign message because as you wrote it, it looks like a function call that is not returning a value anywhere (the value VB thinks the syntax should be generating is not being assigned to anything).

 

[bklabel1]

The second question is not above.
2) why is the compiler asking for an equal sign on CopySheetFromCloseWB? It is calling a Sub and not a function.

 

[bklabel1]

Rick,
You are an excellent technical writer. When I read the information is seems like someone is talking to me.
I don't know if I will get back to this tonight but I will try again tomorrow with the new infomation.

Another part I am having trouble with and it may have been answered by you in your information you just sent is I'm having trouble with the staring point of the code. I don't understand how the Run Macro list choices match up with the subs and Functions. How does VBA know which ones are starting points and which are Functions? Does it list all Functions in the Macro List or just the ones from when the macro is created. I can experiment to find out but until I get the functions and subs with more than one parameter working I can't backup to see the first question.

Thanks,

Kevin