Defining a function to use multiple places in code

[Guest]

I have a function that I need to use multiple places in my code, but don't
want to have to type it over and over. Could someone give an example of how
I'd set it up and use it? You can give a simple example and I'll fill in my
own data.

Thanks,
Barb Reinhardt

 

[NickHK]

Make your function public in a module, then call it from whereever you need
its functionality. As a trivial example :

' In a module
Public Function GetVal(argRange as Range) as variant
GetVal=argRange.Value
End function

' Call the function from anywhere
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox GetVal(Target)
End Sub

NickHK

 

[Guest]

Let's say I want to pass a string and a value from RC7 of the open workbook
to the function. How would I set up the function.

This is what I have so far:

Function row(lookup As String, range As String) As Variant
row = "=MATCH(lookup,indirect(""'[""&range&""]Sheet1'!""&C2),TRUE),0)"
Debug.Print row
End Function

In the match function, I want it to check COLUMN 2.

How do I do this?

 

[Dave Peterson]

First, I wouldn't use Row as the function name--it looks too much like the .Row
property for a range object. Same thing with using Range as a variable, too.

Function myRow(Lookup as String, myRng as Range) as variant

dim res as variant
res = application.match(lookup, myRng.columns(2), 0)

if iserror(res) then
myRow = "some error message here?"
else
myrow = res
end if

end function

And you could use it like:

sub testme01()

dim myRng1 as range
dim myStr as string
with activesheet
set myrng1 = .range("c1:d99")
myStr = .range("a1").value
end with

msgbox myRow(mystr,myrng1)
end sub

But this is just a guess. I'm confused about what you're passing in your range
variable--do you really want a range? Are there two columns included in that
range?

And your match statement is using True and False, so I'm confused about that,
too.

Let's say I want to pass a string and a value from RC7 of the open workbook
to the function. How would I set up the function.

This is what I have so far:

Function row(lookup As String, range As String) As Variant
row = "=MATCH(lookup,indirect(""'[""&range&""]Sheet1'!""&C2),TRUE),0)"
Debug.Print row
End Function

In the match function, I want it to check COLUMN 2.

How do I do this?

Make your function public in a module, then call it from whereever you need
its functionality. As a trivial example :

' In a module
Public Function GetVal(argRange as Range) as variant
GetVal=argRange.Value
End function

' Call the function from anywhere
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox GetVal(Target)
End Sub

NickHK

 

[Guest]

I have this type of thing stored in RC8


[Workbook.xls]Sheet1'!

I need to to do the match on Column 2 of this sheet.

I'm thinking that the second argument isn't right, but I'm not certain.

First, I wouldn't use Row as the function name--it looks too much like the .Row
property for a range object. Same thing with using Range as a variable, too.

Function myRow(Lookup as String, myRng as Range) as variant

dim res as variant
res = application.match(lookup, myRng.columns(2), 0)

if iserror(res) then
myRow = "some error message here?"
else
myrow = res
end if

end function

And you could use it like:

sub testme01()

dim myRng1 as range
dim myStr as string
with activesheet
set myrng1 = .range("c1:d99")
myStr = .range("a1").value
end with

msgbox myRow(mystr,myrng1)
end sub

But this is just a guess. I'm confused about what you're passing in your range
variable--do you really want a range? Are there two columns included in that
range?

And your match statement is using True and False, so I'm confused about that,
too.

Let's say I want to pass a string and a value from RC7 of the open workbook
to the function. How would I set up the function.

This is what I have so far:

Function row(lookup As String, range As String) As Variant
row = "=MATCH(lookup,indirect(""'[""&range&""]Sheet1'!""&C2),TRUE),0)"
Debug.Print row
End Function

In the match function, I want it to check COLUMN 2.

How do I do this?

Make your function public in a module, then call it from whereever you need
its functionality. As a trivial example :

' In a module
Public Function GetVal(argRange as Range) as variant
GetVal=argRange.Value
End function

' Call the function from anywhere
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox GetVal(Target)
End Sub

NickHK

"Barb Reinhardt" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
I have a function that I need to use multiple places in my code, but don't
want to have to type it over and over. Could someone give an example of
how
I'd set it up and use it? You can give a simple example and I'll fill in
my
own data.

Thanks,
Barb Reinhardt

 

[Guest]

I have the following:

Debug.Print myRow("TEXT", Cells(i, "H").Value & "C2")


Function myRow(Lookup As String, myString As String) As Variant

Dim res As Variant
'res = Application.Match(Lookup, myString.Columns(2), 0)
res = Application.Match(Lookup, myString, 0)
Debug.Print res, Lookup, myString
If IsError(res) Then
myRow = "some error message here?"
Else
myRow = res
End If

