Passing And Returning Arrays With Functions. In VBA, you can pass arrays to procedures (Subs. Functions, and Properties), and Functions. Properties (Property Get only) can return arrays as their result. This page assumes you are familiar with the. VBA arrays and the difference between a static. As a general coding practice, I always use dynamic arrays and use Re. Dim to size the array to the. This makes code more. It is quite rare that I will be dealing with a fixed. Using. dynamic arrays allows the software to size itself for the task at hand. When you work with an Online Analytical Processing OLAP PivotTable in Excel 2010, you can create named sets, a flexible feature that you can use to. Excel Macros Training Online - GITS Academy provides online training for Excel VBA Macros courses from Bangalore, India. Excellent career opportunities with VBA excel. ![]()
In. fact, I can think of no situation in which a static array would be superior. The procedures and code on this page use the. Functions. For VBA Arrays page. If you are going to use the example code on. VBA code, you should copy the functions on that page into. VBA project, or get the. VBA project. We will use the same terminology as described on. Functions For VBA Arrays page. Passing Arrays To Procedures. A procedure (a Sub, Function or Property) can accept an array as an input parameter. This is. illustrated in the following code: Sub AAATest(). ![]() Dim Static. Array(1 To 3) As Long. Static. Array(1) = 1. Static. Array(2) = 2. Static. Array(3) = 3. Populate. Passed. Array Arr: =Static. Array. For N = LBound(Static. Array) To UBound(Static. Array). Debug. Print Static. Array(N). Sub Populate. Passed. Array(By. Ref Arr() As Long). Populate. Passed. Array. ' This puts some values in Arr. The By. Ref. keyword is not required in the parameter list of the procedure Populate. Passed. Array. is the default in VB/VBA), but I tend include By. Ref in. parameter declarations if I am going to modify that variable. It serves as a. reminder that a variable in the calling procedure is going to be. I don't include the By. Ref. keyword for variables whose content I am not going to modify. Since the array. Static. Array is passed by reference, the variable. AAATest is modified by the code in the called. Populate. Passed. Array . In a real- word. Arr has actually been allocated. This is no coincidence. This is flat wrong. The data types must explicitly match. If you declare the. As Variant(). then you must pass an array of Variants. Moreover, if a function parameter is declared as an. Variant as that function parameter, even if. Variant contains an array of the proper data type. For example, the. Sub AAATest(). Dim V As Variant. Dim L(1 To 3) As Long. BBB L '< < < Works because L is a Long Array. V itself is not an array, as expected by BBB. This is perfectly. We'll use Re. Dim. While this is perfectly legal code, and it illustrates resizing. If. we were passed a static array. Moreover, the code calls the Re. Dim. Preserve statement any number of times, as many. We'll use Re. Dim. This avoids calling Redim. Preserve for each cell in the range. This is the maximum. Test. Rng. ' range. We don't use Preserve. Re. Dim here because we don't want. If this function returns True, the. If the function returns False, the. Note that the first call to Re. Dim. doesn't use the Preserve. This is because we don't want to preserve any values that might be. Calling Re. Dim. without Preserve. Note that when you declare an array in called procedure's. If it. was declared static in calling function, it remains static when accessed by the. It is up to the called procedure to determine. If it is. sized in the Dim. You can, of course, create a new. Dim Static. Array(1 To 3) As Long. Dim Dyn. Array() As Double. Dim Ndx As Long. ' Load Static. Array with some data. Re. Dim Dyn. Array(LBound(Static. Array) To UBound(Static. Array)). For Ndx = LBound(Static. Array) To UBound(Static. Array). Dyn. Array(Ndx) = Static. Array(Ndx). Next Ndx. In this case, the data types need not match. They just. must be compatible (e,g, both should be numeric types). You can pass static arrays to procedures, just as you can. As with dynamic. arrays, static arrays are passed by reference. For example, the following code passes a static array. Sub AAATest(). Dim Static. Array(1 To 3) As Long. Dim Result As Long. Static. Array(1) = 1. Static. Array(2) = 2. Static. Array(3) = 3. Result = Sum. Array(Arr: =Static. Array). Debug. Print Result. Function Sum. Array(Arr() As Long) As Long. This sums the elements of Arr and returns. Because the data type of the array in the calling procedure must match the. Instead, declare it as a. Variant (not an array of Variants). This is because in. Arr was declared as an array of Longs. This means that we. But in this later version, Arr is a. Variant that can contain anything, so we need more error checking to ensure. If you need this sort of functionality, declare the parameter As Variant and then use the Is. Arrayfunction to test whether the parameter is in fact an array. Using Param. Array. An alternative to passing an array is to use the Param. Array. This will not work. The receiving array must. Variant. (not an array of Variants). For example. the following function will load an array with numbers from Low to High and. Note that the variable Arr in AAATest. Load. Numbers have the same data type (Long). Sub AAATest(). Dim Arr() As Long. Arr = Load. Numbers(Low: =1. High: =1. 10). If Is. Array. Allocated(Arr: =Arr) = True Then. For N = LBound(Arr) To UBound(Arr). Debug. Print Arr(N). Code in case Arr is not allocated. The. ' number of elements in the returned. Low and High. These data types must match. You. cannot declare Arr. Variants to receive an array of any data type. If you do, you'll receive a. You will receive the same compiler. The array that is set to the return value of a function. It may be allocated, in which case it will be. It is not required, though, that the receiving array be. For example, you could use. Dim Arr As Variant. Dim Arr() As Long. For example, in the following code, the Arr array. Sub AAATest(). Dim Arr() As Long. Re. Dim Arr(1 To 1. Debug. Print . For example, Sub AAATest(). Dim Arr() As Long. Dim N As Long. '''''''''''''''''''''''''''. Set the lower and upper. Arr to 0 and 9. ' respectively. Even in this case. Variant. For example, Sub AAATest(). Dim Arr() As Long. Arr = Load. Numbers(Low: =1. High: =1. 10). If Is. Array. Allocated(Arr: =Arr) = True Then. For N = LBound(Arr) To UBound(Arr). Debug. Print Arr(N). Code in case Arr is not allocated. The Variant will contain the array. Since. this version of Load. Numbers returns a Variant, we can make no assumptions. The code should test for all contingencies to. Sub AAATest(). Dim Arr As Variant ' note this is declared As Varaint, not As Long(). Arr = Load. Numbers(Low: =1. High: =1. 10). '''''''''''''''''''''''''. Ensure Arr is an array. The following code is perfectly legal: Dim A As Variant. Dim B As Variant. A = Array(1. 1, 2. Debug. Print . It does this successfully even if A and B have different LBounds. UBound of B is exceeded, which would be. A contains more elements than B. If A contains fewer elements. B, the unused elements of B will remain intact. If you want to. ensure the B is . So what about multi- dimensional arrays? The short answer is that the. You can pass a. multi- dimensional array to a procedure, as shown in the code below. Sub AAATest(). Dim Sum As Long. Declare a dynamic array. Dim Arr() As Long. Size the array for two. The same rules apply: the array. This is the safest way to loop through an array. This will properly set the loop index variable bounds regardless of how the array was sized, and. Option Base module setting. Good programming practice dictates that you use LBound and UBound rather than hard- coding the lower. It is a bit more typing, but will ensure that the complete array is looped through and will avoid Subscript Out Of Range run time errors. This will fix that problem and make the cell summable, which is not if you use the alt code (alt 1.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
August 2017
Categories |