If I have say several hundred elements stored in an indexed array and I want to clear them each time the sub or function holding them is called, is there a way to erase the contents of the entire array without having to loop through each element. Something like array().clear? Note that each time the sub is called, they are also redimed to some reference.
The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time,
The code I have is:
Sub test() Dim vSheetColours As Variant Dim iCounter As Integer vSheetColours = Range("Colours").Interior.ColorIndex For iCounter = 1 To UBound(vSheetColours, 1) MsgBox vSheetColours(iCounter, 1) Next iCounter End Sub
(Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!)
Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours.
If I run this code, I get a runtime error 13 type mismatch, and it highlights For iCounter = 1 To UBound(vSheetColours, 1)
But if I replace vSheetColours = Range("Colours").Interior.ColorIndex with vSheetColours = Range("Colours").Value
Is it possible to use an array without looping through it? I have code that has two loops: One that is just for the array and the other that loops through the data. It would be nice if I could get it down to one loop, but I'm not sure if it's possible. For example, if you have Dim varArea As Variant
varArea = Array(1,2,4,6,8,12)
For x = LBound(varArea) To UBound(VarArea) Do While Not C Is Nothing Code here Is there a way to advance to the next item in the array without using the loop.
I am using strongly typed code, but I am also pulling data from worksheets into variants. e.g.
Dim MyArray As Variant
MyArray = [MyNamedRange]
is there any shortcut to convert the variant to a typed array, which doesn't just involve looping throught the variant and using a casting function, e.g.
I am trying to develop some code to serve as an 'undo' for several macros that I have. They each take a selected range and perform some changes to that range. I have managed to make some code that will undo the last macro run but would like to make something that can go back several steps. To that end, I have started with the following code but am running into an issue when I need to use a variant array to hold multiple arrays of a custom defined data type:
VB: Type SaveRange Val As Variant Addr As String
[Code] .....
This declares some public variables I'll need, each as an array so that I can iterate through several steps of do/undo using the undoIndex. I then use them to save each range I am about to change by calling the following macro within my actual data-manipulating macros:
VB: Sub Save_RangeForUndo(rng As Range) Dim i As Integer Dim cell As Range
undoIndex = undoIndex + 1
[Code] .....
If you look at the last line here, this is my problem; I can't figure out how to properly use an array to hold each instance of OldSelection() for later referral. Just for completeness, here is my actual undo macro:
VB: Sub Undo_Operation() Dim i As Integer OldWorkbooks(undoIndex).Activate OldWorksheets(undoIndex).Activate
[Code] .....
I need to get this OldSlctVariant()() array to hold each instance of OldSelection() so that I can restore them for each consecutive undo. I'm not very familiar with the variant type and anything more than the basic use of arrays.
For i = LBound(arrXl) To UBound(arrXl) MsgBox (LBound(arrXl)) 'If 1st char is different from 3rd char then remove all of string after 1st char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then arrXl(i) = Left(arrXl(i), 1) End If 'If 1st char is different from 7th char then delete string after 5th char If Left(arrXl(i), 1) <> Mid(arrXl(i), 7, 1) Then arrXl(i) = Left(arrXl(i), 5) End If
The lbound function returns the value of 1 as the lower bound, I do not have "Option base 1" set so I was expecting the lbound value to be 0. The first 2 cells in the F column are blank so this may have something to do with it, I am unsure if cells in excel can be null if they can be null one cell may be null and the other may be a zero length string but I am unsure about this.
The ubound function returns a value of 487.
The code breaks when I try to access an element in the array so it breaks on the line:
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
and returns the "Subscript out of range" error message.
I have a STRING array which is dimensioned with 100 elements possible. What i want to know is how to determine the "length" (not sure if this is the right word) of the array. For example: Suppose i have 10 strings in the array. Is there a command to determine that there are only 10 elements in the 100 possilble array?
I can't seem to get my head around the simple issue of assigning a range of cell data to an array! Here is a snippet of what I have, maybe you can see the flaw:
I have a question regarding arrays. If I have too many elements in a 1D array(let's say 1000), how can I list all of them in a msg box (separated by comma)?
I am facing the some issue while tried to display the data from array. Below is code I have to tried to display the data from array.
Scenario: I have assigned the few values in my excel to an array and tried to display the data stored in array
Sub Main() Dim Rows_Array() As Variant Dim i As Integer, Sheets("EmployeeWise").Select Rows_Array = Range("C6:C" & Range("C65536").End(xlUp).Row).Value For i = 1 To UBound(Rows_Array()) MsgBox Rows_Array(i) Next End Sub
I am trying to present a 10X10 Matrix Range (A1:J10) full with UNIQUE INTEGER random numbers. I thought to use an Single Dimension Array (100 deep) and fill it with the Rnd() Function.
Then, I thought to check the RANK of each element of that Array and transfer it to 100 cells (10X10) in the Sheet (assume A21:J30) with the help of 2 nested loops.
My problem is: How to find the Rank of each element within ARRAY1. Is there a way to refer to an Array as to a Range in a Worksheet. (I do not want to transfer 100 values from the Array to the Sheet - I rather prefer to check the Rank WITHIN(!) the Array).
Option Base 1
Sub MiKe() Set AWF = Application.WorksheetFunction H = 10 V = 10 Redim Array1(H * V) For CL = 1 To H * V Array1(CL) = Rnd() Next For HC = 1 To H For VC = 1 To V.........................
Join an array with Text elements to create a string that can be Evaluated
So for instance if I have Array("A", "B", "C") and I want to evaluate("=({" & Join(array, ",") & "})="A)"). Is there any way to do this without having to loop or push to a Named array first? I'll even take this evaluate thing if I can do it with text and numbers
I've defined a string array and would want to use it as a basis for a vlookup. Is it possible to find the elements of the array directly in the worksheet ?So far I've got :
In the first loop that executes this command TAG_RANGE gets set to $A$1:$A$39
I want to loop through the values in that range and run tests against them. Is there a function that will take the values in the address range an convert them into an array so that I can use something like this:
Code: For Counter = LBound(TAG_RANGE_ARRAY) To UBound(TAG_RANGE_ARRAY)
[run tests]
Next
Or is there someother direct way to do this other than creating a loop that fills the array element by element
I'm trying to write a macro which will find members of an array on a sheet, highlight the column and then change the format of the column, what I have at the moment is:
I have a cell that contains parts that are comma separated. I want to assign each of these parts to an element in an array so I can process them using a loop function.
How can I count the number of elements of a particular dimension of an array variable that have actually been filled with items/values?
For example, the array variable in this procedure has two dimensions. Dimension 1 has three elements, and dimension 2 has 5 elements. I then add values to some, but not all the defined elements of dimensions. How can I count, for each dimension, the number of elements that have values rather than are empty?
I have an array of data type Variant, who's elements are workbooks opened by a user.
The array size is static, which for now isn't a concern but I can't work out how to close the workbooks in the array via a loop and the usual vba code of Workbooks("file").Close
Code I have that doesn't work is:
Sub Close_Workbooks_In_An_Array ()
Dim dFile (1 to 6) As Variant Dim i As Integer, j As Integer
' // Some code to open files, set each dFile(i) as a file and then process ' // them. Max value for i is 6
j = 1 For j = 1 To i MsgBox ("Closing: " & vbNewLine & vbNewLine & dFile(j)) Workbook.(dFile(j)).Close Next j
I am trying to compare the values in an array with the values in a range of cells. It worked fine until I moved the code into a sub. The specific problem that I am having is that is pulling cell values from "shippablegoodspriorday" and it is supposed to be pulling the cell values from "shippablegoods".
I am trying to figure out a method for calling the ith number in an array that was defined in another function. The mean function is working and the result is (1 x variables) array. Then, I want to use that array in the sdev function. I am having trouble pulling the ith number from the mean function. Also, is there a way to make variables constant so that they do not need to be declared for every function.
Sub stats() periods = Range("periods") variables = Range("variables") Redim X(periods, variables) As Double Redim uX(variables) As Double Redim sdX(variables) As Double 'Load Data For i = 1 To variables For t = 1 To periods X(t, i) = Cells(4 + t, 2 + i) Next Next 'Calculate Mean (run 'mean' function') uX() = mean(X)................................
I am trying to pass a string array into a form. I have added a member string array to the form, and a property to "Let" the array in the the member array.
Private sFormString() As String
Property Let FormString(value() As String) sFormString = value End Property
I can pass a string in using a procedure:
Sub StringArrayTest1()
Dim TestString() As String Dim frmString As FString
but I cannot "modulate" the code, or else I get an internal error (error 51). I.e. this code doesn't work:
Sub StringArrayTest2Mod(TestString() As String, frmString As FString)
frmString.FormString = TestString
End Sub
Sub StringArrayTest2()
Dim TestString() As String Dim frmString As FString
Set frmString = New FString Redim TestString(1 To 2) TestString(1) = "Cat" TestString(2) = "Dog"
Call StringArrayTest2Mod(TestString, frmString)
End Sub
Does anyone know why this happens? Obviously, in the example code its not an issue, but the application I'm using this for is more complex, and some modulation here would be good.
which i am using to map columns between spreadsheets. The basic numeric entries refer to columns to copy. But I want to make the routine smarter with the strings.
If array(x) = 12 (For example) Then Do something End If If left(array(x),1) = "=" Then Do something Else End If If array(x) Is String literal (e.g "xyz" Or "123") Then Do a third thing End If
i cant find a typeof or isstring kind of function. Isnumeric works ok for some values but quoted numbers (eg "123") return true (which isnt what i want). I have tried the left(string,1) = """ but excel seems to hide the quotes.
Secondly, I am struggling to find a solution to something that in PHP, Java and javascript is painfully simple! That is - checking to see if an array contains no elements.
I'm trying to write a function that I can pass an array along with the data I want added to that array. The function will resize the array, put the data into it and return the new array. Example:
Function AddNewDataToArray(MyArray As Variant, Arg1 As String, Arg2 As String, Arg3 As String) As Variant 'If no elements have been added Redim Preserve MyArray(1 To 3, 1 To 1) 'Add the first record of data to the array.
'If there is already data in the array Redim Preserve MyArray(1 To 3, 1 To UBound(MyArray, 2)) 'Add the next record of data to the array End Function
As 2 dimensional arrays are Base 1, the challenge I have is getting it to resize (redim preserve) the array when it has to add the first record. I thought I could just use an IsArray function to test if there had been any data added. If not, then it would run the following...........