End Function

Error 2015
TEST
'[workbook.xls]sheet1'!C2

I think I need an indirect around this, but can't get it to work.

First, I wouldn't use Row as the function name--it looks too much like the .Row
property for a range object. Same thing with using Range as a variable, too.

Function myRow(Lookup as String, myRng as Range) as variant

dim res as variant
res = application.match(lookup, myRng.columns(2), 0)

if iserror(res) then
myRow = "some error message here?"
else
myrow = res
end if

end function

And you could use it like:

sub testme01()

dim myRng1 as range
dim myStr as string
with activesheet
set myrng1 = .range("c1:d99")
myStr = .range("a1").value
end with

msgbox myRow(mystr,myrng1)
end sub

But this is just a guess. I'm confused about what you're passing in your range
variable--do you really want a range? Are there two columns included in that
range?

And your match statement is using True and False, so I'm confused about that,
too.

Let's say I want to pass a string and a value from RC7 of the open workbook
to the function. How would I set up the function.

This is what I have so far:

Function row(lookup As String, range As String) As Variant
row = "=MATCH(lookup,indirect(""'[""&range&""]Sheet1'!""&C2),TRUE),0)"
Debug.Print row
End Function

In the match function, I want it to check COLUMN 2.

How do I do this?

Make your function public in a module, then call it from whereever you need
its functionality. As a trivial example :

' In a module
Public Function GetVal(argRange as Range) as variant
GetVal=argRange.Value
End function

' Call the function from anywhere
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox GetVal(Target)
End Sub

NickHK

"Barb Reinhardt" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
I have a function that I need to use multiple places in my code, but don't
want to have to type it over and over. Could someone give an example of
how
I'd set it up and use it? You can give a simple example and I'll fill in
my
own data.

Thanks,
Barb Reinhardt

 

[Dave Peterson]

First, I would think that you'd want something like this:

'[Workbook.xls]Sheet1'!

Second, I would use two cells--one for the workbook name and one for the
worksheet name.

But you could parse that yourself in code:

I'd do something like this:

Option Explicit
Function DetermineWks(myVal As String) As Range

Dim WkbkName As String
Dim WksName As String
Dim OpenBracketPos As Long
Dim CloseBracketPos As Long
Dim TestWks As Range

OpenBracketPos = InStr(1, myVal, "[", vbTextCompare)
CloseBracketPos = InStr(1, myVal, "]", vbTextCompare)

WkbkName = Left(myVal, CloseBracketPos - 1)
WkbkName = Mid(WkbkName, OpenBracketPos + 1)

WksName = Mid(myVal, CloseBracketPos + 1)

If Right(WksName, 1) = "!" Then
WksName = Left(WksName, Len(WksName) - 1)
End If

If Right(WksName, 1) = "'" Then
WksName = Left(WksName, Len(WksName) - 1)
End If

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Workbooks(WkbkName).Worksheets(WksName)
On Error GoTo 0

Set DetermineWks = TestWks

End Function

=======
Then in your function:

function myRow(lookup as string, myStr as string) as variant

dim myWks as wks
dim res as variant

with activesheet
set mywks = determinewks(mystr)
if mywks is nothing then
'error in that cell
'what should happen here?
res = "Not valid workbook/worksheet"
else
res = application.match(lookup, mywks.range("B:B"), 0)
if iserror(res) then
res = "No match found
else
'it was found in row res
end if
end if
end with

myrow = res

end sub

(Untested, uncompiled--watch for typos.)

I have this type of thing stored in RC8

[Workbook.xls]Sheet1'!

I need to to do the match on Column 2 of this sheet.

I'm thinking that the second argument isn't right, but I'm not certain.

First, I wouldn't use Row as the function name--it looks too much like the .Row
property for a range object. Same thing with using Range as a variable, too.

Function myRow(Lookup as String, myRng as Range) as variant

dim res as variant
res = application.match(lookup, myRng.columns(2), 0)

if iserror(res) then
myRow = "some error message here?"
else
myrow = res
end if

end function

And you could use it like:

sub testme01()

dim myRng1 as range
dim myStr as string
with activesheet
set myrng1 = .range("c1:d99")
myStr = .range("a1").value
end with

msgbox myRow(mystr,myrng1)
end sub

But this is just a guess. I'm confused about what you're passing in your range
variable--do you really want a range? Are there two columns included in that
range?

And your match statement is using True and False, so I'm confused about that,
too.

Let's say I want to pass a string and a value from RC7 of the open workbook
to the function. How would I set up the function.

