Redim Array Function With Pre-Dimensioned Arrays?

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


ADVERTISEMENT

Redim Function - Array Already Dimensioned Error

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

Function To Return Single Dimensioned Array

Jan 3, 2008

I am trying to write a function that returns a single dimension array from inputed data. I want this to work for any data, i.e. a single cell, an array in vba or a range. I am using the "for each" staement. However, when I pass an array in the "for each" returns the same range, rather than the elements of the range. I've played around a bit, and the "for each" does what I want on an range if I am not passing the range to a function. Is it because I am passing the range into a function as a variant? Here is the code for the function.

Public Function CreateSingleDimensionArray(ByVal dataToConvert) As Variant

Dim vHolder As Variant
Dim vArray As Variant
Dim lElementCount As Long

lElementCount = 0
For Each vHolder In dataToConvert
lElementCount = lElementCount + 1
Redim vArray(1 To lElementCount)
vArray(lElementCount) = vHolder
Next vHolder
CreateSingleDimensionArray = vArray

End Function

View 4 Replies View Related

Use SUM Function With Two Arrays / One Array To Be Negative Value?

May 12, 2014

I am trying to subtract two values, which are calculated from arrays. Here is my actual code:

[Code] ......

If I remove the "-", I get the sum of both arrays as expected. If I include the "-", I get the "#VALUE!" error message.

View 4 Replies View Related

Redim Array 2 Dimensions

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

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 View Related

Dynamic Array Redim Preserve: Run-time Error 9, Subscript Out Of Range.

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

Redim An Array Within An Array

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

Populate 2D Array From Other 2D Arrays

Jul 21, 2014

I have read in 2 blocks of data as 2D arrays, and wish to create a third 2D array from some of the elements of the first two. When I do this I get an output range that is the correct size, however each row of data in the range is identical. This is what I am using:

[Code] .....

So my main concern is how to get each row of data being printed to Sheet2 to be the correct rows, and not just one row repeated thousands of times.

View 1 Replies View Related

Combine Arrays Into One Array

Apr 29, 2007

an array formula to combine the array formulas in columns B, C, and D? I would like the results displayed in one cell....

View 9 Replies View Related

Variant Array Of Arrays Of Differing Size

Jan 24, 2013

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.

View 7 Replies View Related

Sum Of Products With Non Parallel Arrays With 1 Array In Reverse Order

May 31, 2014

I need to calculate a Sums of Products with unusual requirements. Please refer to the attached picture. The Orange Cells under "Noise Weight" are the Formula Cells. The Values in those Cells need to evaluate as shown in the equations below. I want this to be dynamic, so that if I expand the FM Region (Add Columns between "Noise Weight" and "FM15") AND expand the Severity Region (Add Rows between Severity and Noise Weight), I will not have to update Formulas manually. I know for sure that the standard SUMPRODUCT Function cannot handle this.

PRODUCT 1 = D29*E31 + D28*F31+D27*G31.....+D15*S31
PRODUCT 2 = D29*E32 + D28*F32+D27*G32.....+D15*S32
PRODUCT 3 = D29*E33 + D28*F33+D27*G33.....+D15*S33
...
....
PRODUCT 7 = D29*E37 + D28*F37+D27*G37.....+D15*S37

View 11 Replies View Related

Summing The Multiplication Of One Array Times Multiple Arrays

Jul 24, 2008

I am trying to multiply one array of prices for multiple input products across
the volume that that product my go into multiple end products.

I know you can use SUMPRODUCT with two arrays that are equal BUT I want to Multiply the price matrix across 15 or 16 other matrices and sum all the products.
The price matrix and the other arrays are all 1 x "whatever".

View 9 Replies View Related

INDEX/MATCH Function To Retrieve Vlaues From A Table And AVERAGE Them (arrays)

Mar 26, 2009

I have 2 sheets recording feedback and summarising the overall percentages for each trainer.

My boss wants me to do this by month!?!?!

I have created a third sheet but I am not sure how to do the following:
Look up the trainer name
Look up the month
identify the percentages for each category
create an overall average of these percentages

So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.

View 7 Replies View Related

Slicing And Dicing CSV Files - Involves Arrays And Jagged Arrays

May 8, 2013

I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:

|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|

.
. extending until the end of the data set
.

I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:

Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub

View 1 Replies View Related

Does ReDim Autoinitialize To 0?

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

Redim Custom Types

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

ReDim Vs. Dim: Code On Another Machine Not Working

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

Array Concatenate Function VBA

Aug 8, 2012

I use a existing code and want to add the Concatenate in the code.

VB:
Sub Subtotal()

Dim myAreas As Areas, myArea As Range, x As String
Dim y As Long

Worksheets("INVOICE_hulp").Activate
Range("Q1").Select

[Code] ....

The array sum function works but Concatenate not. In Excel I get the formula Concatenate(H2:H3) in stead of Concatenate(H2,H3). What should I change to get the code working?

View 9 Replies View Related

Printing Array From Function?

Sep 3, 2012

I want to create a function in a module that will read an array from sheet1 preform an operation on it then return the result to the spreadsheet in a defined amount of cells in this example 8.

In Module1:

VB:
Function func1(ByRef arrayA())
Dim arrayB(8)
For k = 0 To 7

[Code].....

How does the resultant arrayB get populated onto the spreadsheet?

View 5 Replies View Related

Using Countif Function For Certain Array

May 2, 2014

I want to use the countif function for a certain array. The range is set by another cell which is made up out of a percentile of an entire row.

