vba - 从函数返回范围值 [英] vba - returning a range value from a function

问题描述

我想将一个范围值传递给一个函数，然后将结果的范围值返回到我的子。

sub：

  Dim nettowertadresse As Range 
  

和

  nettowertadresse = searchAdress（Dateiname，CurrentSheet，Nettowert，Range（A1：BA2））
  

功能：

 函数searchAdress（inputworkbook As String，inputheet As String，inputsearchtext As String，inputrange As Range）As Range 
 
使用工作簿（inputworkbook）.Sets（inputsheet）
设置searchAdress = inputrange.Find （inputsearchtext，LookIn：= xlValues）
结束
 
结束函数
  

第一个问题是我不能在函数中获取范围值。我认为我需要在searchAdress中的.adress，但它不会这样工作。也许与另一个将地址传递到searchAdress的对象？但是在任何情况下，我收到错误91.请指导如何从函数中的找到的单元格将地址返回到我的子文件中的变量nettowertadresse。

解决方案

您有两个问题：

1. 运行时错误'91'：对象变量或块变量不设置


2. 没有看到您有范围对象被返回

1 - 运行时错误'91'

对于第一个错误，原因是因为您调用功能。正如Fratyx指出的那样，调用<$ c时需要使用关键字 Set $ C> searchAdress（） 。

您还可以在以下位置看到：

• vba中的dim和set之间有什么区别


• 从功能返回范围


• Excel中的VBA功能返回范围？

原因是您正在尝试将对象结果分配给变量。在VBA设置对象时，您可以使用 Set 关键字。

2 - 没有看到范围对象

代码 返回一个范围对象根据需要。您可以通过一些调试测试代码来确认，如：

 设置nettowertadresse = searchAdress（Dateiname，CurrentSheet，Nettowert，Range A1：BA2））
 nettowertadresse.Select 
  

或

  MsgBox nettowertaddresse.Address 
  

您可能使用 MsgBox（） 或将鼠标悬停在编辑器中的变量上，该变量返回实际范围值（ nettowertadresse.value ）

---

这两个问题都与第二天的问题相关，这些问题解决了这些完全相同的问题，但在开始使用自己的功能之前： p>

• 当使用With，Range和.F时，使用vba错误91进行故障ind

看看这里的答案与我在这里给出的解释直接相关。不同之处在于您现在使用自己的功能，但基本的基本问题仍然是一样的。

i would like to pass a range value to a function and then return the resulting range value back to my sub.

sub:

Dim nettowertadresse As Range

and

nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2"))

function:

Function searchAdress(inputworkbook As String, inputsheet As String, inputsearchtext As String, inputrange As Range) As Range

  With Workbooks(inputworkbook).Sheets(inputsheet)
      Set searchAdress = inputrange.Find(inputsearchtext, LookIn:=xlValues)
  End With

End Function

The first problem is that i don´t get a range value in the function. I think that I need the .adress in searchAdress but it does not work that way. maybe with another object that passes the adress into searchAdress? but in any case i get error 91. please advise on how to return the adress from the found cell in the function back to the variable nettowertadresse in my sub.

解决方案

You have 2 problems:

1. Run-time error '91': Object variable or With block variable not set
2. not seeing that you have a Range object being returned

1 - Run-time error '91'

For the first error, the reason is because of the way you are calling your Function. As Fratyx points out, you need to use the keyword Set when calling searchAdress().

You can also see this in:

• What is the difference between dim and set in vba
• Returning a Range from a Function
• Can a VBA function in Excel return a range?

The reason for this is that you are trying to assign an object result to your variable. In VBA when setting objects, you use the Set keyword.

2 - not seeing a Range object

The code does return a Rangeobject as required. You can confirm this by some debug test code like:

Set nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2"))
nettowertadresse.Select

or

MsgBox nettowertaddresse.Address

This will select the cell which has your word in it, or display the address in a popup box.

You are probably using MsgBox() or hovering over the variable in the editor, which is returning the actual range value (nettowertadresse.value)

---

Both of these issues are related the your question from the other day, which addresses these exact same issues, but before you started to use your own function:

• Trouble with vba error 91 when using With, Range and .Find

See how the answer there relates directly to the explanations I give you here. The difference is that you are using your own function now, but the basic underlying issues are still the same.

这篇关于vba - 从函数返回范围值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！