This is what I have so far:

Function row(lookup As String, range As String) As Variant
row = "=MATCH(lookup,indirect(""'[""&range&""]Sheet1'!""&C2),TRUE),0)"
Debug.Print row
End Function

In the match function, I want it to check COLUMN 2.

How do I do this?

:

Make your function public in a module, then call it from whereever you need
its functionality. As a trivial example :

' In a module
Public Function GetVal(argRange as Range) as variant
GetVal=argRange.Value
End function

' Call the function from anywhere
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox GetVal(Target)
End Sub

NickHK

"Barb Reinhardt" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
I have a function that I need to use multiple places in my code, but don't
want to have to type it over and over. Could someone give an example of
how
I'd set it up and use it? You can give a simple example and I'll fill in
my
own data.

Thanks,
Barb Reinhardt

 

[Dave Peterson]

Ps.

Change this line:

Function DetermineWks(myVal As String) As Range
to
Function DetermineWks(myVal As String) As Worksheet

(I changed my mind and forgot to change that line.)

First, I would think that you'd want something like this:

'[Workbook.xls]Sheet1'!

Second, I would use two cells--one for the workbook name and one for the
worksheet name.

But you could parse that yourself in code:

I'd do something like this:

Option Explicit
Function DetermineWks(myVal As String) As Range

Dim WkbkName As String
Dim WksName As String
Dim OpenBracketPos As Long
Dim CloseBracketPos As Long
Dim TestWks As Range

OpenBracketPos = InStr(1, myVal, "[", vbTextCompare)
CloseBracketPos = InStr(1, myVal, "]", vbTextCompare)

WkbkName = Left(myVal, CloseBracketPos - 1)
WkbkName = Mid(WkbkName, OpenBracketPos + 1)

WksName = Mid(myVal, CloseBracketPos + 1)

If Right(WksName, 1) = "!" Then
WksName = Left(WksName, Len(WksName) - 1)
End If

If Right(WksName, 1) = "'" Then
WksName = Left(WksName, Len(WksName) - 1)
End If

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Workbooks(WkbkName).Worksheets(WksName)
On Error GoTo 0

Set DetermineWks = TestWks

End Function

=======
Then in your function:

function myRow(lookup as string, myStr as string) as variant

dim myWks as wks
dim res as variant

with activesheet
set mywks = determinewks(mystr)
if mywks is nothing then
'error in that cell
'what should happen here?
res = "Not valid workbook/worksheet"
else
res = application.match(lookup, mywks.range("B:B"), 0)
if iserror(res) then
res = "No match found
else
'it was found in row res
end if
end if
end with

myrow = res

end sub

(Untested, uncompiled--watch for typos.)

I have this type of thing stored in RC8

[Workbook.xls]Sheet1'!

I need to to do the match on Column 2 of this sheet.

I'm thinking that the second argument isn't right, but I'm not certain.

First, I wouldn't use Row as the function name--it looks too much like the .Row
property for a range object. Same thing with using Range as a variable, too.

Function myRow(Lookup as String, myRng as Range) as variant

dim res as variant
res = application.match(lookup, myRng.columns(2), 0)

if iserror(res) then
myRow = "some error message here?"
else
myrow = res
end if

end function

And you could use it like:

sub testme01()

dim myRng1 as range
dim myStr as string
with activesheet
set myrng1 = .range("c1:d99")
myStr = .range("a1").value
end with

msgbox myRow(mystr,myrng1)
end sub

But this is just a guess. I'm confused about what you're passing in your range
variable--do you really want a range? Are there two columns included in that
range?

And your match statement is using True and False, so I'm confused about that,
too.



Barb Reinhardt wrote:

Let's say I want to pass a string and a value from RC7 of the open workbook
to the function. How would I set up the function.

This is what I have so far:

Function row(lookup As String, range As String) As Variant
row = "=MATCH(lookup,indirect(""'[""&range&""]Sheet1'!""&C2),TRUE),0)"
Debug.Print row
End Function

In the match function, I want it to check COLUMN 2.

How do I do this?

:

Make your function public in a module, then call it from whereever you need
its functionality. As a trivial example :

' In a module
Public Function GetVal(argRange as Range) as variant
GetVal=argRange.Value
End function

' Call the function from anywhere
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox GetVal(Target)
End Sub

NickHK

"Barb Reinhardt" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
I have a function that I need to use multiple places in my code, but don't
want to have to type it over and over. Could someone give an example of
how
I'd set it up and use it? You can give a simple example and I'll fill in
my
own data.

Thanks,
Barb Reinhardt