The problem is is that excel doesn't see the value it displays so i continuously get a value of 0 in the countif cell. If i fill in the range by hand, which is exactly the same range as the outcome of the percentile the countif cell does give the correct value.

So in short the problem is i guess that the countif cell does not recognize the value because this value is made up by a formula.

View 8 Replies View Related

Function That Returns The First Value In An Array

Jan 24, 2007

I am working on a Monthly Vehicle Spread Sheet. One of the outputs I am trying to achieve is an automatic calculation of Mile Per Gallon. To do this, I need to know if there is a function that will return the value of the first entry of a group of cells.

In calculating the miles per gallon, I need to subtract the first gallon amount entry of the total gallons in the month, then divide that number into the difference of the mileage in the month recorded when the vehicles fueled up.

View 11 Replies View Related

MINIMUM IF Function Without Using Array

Oct 15, 2008

Is there anyway I can do the below formula without it being an array formula?

View 2 Replies View Related

Assigning A Function To Array?

Feb 21, 2014

I am trying to write a code for simulating a stock price using geometric brownian motion. I have the function part down as follows:

Public Function SimStock(Initial_Stock_Price As Double, _
Expected_Return As Double, _
Volitility As Double, _
End_Time_Days As Double, _
Number_of_Steps) As Double
SimStock = Initial_Stock_Price * Exp((Expected_Return - Volitility ^ 2 / 2) * (Number_of_Steps / End_Time_Days) * NRnd2() * Sqr((Number_of_Steps / End_Time_Days)))
End Function

Now what i need to do is to apply this to an array. so that when i run the sub it will simulate prices from today up to some number of days that i specify. This number of days will be equal to "End_Time_Days" which is part of my function. Furthermore the simulation has to be such that it uses the above formula for the first entry but then replaces "Initial_Stock_Price" with the result of the preceding entry in each subsequent entry.

View 1 Replies View Related

MEDIAN() As Array Function?

Oct 11, 2005

Is it possible to submit the MEDIAN() function as an array (ctrl + shift + enter)?

I.e., I would like to submit a function similar to this SUM() function:
{=SUM(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}

However, when I submit

{=MEDIAN(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}

it does not give me the intended result.

View 6 Replies View Related

Sum Function For Array Not Working

Feb 27, 2012

For some reason my array called "PriceSum" is not getting load with values or not being summed. I am not sure if it is because I am trying to load a value with a decimal in it or not.

For logic purposes:

lastrow = 2
Stock = Banking
Cell "W2" = 5
Cell "X2" = 71.84
Cell "U2" = Bought
Cell "V2" = Banking

Code:
Private Sub Purchase_Click()Dim MyArray As Variant
Dim x As Integer
Dim t As Integer
Dim lastrow As Integer
Dim QuantityArray(0 To 1000) As Variant,

[Code] ...........

View 9 Replies View Related

Looping If Function Through Array?

Jan 9, 2014

I have a big macro that runs whenever there is a change made. I'm not gonna link it all because I don't think that would be useful... This macro does:

1. Disables Events.
2. Removes Protection
3.Calls some macros that check a few values and change a few cells.
4. Activates events.
5. Hides or shows ranges of cells depending on the results of the called macros.
6. Reactivates protection.

As well as the cells the called macro changes, there is a column of True/False results stretching from AE16 to AE491 with several blanks in the list as well. (All of this is decided upon a different macro that isn't linked with the change.) I want a loop that will read the True/False column and if it finds a false, make 8 buttons invisible.

I tried putting this code between Step 5 and 6 of the macro.


Code:
Dim J As Integer
J = 16
Do

[Code].....

This isn't working at all... I was thinking if there was gonna be an issue then it would be the opposite where it stops the buttons appearing all together but even when there are falses and I am changing things it doesn't seem to make a difference.

View 3 Replies View Related

Using Large Function In An Array

Jun 8, 2007

Column C contains the names of real-estate brokers.

Column E contains the colors of the homes for sale.

Column Q contains the values of the homes.

I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21.

I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.

View 9 Replies View Related

Usually Array Formula But Function

Oct 10, 2008

I'm just getting into functions, so please bear with me on this as I start to understand how they really work. I do have the VBA and Macros for MS Excel book which has been a great help!

Normally on this I would use a simple array formula to gather a total calculation from a timesheet, but in this case there can be numerous timesheets to add this up.

Hours would be recorded on a sheet with both a client and a task selected. I also have a summary sheet to show clients against tasks... adding all the hours from each timesheet.

If it were from a single Timesheet (possibly creating a single one of these for all sheets is an option?) I would be able to use the following array formula:

=SUM(IF((Timesheet1!$F$8:$F$22=C$5)*(Timesheet1!$G$8:$G$22=$A9),Timesheet1!$D$8:$D$22))
Timesheet1 Column D = hours
Timesheet1 Column F = clients
Timesheet1 Column G = tasks
Row 5 (starting Column C) = clients in summary sheet (across top)
Column A (starting Row 9) = tasks in summary sheet (down side)...........

View 9 Replies View Related

Function For 2-dimensional Array

Feb 27, 2007

I am trying to write a public function that fills a table for a 2-dimensional array and am having trouble with my named ranges. The x-axis is based on years (range F2:O2) and the y-axis is a q_factor (range E3:E23) so the data range would be (F3:O23. The following outlines my logic:

Public Function bondValue(years As Range, q_factor As Range, z As Double)

Dim nRow As Long, nCol As Long
Dim bondPort As Range

nRow = q_factor.Rows.Count
nCol = years.Columns.Count
sumTau = 0

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved