Simplifying Find Functions in Excel VBA
IENG 490 LAB
FIND/FINDNEXT
AND
BYREF/BYVAL
BY
RESEARCH ASSISTANT:
ADEWALE ADESINA
FIND FUNCTIONS
Generally, each of the function is expressed as
.Find(
What
,
After
,
LookIn
,
LookAt
,
SearchOrder
,
Sea
rchDirection
,
MatchCase
,
MatchByte
,
SearchFormat
)
Take note of the
“dot”
before
“Find”,
the meaning of
this is that you will have to find something from
somewhere.
That “Somewhere” is written before the
“dot”
.
Ofcourse “Somewhere” to find a string or any Excel data
type must be a collection of strings or other data types
which is or must be definate.
That is, it should have a specified boundaries (starting
point and end point).
Obviously, find function anticipates to look (in, at),
after, search (order or with direction or certain format),
match data (by case or character, byte) from a range of
string of data type.
Hence a range of data source must be specified with find
function and a
Range
object represents the first cell where
that information is found.
Hence a range of data source must be specified
with find function and a
Range
object represents
the first cell where that information is found.
The decleration is
Range.Find(Requiredfunction:=string/datatype,Opti
onalfunction:=excelValues)
The FindAll function simplifies the use of the Find
method.
Function FindAll(What, Optional SearchWhat As Variant, _
Optional LookIn, _
Optional LookAt, _
Optional SearchOrder, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional MatchCase As Boolean = False, _
Optional MatchByte, _
Optional SearchFormat) As Range
The function returns a range consisting of all the
cells that match the specified criteria.
The table below presents the list of the required
and optional functions
Sub Find( )
Dim SalaryWkSht As Worksheet
Dim LastRow As Long, i As Long, c As Long
Dim myCell As Range
Set SalaryWkSht = Sheets("Sheet1")LastRow = SalaryWkSht.Range("A" &Rows.Count).End(xlUp).Row
c = 12000
Set myCell = SalaryWkSht.Range("B1:B" &LastRow).Find(What:=c, LookIn:=xlValues)
If Not myCell Is Nothing Then
MsgBox "Value found in cell " & myCell.Address
End If
Exit Sub
End Sub
Assumming the value to be found appears more
than once in the cell, we need .FindNext function to
find them within the cell.
Sub FindNext()
Dim c As Range
On Error Resume Next
With Worksheets(1).Range("B1:B500")Set c = .Find(10000, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
MsgBox "Value found in cell " & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End Sub
BYVAL AND BYREF FUNCTION
A previously declared argument can by passed over
or copied to another argument(s) by using By Value
(ByVal) or By Reference (ByRef) Subfunction.
ByRef passes a
pointer
to the variable, so any
changes are reflected everywhere that variable is used.
ByVal means that you are passing a copy of a
variable to your Subroutine. Changes are made to the
copy but the original will not be altered
.
This implies that , in the case of objects, they are always
passed as references (pointers) but if you pass an
object ByVal you pass a copy of the pointer to the object
rather than the original pointer (note: not a copy of the
object)
ByVal
Public Function functionByVal(ByVal x)
variant/datatype
functionByVal = variant/datatype
End Function
ByRef
Public Function functionByRef(ByRef x)
variant/datatype
functionByRef = variant/datatype
End Function
Default argument
Public Function functionByDefault(x)
variant/datatype
functionByDefault= variant/datatype
End Function
It should be noted that whenever, the default
argument is used, ByRef is preset by the Excel
VBA.
Example: Datatype
Sub ieng490( )
Dim i As Long
i = 1
Call Change_ByRef(i)
' i is changed by the Change_ByRef procedure and
that change IS reflected here too because it was
passed by reference
MsgBox "i is now: " & i
End Sub
Sub Change_ByRef(ByRef lInput As Long)
lInput = 14
End Sub
Sub ieng490ByVal ( )
Dim i As Long
i = 1
Call Change_ByVal(i)
' i is changed only within the Change_ByVal
procedure because a COPY of it was passed
MsgBox "i is still: " & i
End Sub
Sub Change_ByVal(ByVal lInput As Long)
lInput = 21
End Sub
Example: Object
Sub Object_ieng490ByRef ( )
Dim j As Range
Set j = Range("B1")Call Change_object_ByRef(j)
' j is changed to a different cell by the Change_ByRef
procedure and
' that change IS reflected here too because it was passed by
reference
MsgBox "j is now: " & j.Address
End Sub
Sub Change_object_ByRef(ByRef jInput As Range)
' change the range variable to one row down
Set jInput = jInput.Offset(1)
End Sub
Sub Object_ieng490ByVal ( )
Dim j As Range
Set j = Range("B1")Call Change_object_ByVal(j)
' r is changed to a different cell by the Change_ByVal procedure
but
' that change IS NOT reflected here because it was passed by
value.
MsgBox "j is still: " & j.Address
End Sub
Sub Change_object_ByVal(ByVal jInput As Range)
' change the range variable to one row down
Set jInput = jInput.Offset(1)
End Sub
Data ByRef
Sub MyHome ( )
Dim x As Double
x = 5.005
MsgBox AddUp(x)
End Sub
Function AddUp(ByRef x As Double) As
Double
x = 10 + x
End Function
Example 2: Data ByVal
Sub MyHome( )
Dim x As Double
x = 5.005
MsgBox AddUp(x)
MsgBox x
End Sub
Function AddUp(ByVal x As Double) As
Double
AddUp = 10 + x
End Function
CIRCLE RADIUS, AREA AND CIRCUMFERENCE
Sub CalRadiusByRef()
Dim r As Double
r = 2.5
Radius = r
MsgBox "Radius of the circle is " & r
MsgBox "Area of the circle is " & Area(r)
MsgBox "Circumference of the circle is " & Circumference(r)
End Sub
Function Area(Radius As Double) As Double
Radius = Radius ^ 2
Area = WorksheetFunction.Pi * Radius
End Function
Function Circumference(Radius As Double) As Double
Circumference = 2 * WorksheetFunction.Pi * Radius
End Function
You will notice that the value of the circumference of 39.2669
obtained by the ByRef function is wrong
The actual value for the circumference of a circle with radius
of 2.5 is 15.7
This is as a result of ByRef function that changed the value
of the original radius to the new value of radius used for the
calculation of the area at this line
Radius = Radius ^ 2
To correct this, ByVal can be added the Function Area code
as
“
Function Area(ByVal Radius As Double) As Double
”
Sub CalRadius()
Dim r As Double
r = 2.5
MsgBox "Radius of the circle is " & r
MsgBox "Area of the circle is " & Area(r)
MsgBox "Circumference of the circle is " & Circumference(r)
End Sub
Function Area(ByVal Radius As Double) As Double
Radius = Radius ^ 2
Area = WorksheetFunction.Pi * Radius
End Function
Function Circumference(Radius As Double) As Double
Circumference = 2 * WorksheetFunction.Pi * Radius
End Function
Each function in Excel VBA is expressed as Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat). The Find function searches for information within a specified range. The FindAll function simplifies the use of Find method by returning a range of all cells that match specified criteria. Understanding the syntax and usage of these functions can significantly enhance data searching and manipulation tasks in Excel VBA.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
IENG 490 LAB FIND/FINDNEXT AND BYREF/BYVAL BY RESEARCH ASSISTANT: ADEWALE ADESINA
FND FUNCTONS Generally, each of the function is expressed as .Find( What , After , LookIn , LookAt , SearchOrder , Sea rchDirection , MatchCase , MatchByte , SearchFormat ) Take note of the dot before Find , the meaning of this is that you will have to find something from somewhere.
That Somewhere is written before the dot. Ofcourse Somewhere to find a string or any Excel data type must be a collection of strings or other data types which is or must be definate. That is, it should have a specified boundaries (starting point and end point). Obviously, find function anticipates to look (in, at), after, search (order or with direction or certain format), match data (by case or character, byte) from a range of string of data type. Hence a range of data source must be specified with find function and a Range object represents the first cell where that information is found.
Hence a range of data source must be specified with find function and a Range object represents the first cell where that information is found. The decleration is Range.Find(Requiredfunction:=string/datatype,Opti onalfunction:=excelValues)
The FindAll function simplifies the use of the Find method. Function FindAll(What, Optional SearchWhat As Variant, _ Optional LookIn, _ Optional LookAt, _ Optional SearchOrder, _ Optional SearchDirection As XlSearchDirection = xlNext, _ Optional MatchCase As Boolean = False, _ Optional MatchByte, _ Optional SearchFormat) As Range The function returns a range consisting of all the cells that match the specified criteria. The table below presents the list of the required and optional functions
Sub Find( ) Dim SalaryWkSht As Worksheet Dim LastRow As Long, i As Long, c As Long Dim myCell As Range Set SalaryWkSht = Sheets("Sheet1") LastRow = SalaryWkSht.Range("A" & Rows.Count).End(xlUp).Row c = 12000 Set myCell = SalaryWkSht.Range("B1:B" & LastRow).Find(What:=c, LookIn:=xlValues) If Not myCell Is Nothing Then MsgBox "Value found in cell " & myCell.Address End If Exit Sub End Sub
Assumming the value to be found appears more than once in the cell, we need .FindNext function to find them within the cell. Sub FindNext() Dim c As Range On Error Resume Next With Worksheets(1).Range("B1:B500") Set c = .Find(10000, LookIn:=xlValues) If Not c Is Nothing Then firstaddress = c.Address Do MsgBox "Value found in cell " & c.Address Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstaddress End If End With End Sub
BYVAL AND BYREF FUNCTON A previously declared argument can by passed over or copied to another argument(s) by using By Value (ByVal) or By Reference (ByRef) Subfunction. ByRef passes a pointer to the variable, so any changes are reflected everywhere that variable is used. ByVal means that you are passing a copy of a variable to your Subroutine. Changes are made to the copy but the original will not be altered.
This implies that , in the case of objects, they are always passed as references (pointers) but if you pass an object ByVal you pass a copy of the pointer to the object rather than the original pointer (note: not a copy of the object) ByVal Public Function functionByVal(ByVal x) variant/datatype functionByVal = variant/datatype End Function ByRef Public Function functionByRef(ByRef x) variant/datatype functionByRef = variant/datatype End Function
Default argument Public Function functionByDefault(x) variant/datatype functionByDefault= variant/datatype End Function It should be noted that whenever, the default argument is used, ByRef is preset by the Excel VBA.
Example: Datatype Sub ieng490( ) Dim i As Long i = 1 Call Change_ByRef(i) ' i is changed by the Change_ByRef procedure and that change IS reflected here too because it was passed by reference MsgBox "i is now: " & i End Sub Sub Change_ByRef(ByRef lInput As Long) lInput = 14 End Sub
Sub ieng490ByVal ( ) Dim i As Long i = 1 Call Change_ByVal(i) ' i is changed only within the Change_ByVal procedure because a COPY of it was passed MsgBox "i is still: " & i End Sub Sub Change_ByVal(ByVal lInput As Long) lInput = 21 End Sub
Example: Object Sub Object_ieng490ByRef ( ) Dim j As Range Set j = Range("B1") Call Change_object_ByRef(j) ' j is changed to a different cell by the Change_ByRef procedure and ' that change IS reflected here too because it was passed by reference MsgBox "j is now: " & j.Address End Sub Sub Change_object_ByRef(ByRef jInput As Range) ' change the range variable to one row down Set jInput = jInput.Offset(1) End Sub
Sub Object_ieng490ByVal ( ) Dim j As Range Set j = Range("B1") Call Change_object_ByVal(j) ' r is changed to a different cell by the Change_ByVal procedure but ' that change IS NOT reflected here because it was passed by value. MsgBox "j is still: " & j.Address End Sub Sub Change_object_ByVal(ByVal jInput As Range) ' change the range variable to one row down Set jInput = jInput.Offset(1) End Sub
Data ByRef Sub MyHome ( ) Dim x As Double x = 5.005 MsgBox AddUp(x) End Sub Function AddUp(ByRef x As Double) As Double x = 10 + x End Function
Example 2: Data ByVal Sub MyHome( ) Dim x As Double x = 5.005 MsgBox AddUp(x) MsgBox x End Sub Function AddUp(ByVal x As Double) As Double AddUp = 10 + x End Function
CIRCLE RADIUS, AREA AND CIRCUMFERENCE Sub CalRadiusByRef() Dim r As Double r = 2.5 Radius = r MsgBox "Radius of the circle is " & r MsgBox "Area of the circle is " & Area(r) MsgBox "Circumference of the circle is " & Circumference(r) End Sub Function Area(Radius As Double) As Double Radius = Radius ^ 2 Area = WorksheetFunction.Pi * Radius End Function Function Circumference(Radius As Double) As Double Circumference = 2 * WorksheetFunction.Pi * Radius End Function
You will notice that the value of the circumference of 39.2669 obtained by the ByRef function is wrong The actual value for the circumference of a circle with radius of 2.5 is 15.7 This is as a result of ByRef function that changed the value of the original radius to the new value of radius used for the calculation of the area at this line Radius = Radius ^ 2 To correct this, ByVal can be added the Function Area code as Function Area(ByVal Radius As Double) As Double
Sub CalRadius() Dim r As Double r = 2.5 MsgBox "Radius of the circle is " & r MsgBox "Area of the circle is " & Area(r) MsgBox "Circumference of the circle is " & Circumference(r) End Sub Function Area(ByVal Radius As Double) As Double Radius = Radius ^ 2 Area = WorksheetFunction.Pi * Radius End Function Function Circumference(Radius As Double) As Double Circumference = 2 * WorksheetFunction.Pi * Radius End Function
