vba - returning a range value from a function

Question

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.

Answer 1

In call to searchAddress write

Set nettowertadresse = searchAdress(.....) 

The problem is in assigning the return value of searchAdress to nettowertAddress

Answer 2

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.