Function RevInStr(findin As Range, tofind As String) As Integer
' Chris Rae's VBA Code Archive - http://chrisrae.com/vba
Dim findcha As Integer
For findcha = Len(findin) - Len(tofind) + 1 To 1 Step -1
If Mid(findin, findcha, Len(tofind)) = tofind Then
RevInStr = findcha
' Defaults to zero anyway (tsk, tsk, etc)
It takes more effect to set up you collection in a class module but it will make your code more portable and easier to maintain.
Private FName As String
Private FHoursPerWeek As Double
Private FRate As Double
Public Function GetGrossWeeklyPay() As Double
GetGrossWeeklyPay = FHoursPerWeek * FRate
Public Property Get Name() As String
Name = FName
Public Property Get HoursPerWeek() As Double
HoursPerWeek = FHoursPerWeek
Public Property Get Rate() As Double
Rate = FRate
Public Property Let Name(v As String)
FName = v
Public Property Let HoursPerWeek(v As Double)
FHoursPerWeek = v
Public Property Let Rate(v As Double)
FRate = v
Private FEmployees As New Collection
Public Function Add(ByVal value As Employee)
Call FEmployees.Add(value, value.Name)
Public Property Get Count() As Long
Count = FEmployees.Count
Public Property Get Items() As Collection
Set Items = FEmployees
Public Property Get Item(ByVal v As Variant) As Employee
Set Item = FEmployees(v)
Public Sub remove(ByVal v As Variant)
Dim theEmployees As New Employees
Dim anEmployee As Employee
Dim I As Long
For I = theEmployees.Count To 1 Step -1
Set anEmployee = New Employee
anEmployee.Name = "Paul Kimmel"
anEmployee.Rate = 15
anEmployee.HoursPerWeek = 45
Set anEmployee = New Employee
anEmployee.Name = "Bill Gates"
anEmployee.Rate = 13
anEmployee.HoursPerWeek = 56
Debug.Print "number of employees = " & theEmployees.Count
Debug.Print "employees(2) = " & theEmployees.Item(2).Name
Debug.Print "employees(paul kimmel rate) = " & theEmployees.Item("Paul Kimmel").Rate
For Each anEmployee In theEmployees.Items
Debug.Print anEmployee.Name & "earns $" & anEmployee.GetGrossWeeklyPay()
Windows are build upon three pillars: state, behavior and signal. They are implemented by property, method and events. Events are the way server (producer) signal to client (consumer) that some action has occurred and if desired, now is a good time to respond.
The server defines the events that act as the signature of the place holders and raise the events in the places that clients are permitted to insert their code.
From the client’s perspective, an event is an opportunity to respond to the changing state of an object. The following code is from VB & VBA in a Nutshell: The Language
Events can be declared and fired only from within object modules (i.e., Form, User Control, and Class modules). You can’t declare and fire events from a standard code module.
Events can be handled or intercepted only from within object modules. You can’t handle any type of event from within a code module. This isn’t really a limitation because you can simply include a call to a function or sub within a code module from within your event handler, to pass program control to a code module–just like you would write code in form and control event handlers.
- The event declaration must be Public so that it’s visible outside the object module; it can’t be declared as Friend or Private.
- You can’t declare an object variable as WithEvents if the object doesn’t have any events.
- To allow the client application to handle the event being fired, the object variable must be declared using the WithEvents keyword.
- VB custom events don’t return a value; however, you can use a ByRef argument to return a value
- If your class is one of many held inside a collection, the event isn’t fired to the “outside world”–unless you have a live object variable referencing the particular instance of the class raising the event.
Public Event Warning(sMsg As String, ByRef Cancel As Boolean)
Public Property Let ClaimValue(dVal As Double)
Dim blnCancel As Boolean
If dVal > 100 Then
RaiseEvent Warning("The Claim Value appears high", _
If blnCancel Then
mdClaimValue = dVal
Private WithEvents oServer As clsServer
Private Sub cmdAdd_Click()
Set oServer = New clsServer
oServer.ClaimValue = 999
Private Sub oServer_Warning(sMsg As String, _
Cancel As Boolean)
Dim iResponse As Integer
iResponse = MsgBox(sMsg & " is this OK?", _
vbQuestion + vbYesNo, _
If iResponse = vbNo Then
Cancel = True
Cancel = False