Excel Visual Basic call function as stand-alone ro

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.

Answer 1

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...