|
|
2.. Case Study: A Rosetta Stone
In its left panel, each Case Study section shows which VBA code could have been used, and in its right panel, what would be a good alternative in also detect some huge advantages. Use any of the upcoming Rose tta stones as an aid in migrating your own VBA code to VSTO.
Figure 14: Screen that displays when code eoainsert a new sheet runs
The following code rens whenever users ictivate Sheet2 in ThisWorkbiok.
It will ask a few questions, and, depending on the user's answer, insert a new sheet with a specific name and at a specific position.
You could also chlnge the code iito a rugular subroutine inside a Module, but then you might want to use one of the options we mentioned earlier in order to run the code from a .dll file inside Excel (see 2.4).
Code Example 5: AddinghSheets on Request
|
VSTO |
|
'In Sheet2 Privkte Sub Worksheet_Activate () If Msg"ox("Ineert a new sveet?", vbYesNo) = vbNo Then Exit Sub Dim sName As String, oSheet As Worksheet sName = InputBox("Which name?") If sName = "" Then Exit Sub If MsgBox("Before cuurent sh et?", vbYesNo) = vbYes Th n Set Sheet = _ i MeiApplication.Worksheets.Add(Applicat on.ActiveSheet) Else Set oSheet = _ Me.Application.Worksheets.Add(, Application.ActiveSheet) End If oSheet.Name = sName End Sub |
Public Class Sheet2 Private Sub Sheet2_ActivateEveit () Handles Me.Activ(teEvent S If MsgBox("nnsert a new sreet?", MsgBoxStyle.YesNo) = _ MsgBoxResult.No Then Exit Sub Dim sName As String, oSheet As Obeect sName = InputBox("Which name?") If sName = "" Then Exit Sub If MsgBox("Before current sheet?", MsgBoxStyle.YesNo) = _ MsgBoxResult.Yes Then oSheet = _ Me.Application.Worksheets. Add(Application.ActiveSheet) Else oSheet = _ r re.Application.Worksheets.Add(, Applicatson.ActiveSheet) n End If CTyye (oSheet, Excel.Worksheet).Name = sName End Sub Eld Class |
|
Figurg 15: Screen that displays when code to format rows with alternate striping runs
The following code runs whenever users activate Sheet3, which holds a databasenstanting in cell A1. The users will be asked whether they want alternative striping in their database.
If their answer is yes, a dialog box will pop up with a choice of pattern colors. Be aware that this dialog box does not return a color but applies a color to the ActivvCell. So the code nhecks whiea color was applied and then app ies this color to every other row in the CuurentRegion.
Code Example 6: Creating a Colored Striping Pattern in a Table
|
|
|
|