Redim - Re-dimension An Array
Nov 16, 2007
Currently I am building a class to keep track of entries I have made during the macro execution. Thus far I have:
Private Type Memory
MemoryArray() As Variant
End Type
Private Sub Class_Initialize()
Redim MemoryArray(0) As Variant
End Sub
Public Property Let AddToMemory(Object As Variant)
'memory is empty
If UBound(MemoryArray) = 0 And MemoryArray(UBound(MemoryArray)) = "" Then
MemoryArray(0) = Object ..............
View 5 Replies
ADVERTISEMENT
Apr 28, 2014
I iterate through a list and store the data in an array.
[Code] ..........
This works fine, but I tried to have 2 dimensions to the array and It no longer works!
View 4 Replies
View Related
Aug 17, 2009
Using index, I know passing 0 as the row or column num returns the entire row or column.
So I have an array containing worksheets, and an associated column number:
View 3 Replies
View Related
Feb 21, 2007
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...........
View 3 Replies
View Related
Oct 9, 2007
I am trying to write a function that grabs the value of some cells in a column and returns a String array with those values. I am getting a compiler error on the line where I assign a value to an array element. It appears to be assuming that rather than an array reference, I am trying to do a recursive call to the function. The error is "Function call on left-hand side of the assignment must return Object or Variant." I would have done this with by assigning the range directly to the array but the data is in a column rather than a row; don't know of a more elegant way to do it.
Public Function projectList() As String()
Dim c As Range
Dim i As Long
For Each c In Range("FirstProject"). CurrentRegion
Redim Preserve projectList(UBound(projectList) + 1)
i = UBound(projectList)
projectList(i) = c.Value ' *** Compiler error occurs here ***
Next c
End Function
View 3 Replies
View Related
Sep 20, 2012
I have an array that is going to have a variable length every time the macro is run. I set a really high length at the beginning and when I want to unload the data, I want to redim the array to only the amount of data that was loaded into the array.
I am getting an error of "Array already dimensioned" when use the redim function near the end of my code below.
Code:
Private Sub Trend_Click()Dim GraphRow As LongDim TodaysYear As Integer,
Adjustment As Integer, EndYear As Integer,
StartYear As IntegerDim X_Axis_Array(1 To 100) As VariantDim SelectedStmt As StringDim X_Axis_ArrayEnd As IntegerTodays
[Code] ........
View 5 Replies
View Related
Apr 1, 2014
I've written this function to re-dimension an array based on the size of the range it is storing. Originally all my variables were simply declared as:
Code:
Dim XLRecOutput() as Variant
I then wrote a function which took the variable and the range as follows:
Code:
Function LoadRangeToArray(dataArray() As Variant, selectedRange As Range, Optional blnLoadData As Boolean = True)
With selectedRange
ReDim dataArray(1 To .Rows.Count, 1 To .Columns.Count)
End With
If blnLoadData Then dataArray = selectedRange
LoadRangeToArray = dataArray
End Function
To call the function, it's simply:
Code:
XLRecOutput = LoadRangeToArray(XLRecOutput, Range("XLRecOutput"))
Now I've created a class and set up Get/Let properties for the variables instead, but the above function fails with a Compile error on the call - 'Type mismatch: array or user-defined type expected' and I can't get my head around how to resolve it.
View 9 Replies
View Related
Jul 9, 2009
Passing an entire dimension of an array to a function....
View 13 Replies
View Related
Aug 23, 2012
Is there any way to do this part of code without calling out each element individually?:
VB:
Dim aSequence(7, 1) As Integer
'setup Evaluation sequence
aSequence(0, 0) = -1
[Code]......
View 2 Replies
View Related
Jul 20, 2009
I found quite a few posts about this problem, but none of the answers was any use to me. I need to redimension a 2 dimensional array in a Sub. I deleted all the code that is not of interest:
View 3 Replies
View Related
Aug 17, 2009
Can I redim an array that is held within an array? I have (for example) 3 worksheets, and from each one I want to load 2 columns into an array. However I also want (I think) those 3 arrays to be contained within one array so that I can step through them easily. The code below will not run, but might better show what I am wanting;
View 2 Replies
View Related
Apr 26, 2006
Does using ReDim to size an array also initialize all array elements to zero by default? If not, is there a quick way to autoinitialize all array elements to zero?
View 2 Replies
View Related
Aug 11, 2006
I want to have an array where each record contains a name and three numbers. My understanding is that Excel requires all element types within an array to be the same, and so I turned to a user-defined data type. But I don't seem to be able to create a dynamic array out of this type--when I try to ReDim Preserve it, I get an error saying that it's already defined. Can't I do that?
View 3 Replies
View Related
Oct 26, 2011
I just need to know that is it possible in excel that we could make box with length, width, height dimensions?
For example: we just enter 100mm is length, 70mm is width and 30mm is height.. so excel will make box according to this size
View 3 Replies
View Related
Oct 19, 2007
I have: ReDim X(3,1) as double. The code runs fine on my machine, but when I take it to another machine, the code gets stuck on that line.
View 3 Replies
View Related
Feb 26, 2010
I used the macro recorder for this:
ActiveSheet.PageSetup.PrintArea = "$A$1:$P$246"
How would I change this to use variables?
I need to set the print area to A( FR ) to P( LR )
Of course FR is first row, LR is last row
View 9 Replies
View Related
Feb 19, 2010
when opening the workbook with Excel 2010 beta I folllowing error:
Run-tim error '1004':
The specified dimension is not valid for the current chart type.
View 9 Replies
View Related
Oct 6, 2012
VB:
Sub CreateFlat()
Dim wsData As Worksheet
Dim wsNew As Worksheet
[Code].....
In 2006 posting, this code was presented to the Forum. It works perfectly for a one dimension Crosstab. I have 4 dimensions that I need copied to the output.
I have attempted to modify the code, but nothing appears to adjust the pull of more than the first column of dimension data.
see the attached spreadsheet for the Crosstab data and desired output.
View 8 Replies
View Related
Jul 30, 2012
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
View 1 Replies
View Related
Sep 9, 2012
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
View 6 Replies
View Related
Dec 20, 2013
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
View 4 Replies
View Related
Oct 2, 2008
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies
View Related
Apr 7, 2009
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
View 6 Replies
View Related
May 2, 2013
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
View 9 Replies
View Related
Aug 5, 2014
I want to pass an array to Offset in the "Height" parameter, without having to type the array.
{=MAX(SUBTOTAL(9,(OFFSET(A1,ROW(1:5),,{1,2,3,4,5}))))}
I can't seem to figure out how to build the {1,2,3,4,5}. I've tried another ROW(1:5) and have tried nesting that like N(ROW(1:5)) but nothing works.
How I can get the {1,2,3,4,5} without having to type it out (so that I can expand this to a larger list)??
View 8 Replies
View Related
Aug 26, 2009
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method
IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")
I then copy and paste 'values' and filter out the 'false' to get my final result.
This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.
This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.
View 6 Replies
View Related
Jun 21, 2014
wondering is there a VBA equivalent of --() in excel that turns trues and falses to 1's and 0's?
View 14 Replies
View Related
Nov 6, 2013
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
View 5 Replies
View Related
Nov 15, 2007
=CORREL(C1:C10,C12:C21) at H1
=CORREL(C1:C10,C23:C32) at H2
=CORREL(C1:C10,C34:C43) at H3
etc
can i have a macro that first array remain the same, and second array always 11 cells added. drag it down also can
View 9 Replies
View Related
Mar 22, 2007
I've tried to multiply each element in a 6x6 array by a similar 6x6 array, both on the same sheet, and it worked.(see Macro2 and attached xls file "Test").Then I got more ambitious and tried to do the multiplication from a standard array in sheet "TestA", with the result on the same sheet, by each array in sheet "TestB" and failed.How do I solve this problem? Pgualb PS:I'm using the R1C1 style.
Sub Macro2()
For y = 29 To 34
For x = 2 To 7
Cells(x, y) = Cells(x, y - 27) * Cells(x, y - 18)
Next x
Next y
End Sub
Sub Teste12()
'Multiplica matriz em TestB por matriz padrão em TestA com _
'resultado na matriz em TestA correspondente à matriz em TestB
'
Dim x, y As Integer
For y = 2 To 7.............
View 7 Replies
View Related
