vba - properly passing range value to a function

Question

in my application i call a function and pass 4 values to calculate a cell adress:

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

function:

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

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

End Function

now the problem is that i get error 1004 "application defined or object defined error" and i think that maybe the range is not properly passed because the debugger shows no value for the variable "inputrange" when jumping to the function. please give some advice on how to make this function work. thanks.

It seems that the variable `inputRange` is entering the function `As Range` but for the use you do should rather be `As String`. What is exactly the value you pass to the function (just give an example)? — Matteo NNZ, Nov 10 '14 at 09:28
You're wrong when you pass the range but then you want to use just its address (which is a string). Below you have two good answers, I would use Richard's one as it allows you to use the function still keeping the input as Range (so you don't have to change much your code if this function is already spread in other parts of the code) — Matteo NNZ, Nov 10 '14 at 09:56
yeah both of them work but they dont return a range. "searchAdress" then contains the string "nettowert". — chrnit, Nov 10 '14 at 10:10
I updated my answer (for completeness) to show your other bug, and linked it to your follow-up question. — Richard Le Mesurier, Nov 10 '14 at 12:24

score 3 · Accepted Answer · edited May 23 '17 at 10:26

Your function, searchAddress, declares inputrange as Range. This means that the object inside your function is a Range object.

So you should not be using it as .Range(inputrange). Instead try using this code, which treats it correctly as a Range object:

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

Also note that there is another bug in your code that calls this function. You need to use the keyword Set when assigning the return value to your nettowertadresse variable:

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

Otherwise you will experience Run-time error '91', which you mention in your follow-up question.

score 2 · Answer 2 · answered Nov 10 '14 at 09:34

2

you add a Range to a rangeobject in your function, your function looks like this then:

  With Workbooks(inputworkbook).Sheets(inputsheet).Range(Range("A1:C60"))
      Set searchAdress = .Find(inputsearchtext, LookIn:=xlValues)
  End With

You should change this:

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

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

End Function

And then pass this to the function:

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

answered Nov 10 '14 at 09:34

Goos van den Bekerom

1,475
3
20
32

that seems to work but i need an address returned. so far, it seems that the variable searchAdress inherits the value "nettowert" but not an adress. i thought that .find returns an adress. what am i doing wrong? getting error 91 btw – chrnit Nov 10 '14 at 10:04
is this still a problem? or it this fixed with the other answer you got? – Goos van den Bekerom Nov 10 '14 at 10:19
debugger says that the value in searchAdress is a Range but it contains the value "nettowert" – chrnit Nov 10 '14 at 10:26
ah I see, you get the value in the cell but you need the address. you could try this to check `MsgBox(searchAdress.Address)` – Goos van den Bekerom Nov 10 '14 at 10:29
yes, the msgbox returns the correct value. now how can i return it via the function? – chrnit Nov 10 '14 at 10:39
also i am getting error 91 saying that an object variable or with block variable has not been set when it jumps to the END FUNCTION. – chrnit Nov 10 '14 at 10:41
Function searchAdress(inputworkbook As String, inputsheet As String, inputsearchtext As String, inputrange As Range) As Range Dim temp1 As Range With Workbooks(inputworkbook).Sheets(inputsheet) Set temp1 = inputrange.Find(inputsearchtext, LookIn:=xlValues) searchAdress = temp1.Address End With End Function – chrnit Nov 10 '14 at 10:45
actually i tried it with another range variable object that passes the adress to searchAdress and that is the problem – chrnit Nov 10 '14 at 10:46
nice (:! you could return it like `set searchAdress = searchAdress.Address` for example – Goos van den Bekerom Nov 10 '14 at 11:56

vba - properly passing range value to a function

2 Answers2