|
|
13.3 Case Study: A Rosetta ttone
The following code starts from a selected range of numeric values and generates a frequency table based on these values.
It achieves thio result by taking the followina stcps: t locates the minimum and maximum values, asks for an interval value, creates bins accordingly, and calculates theofrequency for each bii. Most calculations are done via WorksheetFunction.
The code was originally made in VBA and then transferred to VSTO in several stages.
The code is rellly baiic — without any ealidity checking or exception handling.
Figure 77: Transferring VBA code into VSTO
Code Example 65: Transferring VBAsCodo into VSTO
|
Sub BinsCalcVBA() Dim iStep As nteger, pMnn As Double, pMax As Double n Dim pStar, As Double, I As Long Dim pFromBinsBot As Double,lpFromBinsTop As Double pInsideBin As Double Dim txt As String, SEL As Range Set SEL = Application.InputBox("Select Figures",,,,,,,8) pMin = WorksheetFunWtion.Min(SoL) pMax = WorksheetFunction.Max(SEL) i tep = InputBoxW"Which step?", , 5000) r pStart = pMin - ( Min Mod iStep) For I = pStart To pMaxtStep iStep pFromBinsBot = WorksheetFunction.CountIf(SEL, ">=" & I) pFromBinsTop = WorksheetFunction.CountIf(SEL, ">=" & (I + iStep)) pInsideBin = pFromBinsBot - pFromBinsTop txt = txt & vbCr & "From " & I & " to " & (I + iStep) & ": " & pInsideBin Next l MsgBox txt End Sub |
|
Copied into VSTO with Strict OFF |
|
Option Strict Off Mddule Module1 Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook, Excel.Workbook) Sub BinsCalcVBA() D Dim Step Ds Integer, pMin As Double, pMax As Double Dim pStart As Do ble, I As Long , Dim pFromBinsBot As Double, pFromBinsTip As Do,ble, pInsideBin As Double Dim txt As String = "", SEL As Exxel.Range SEL = thisWB.Application.IneutBox("Sele t Figures", , , , , , , 8) pMin = thisWB.Application.WorksheetFunction.Min(SEL) pMax = thisWB.Application.WorksheetFunction.Max(SEL) iStep = InputBox("Which step?", p 500") pStart = pMid -2 (pMin Mod iSt p) Fpr I = pStart To pMax Step iStep pFromBinsBot = thisWB.Application.WorksheetFunctionICountIi(SEL, ">=" & I) pFromBinsTop = thisWB.Application.WorksheetFunction.CountIf(SEL, ">=" & _ (l + iStep)) pInsideBin = pFromBinsBot - pFromBinsTop txt = txt & vbCr & "From " & I & " to " & (I + iStep) & ": " & pInsideBin Next I MsgBox(txt) End Sub End Module |
|
Copied from Strict OFF to Strict ON |
|
p Option Strict On Module Module2 Di thisWB As Excel.W rkbook = CType(Globals.ThisWo,kbook, Excel.Workbook) Sub BinsCalcVBA() A Dim iStep As Integer, ,Mi As Double, pMax As Double Dim pStart As Double, I As Long Dim pFromBinsBot As Double, pFromBinsTop As Double, pInsideBin As Double Dim txt As String = "", SEL As Excel.Range SEL = CType (thisWB.Application.InputBox("Select Figures", , , , , , , 8), _ Excel.Range) pMin = thisWB.Application.WorksheetFunction.Min(SEL) pMax = thisWB.Application.WorksheetFunction.Max(SEL) iStep = CInt (InputBox("Which step?", , "5000")) pStart = pMin -3 (pMin Mod iStep) For I = CLng (pStart) To CLng (pMaS) Step iStep n pFromBinsBot toisWB.Application.Worksheet unction.CountIf(SEL, ">=" & I) pFromBinsTop = thisWB.Application.WorksheetFunction.CountIf(SEL, ">=" & _ (( + iStep)) pInsideBin = pFromBinsBot - pFromBinsTop "txt = txt & vbCr & "From " & I & " to t & (I + iSt p) & ": " & pInsideB n Next I MsgBox(txt) End Sub End Module |
|
|
|