![]() ![]() What this says is that the myarray array variable should have its index number start at 1 and end at 3. You can just as easily store text with numbers in the same array variable. These two arrays, one to show you how to store text values and the other to show you how to store numbers are almost the same except that text must be surrounded with double quotation marks. Notice that the variables are not even declared at the top of the Macro (though that is good practice, it is not required here).ĭownload the accompany Excel spreadsheet from the side-menu or below this tutorial in order to test this and the following macros. These arrays are simply variables which use the Array() function in order to be turned into arrays. The array variables are myarray and mysecondarray. This is the simplest way to create an array variable in VBA and Macros for Excel. ' the option base.MsgBox myarray(0) & " " & mysecondarray(2) ' Get count of elements in an array regardless of Test = UBound(sourceArray, currentDimension)ĪrrayDimensionLength = currentDimension - 1 Public Function ArrayDimensionLength(sourceArray As Variant) As Integer ' Returns the length of the dimension of an array In my tests I used two helper functions: Arra圜ount, and ArrayDimensionLength. Public Function ArrayFromRange(ByVal target As Range) As VariantĪrrayFromRange = Application.Transpose( _ĪrrayFromRange = Application.Transpose(target.Value)Īs a bonus, here are the tests that I ran to check that this function works. ' not returning as an array, and when a 2 dimension array is returned ' Helper function that returns an array from a range with the Note, this uses the same technique of using the Application.Transpose method to reshape the array. This way we don't have to load the data into an intermediate array to determine the dimension.įor instance, if the target range is only one cell, then we know we want to return an array with the single value in it Array(target.value).īelow is the complete function that should deal with all cases. 'and in a vertical range of cells for a column.Īnother approach in creating a function for ArrayFromRange would be using the shape and size of the Range to determine how we should structure the array. 'To use values returned as an array, enter the Index function as an array formula in a horizontal range of cells for a row,_ SlicedArr = (arr, 1, 0) 'If you set row_num or column_num to 0 (zero), Index returns the array of values for the entire column or row, respectively._ 'convert two dimension array into a one dimension arrayĭim arr() As Variant, slicedArr() As Variant ' or a single variant value for range will only 1 cellĪdding to 's answer, below is the function I use to convert a row / column vector in a 1D array: Function convertVecToArr(ByVal rng As Range) As Variant ' inputValue will either be an variant array for ranges with more than 1 cell Public Function RangeToArray(inputRange As Range) As Variant()ĭim inputValue As Variant, outputArray() As Variant If that fails then it must be a single value so we'll create an array and assign the value to it. The function works by trying to find the upper bounds of the array. Works with single dimension and multi-dimensional arrays It is also more efficient that other answers as it will return the array generated by the range if possible. This answer improves on previous answers as it will return an array from a range no matter what the size. This function will turn the single value into an array (1 based, the same as the array's returned by ranges) Ranges will return an array unless the range is only 1 cell and then it returns a single value instead. This function returns an array regardless of the size of the range. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |