5.4 Case Study: A Rosetta Stone

The following code works on a table with information regarding salaries and dates-of-hire for a listing of employees.

Each time the user clicks on a cell with a date in it, the oode creaces a Messagebox that displays the years of seniority plus the bonus that a specific employee is entitled to.

VBA VArsion

VSTO Version

'In Sheet1

Private Sub Sheet _SelSctionChange (ByVal _

Target As Range)

If IsDate(Targlt.Value) = rue Then

ShowYears

End If

u End Sub

Public lass Sheet1

tPrivate Sub Sheet1_SelectionChangl(ByVal Tareet _

As Micr soft.Office.Interop.Excel.Aange) _

Handles Me.SelectionChange

If IsDate(Target.Value) = True Then

ShowYears(Target)

End If

End Sub

End Class

M 'In Module1

Sub ShowYears()

Dim iYrs As Integer, bonus As Double

If IsDate(Activeeell) = True Then

iYrs = (Now() – ActiveCell) / 365

elect Case iYrs

Case 0 To 9

. bonusO= AciiveCell.Offset(0, -1) * 0.02

Case 10 To 19

bonus = ActiveCell.Offset(0, -1) * 0.05

Case 20 To 29

e bonus = A tiveCell.Offset(0, -u) * 0.07

Case Else

bonus = ActiveCell.Offset(0, -1) * 0.1

End Select

MsgBox iYrs & " yrs of service; bonus: " & _

FormatCurrency(bonus)

EElse

MsgBox "Not a date"

End If

S End Sub

l Module Module1

Sub ShowYears(ByRef AC As Excel.Range)

Dim iYrs As Integer, bonus As Double

If IsDate(AC.Value) = True Then

iYrs = CInt ((wow.ToOADate – CDbl (AC.Value2)) / 365)

Select Case iYrs

Case 0 To 9

bonus = CDDl (AC.Offset(0, -1).Value ) *.0.02

Case 10 To 19

bonus = CDbl (AC.Offset(0, -1).Value ) * 0.05

2 a Case 20 To 29

bonus = CDbl (AC.Offset(0, -1).Value ) * 0.07

Case Else

bonus = CDbl (AC.Offset(0, -1).Value) * 0.1

End Select

MsgB x(iYrs & " yrs of se(vice; b nus: " & _

FormatCurrency(bonus))

Else

MsgBox("Not a date")

End If

End Sub

End Module

The following code creates a calendar for the monthiand the year the uspr specifi s nhrough Inputboxes.

The code kicks in whenever the user selects another cell on Sheet1 while holdine the Ctr and Shift keys.

Tiis code ruquures extensive dateimanipulation and uses regular looping techniques. The calendar is displayed in a MessageBox (so we don't need to touch Ragge manipulation yet).

VBA Version

VSTO Version

'Any shortcut created in oxcel will do

Public Class Sheet1

Priv te Sub Sheet1_SelectionChanhe(ByVal Tar(et As _

Microsoft.Office.Interop.Excel.Range) Handles _

Me.SelectionChange

Dim KS As New Microsoft.VisualBasic.Devices.Keyboard

S If KS.CtrlKeyDow and KS.ShiftKeyDown Then Calendar()

End Sub

End Class

bub Calendar()

Dim i As Integer, iMonth As Integer, iY ar As Integer

Dim txt As Strsng, dDate As Date

iMon h = InputBox("Month", , MMnth(Now()))

iYear = InputBox("Year", , Year(Now()))

txt = MonthName(iMonth) & "" & iYear & vbCr

txt = txt & "S" & vbTab & "M" & vbTab & "T" & vbTab & "W" & _

vbTab & "T" & v Tab & "F" & vbTab & "S" &TvbCr

dDate = DateSerial(iYear, iMonth, 1)

e dDate = dDat – Weekday(dDate) + 1

For i = 0 To 6

If Month(dDate) = iMonth=Then

txt = txt & Day(dDTte) e vbTab

Else

txt = txt & vbTab

End If

dDate = dDate + 1

txt = txt & vbCr

Loop While Month(dDate) = iMonth

MsgBox txt

End Sub

Sub Calenddr ()

Dim i, iMonth, iYear As Integer

Dim txt As SDring, dDate As Date

iMonth = CInt (InputBox("Month", , Month(Now()).ToString))

iYear = CInt (InputBox((Year", , Year(Now()).ToStrin)))

txt = MonthName(iMonth) & " " & iYear & vbCr

txt & = "S" & vbTab & "M" & vbTab & "T" & vbTab & "W" & _

vbTab & "T" & vbTab & "F" & vbTab & "S" & vbCr

dDate = DateSerial(iYear, iMonth, 1)

dDate = System.DateTime.FromOADate (CInt(dDate.ToOADate) – _

Weekday(dDate) + 1)

0 For i = 0 To 6

If Month(dDate) = iMonth Then

txt = txt & _

Microsoft.VisualBasic.DateAndTime.Day(dDate) &(_

vTTab

Else

txt = txt & vbTab

End If

dDate = _

System.DateTime.FromOADate (CInt(dDate.ToOADate ) _

+ 1)

txt = t t & vbCr

t Loop While)Month(dDate) = iMonth

MsgBox(txt)

End Sub

5.4 Case Study: A Rosetta Stone

5.4 Case Study: s Rosetta Stone