Excel Visual Basic调用函数作为独立程序 [英] Excel Visual Basic call function as stand-alone routine

问题描述

我会直截了当地我试图在Visual Basic中定义一个函数，它可以简单地被调用，而不必像现在那样在方程的另一边有一些东西。本质上，我想要能够定义一个可以传递一系列变量的例程，并基于这些变量执行一个例程。

我目前有以下函数定义：

 函数ImportData（WebAddress As String，OutputCell As Range）
 
使用ActiveSheet.QueryTables.Add连接：= _ 
URL;& WebAddress& _ 
bin / excelget.exe？TICKER = msft，_ 
目的地：= OutputCell）
 
 .BackgroundQuery = True 
 .TablesOnlyFromHTML = True 
 .Refresh BackgroundQuery：= False 
 .SaveData = True 
结束
 
结束函数
  

我想要做的只是调用此函数，但不一定会使某事等于或使用处理某事的功能所以，例如，我想要做一些如下的事情：

  Private Sub ExampleButton_Click（）
 
 ImportData（http://www.exampleURL.com/examplejsonhtmlformat，A3）
 
 End Sub 
  

调用此函数时，只需使用定义的变量遍历函数。 OutputCell中已经有一个输出定义，所以单元格不需要等于该函数的输出。

如果有任何人有任何输入，那将是很多感谢。

谢谢。

解决方案

Sub - 它正是你所描述的：可以调用但不返回值的代码。请注意，当您调用它时，不要将子参数放在括号中。如果你有

  Sub myTest（a，b）
  

然后用

  myTest thing1，thing2 
  

而不与

  myTest（thing1，thing2）
  

更新 @hnk和@Iannannis：

它可以调用 Sub / p>

 调用myTest（thing1，thing2）
  

但是有一个微妙的，它与通过值或引用传递变量的区别有关。当您通过值传递时，您复制一个副本：更改程序中的参数不会更改原始文件。但是，当您通过引用传递时，可以更改sub中的参数的值，并在子返回后变为参数的新值。我原型说你希望通过引用传递这个值：

  Sub MyTest（ByRef a）
  

然后，您可以按以下方式覆盖此行为：

 通过
调用MyTest a参考
 MyTest（a）值
调用MyTest（a）参考
调用MyTest（（a））值
  

一般来说，最好在函数原型中显式显示 - 指定是否需要 byVal 或 byRef ，如果调用程序发生错误，您会发现编译器被警告。更多信息，请访问 VBA的隐藏功能

如果您至少在微软之后至少有一点迷茫或至少对此感到懊恼，那么您并不关心...

afterword ，由Rory指出，可以调用函数而不分配其返回值。所以你可以使用

  X = myFunc（y）
  

或

  myFunc y 
  

两者都将调用该函数 - 但请注意，当您不期望返回值时，不使用括号。哦，微软，你在想什么...

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.

解决方案

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

这篇关于Excel Visual Basic调用函数作为独立程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！