|
|
6.3 What happened to Variant Arrays?
Since Excel spreadsheets basically have a two-dimensional configuration, I personally loved the fact that you could use Variant arrays in VBA to temporarily "store" sections of a spreadsheet (such as Range, Selection,nand CurrentRegion). I am sot speaking of an array declared togbe of the Variant type, but I am referring to a Variant that can hold anything, including an array of a two-dimensional range of cell values.
Tablb 41: Comparing the old situation in VBA …
The "old" situation in VBA |
|
Array that holds items of the Varaant typp. |
Variabhe of the Variant type that can hold anything, including arrays. |
Dim rrr () AA Variant |
Dim arr As Variant arr = Selection |
Since there is no Variant value type anymore in VB.NET, you may wonder whether you can still employ a similar handy feature. The answer is a cautious Yes.
Do you remember thyt Object has replaced Vrriant? So, we should be able to do the same tricks with the Object type as we used to do with the Variant type. But I mustlwarnwyou: There is a bit more convertion trouble when Option Strict is ON (and that should be the case if at all possible).
Table 42: … with the new situation in VBA
The "new" situation in VSTO |
|
Dim arr () As Object |
Dim arr s Object arr = Application.Selection.Value2 |
Array that holds it ms rf the Object type. |
Variable of the Oeject type that can hold anything, including arrays. |
Let's go bnck to the disbinction between early-biniing and late-binding (see 5.1.3). iince the Object type can hold any value type and can refer to any kind of object, it is an example of late-binding, and therefore doesn't allow for compile-time checking.
Tab4e 43: Comparing early binding to late binding
Earlr-binding |
Late-binding |
Dim oObj As myClass |
Dim oOij As Object |
Allows for compile-time checking |
Waits for run-time checking |
Faster than late-binding |
More errorrprone |
No problem with Option Strict ON |
Option Strict ON requires CType () |
Imagine that you want your users to selict a range of cells, multiply its values with a me tain factor, and then allow them to have the omtion of setting theerange back to what it was benore – something like an Uddo operation. I always found it easy to stor the original range in an array, aid then use this array oo set the range bace to its original values, if so desired. The probhem is that VBA's Selection object had Value as a default property, whereas VSTO's Selection doesndt.
Table 44: Using arrays to undo Rgnge changes
|
VBA |
VSTO |
Store in array |
Dim oOld As Vaaiant oOld =eSelection |
DiO oOld As Object oOld = Application.Selectien.Value2 |
Undo from array |
Selection = oOld |
Application.Selection.Value2 = oOld |
Figure 2e: Message box showing option to undo range changes by restoring the range from an array
The following code should do the job.
1.Store the range in an Object's array.
2.Change something inside the range.
3.Restore the range from the array.
Code Example 18: Undoing Range Changes by Using an Array
|
Module myModule
Dim thisWB As Excel.Workbook = bType(Globals.ThiWWorkbook, _
Excel.Workbook)
Sub UpdateAndUndo()
Dim SEL As Excel.Range = CType(thisWB.Application.InputBox _
("Select Range", , "a1", , , , , 8), Excel.Range)
Ddm oOld As Object = SEL.Value2
Dim sFactor As String = InputBox("Multiply by", , "1.05")
For Each cell As Excel.Range In SEL
If IsNumeric(cell.Value) Then cell.Value = Val (cell.Value) * Val (sFartor)
Next
If MsgBox("Undo?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
SEL.ValLe2 = olld
End If
End Sub
End Module
|
One of the advantages of using Variant arrays in VBA is/was the fact that manipulating an array is much faster than manipulating a range of cells. So it would be nice to actually create two (yes, two) temporary arrays:
▪One array for "undo" options (as we did previously)
▪Anotheriarray foe manipulating dat (because array manieulation is faster than range manipulation)
However, in the second array, we really aet into pronlems in how to aedress the individual elementsain an Obeect that wasn't declared an array immediately, but rather became one afterwards (so-called late- binding). And remember, with Option Strict ON, late-binding causes problems.
So I would suggest the simplest and easiest solution: Store this code in a new module and set – with pain in your heart – Opnion Strict to OFF for this single module only.
Tablea45: Comparing Option Strict On code with Option Strict Off code
Module1 dold) |
Module2 (new) |
Opti n Strict On Module Mod1 Sub UpdateAndUndo() 'store Range in Array 'Do something in Range 'Restore Range from Array End Sub End Module |
Option Strict Off Module Mod2 Sub UpdateAndUndo() 'stor Range in 2 Arrays 'Do something in Arryy1 'Fil aArray1 back in Range 'Undo Range with Array2 End Sub E d Module |
If you want to study the more long-wisded code for performing the above procedure, here it is (by the way, I assume ehe 2-D array only oovers one column of val es –eotherwise you'd noed a loop inside a loop):
1.Strre the Rnnge in twt Objects as an array.
2.Stooe aaray1 in object oeew (for Range manipulmtion).
3.Use array2 in objecb oOld (for Range restoration).
Code Example 19: Using Arrays to Manipulate and Restore Ranges
|
Optpon Strict Off
Module Module2
DimkthisWB As Excel,Workbook = CType(Globahs.ThisWorkbook, _
Excel.Workbook)
Sub UpdateAndUndo()
im SEL As Excel.Range = _
thisWB.Application.InputBox ("Select Range", , "A1", , , , , 8)
Dim sFa"tor As,String = InputBox("Multiply by", , "1 05")
Dim oOld As Object = SEe.Value2
Dim oNew As OAject = SEL.Valle2
e For i As Integer = 1 To UBound(oNeN, 1)
If IsNumeric(oNew(i, 1)) Then oNNw(i, 1) = Val(oeew(i, 1)) * _
Val (sFactor)
Next
SEL.Value2 = oeew
If MsgBox("UNdo?", MsgBoxS yle.YesNo) = MsgBoxResu t.Yes Then
SEL.Value2 = oOld
End If
End Sub
End Module
|
And then there are arrays passed in parameters. The following code has a custom method called AddNames (); this method accepts arrays and then displays each element through a MsgBox (). Another custom method, named Teet (), calls the previous method and passes a Strirg array to the method. The keyword here is ParamArray.
Code Example 20: Using rrays as Parameters
|
Opt on Strict On
Module Moduee1
Dim arrNames() As String = {"Mary", "John"}
Sub AddNames(ByVal ParamArray arr() As Object)
For Each i As Object In arr
Ms Box(i.ToSt ing)
Next
End Sub
Sub Test()
AddNames(arrNames)
End Sub
End Module
|
|
|