Excel Visual Basic call function as stand-alone routine

Question

I'll get straight to the point; I'm trying to define a Function in Visual Basic which can simply be called without having to have something on the 'other side of the equation' as it were. Essentially I want to be able to define a routine which can be passed a series of variables and executes a routine based on those variables.

I currently have the following Function defined:

Function ImportData(WebAddress As String, OutputCell As Range)

With ActiveSheet.QueryTables.Add(Connection:= _
       "URL;" & WebAddress & _
       "bin/excelget.exe?TICKER=msft", _
       Destination:=OutputCell)

      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
      End With

End Function

What I want to be able to do is simply call this Function but not necessarily make something equal to or use this Function to manipulate something. So, for example, I'd like to be able to do something like the following:

Private Sub ExampleButton_Click()

ImportData("http://www.exampleURL.com/examplejsonhtmlformat","A3")

End Sub

When this Function is called, it simply steps through the Function using the variables defined. There is already an output defined in OutputCell so a cell doesn't need to 'equal' the output of this Function.

If anybody has any input, it would be much appreciated.

Thanks.

Why not just make it a Sub? – Charles Wood Jul 01 '14 at 23:08 — Charles Wood, Jul 01 '14 at 23:08

score 4 · Accepted Answer · edited May 23 '17 at 12:20

4

You want to make it a Sub - it is exactly what you describe: code that can be called but that doesn't return a value. Note that you don't put the parameters of a sub in parentheses when you call it. If you have

Sub myTest(a,b)

Then you call it with

myTest thing1, thing2

And NOT with

myTest(thing1, thing2)

Update based on excellent comments from @hnk and @Ioannis:

It is possible to call a Sub with

Call myTest(thing1, thing2)

But there is a subtlety, which has to do with the difference between passing a variable by value or by reference. When you pass by value, you make a copy: changing the parameter in the program does not change the original. However, when you pass by reference, it becomes possible to change the value of the parameter inside the sub - and that becomes the new value of the parameter after the sub returns. I'd the prototype says you expect the value to be passed by reference:

Sub MyTest(ByRef a)

Then you can override this behavior as follows:

Call with                 Passing by
MyTest a                  Reference
MyTest (a)                Value
Call MyTest(a)            Reference
Call MyTest((a))          Value

In general it is better to be explicit in the function prototype - specify if you want byVal or byRef and if the calling program gets it wrong you get warned bu the compiler. More info at Hidden features of VBA

If you are not at least a little bit confused or at least annoyed at Microsoft after this, you were not paying attention...

afterword it was pointed out by Rory that it is possible to call functions without assigning their return value. So you can have either

X = myFunc(y)

Or

myFunc y

Both will call the function - but note that when you don't expect a return value you don't use parentheses. Oh Microsoft, what were you thinking...

edited May 23 '17 at 12:20

Community

1
1

answered Jul 01 '14 at 23:13

Floris

45,857
6
70
122

Actually, you can also write it as `Call mytest(thing1, thing2)` as well if you don't like the `myTest thing1, thing2` style. Either way, +1 for the answer – hnk Jul 01 '14 at 23:44
1

@hnk Other than style, `myTest thing1, thing2` passes arguments by reference and `myTest(thing1, thing2)` passes arguments by value. It is discussed [here](http://stackoverflow.com/questions/1070863/hidden-features-of-vba/1070942#1070942). – Ioannis Jul 01 '14 at 23:55
Good point. The default for Call would become pass by value then. Tks for correcting. – hnk Jul 02 '14 at 00:04
1

@hnk Actually, when `Call` is used an extra pair of parenthesis is needed to enforce passing by value (documented [here](http://msdn.microsoft.com/en-us/library/chy4288y.aspx)). `VBA` has quite a few quirks! :) – Ioannis Jul 02 '14 at 00:11
2

haha, agree... it's very messy. The best practice is to explicitly mention ByRef and ByVal. Especially dangerous when Calling Functions from DLLs who are expecting a pointer or a value depending on how they're written. – hnk Jul 02 '14 at 00:14
Guys - this is good information. I will update my answer with it... It's worth keeping around (and comments often get purged...) – Floris Jul 02 '14 at 00:59
FYI all of this applies to Functions too. You do not have to use its return value in which case the calling conventions are exactly the same as for subroutines. – Rory Jul 02 '14 at 05:49
@rory you are right but when you don't even generate a return value it makes more sense to create a sub -it is why the distinction even exists. – Floris Jul 02 '14 at 15:51
1

@floris Agreed - I was merely pointing out that the same calling rules can apply. People often believe that when you call a function you must *always* use parentheses. – Rory Jul 02 '14 at 16:04
1

Awesome answer; got it sorted without using parenthesis and making a couple of changes to the existing function. I was representing OutputCell on its own instead of as "Range(OutputCell)". Thanks a lot :-) – Your_Unequal Jul 02 '14 at 19:49

Excel Visual Basic call function as stand-alone routine

1 Answers1