Array equivalent to Paste-Special-Add?

[greencroft]

I have an Excel model used to create cashflows from real estate projects.

Rather than calculate cashflows for all units within a property simultaneously on a "live" basis with formulas, the model uses a "roll-up" which calculates the cashflow for a single unit at a time then uses VBA to loop through each unit, copying the cashflow for that unit to another part of the tab as values only. It then loops through all remaining units, using Paste-Special-Add such that by the time the loop is exited, I have the aggregate cashflow for the entire property (i.e. all unit cashflows added together).

This approach works fine but, via work on another project, I have recently had my eyes opened to the speed benefits of minimizing copy and paste actions by using array variables instead and then "dumping" the contents of an array to a range all in one go.

I can follow the code for these operations fine.

My query is how best to adopt the array variable approach when trying to add values rather than just taking them from one place to another?

I can set up 2 arrays, one to receive the unit cashflow and the other to act as the running total. But for that to work, do I have to cycle through each element of the array adding the new elements to the respective individual running totals?

Or is there a way to simply add ALL elements in one array to all elements in another? Both arrays would be the same size.

From other material I have read here and elsewhere, I can happily create the code to dump the end result to a range.

Many thanks for any pointers.

[jmac1947]

hello greencroft,
It is always a huge improvement if you can attach an example copy of the workbook with any code, that can speed up the understanding of your question and therefore the response.

I think you are saying that you need to:

1.calculate the cash flow of a particular unit within a property, one at a time
2.accumulate a property total that is the sum of all the individual units within that property.

What is not clear is what the output you are expecting will "look like" (the value of attaching the example file )

Given the assumption here that there may be many such properties, are you looking for a spreadsheet for each property as a starting point?. Let me assume that is the case.

Suggest an approach.
set option base = 1
Declare an array of say name "property_total"
use an input box to collect from the user the number of units in the property (based on plan to use the spreadsheet for multiple properties one at a time, alternative is to add a tab "constants" and store variable information such as number of units)
Redim the "property_total" array to be equal to the number of units +1 - effectively creating a property total (plan here is to store the individual cashflow in an array element that is the same as the individual unit number - hence the setting of the array base to 1)

so something like:

property_total (unit_number) = Unit_cash_flow
property_total (number_of_Units + 1) = property_total (number_of_units + 1) + Unit cash flow

I use a similar structure to accumulate monthly totals where I declare the array to be 32 elements and use element 32 as the monthly total.

In one complex workbook I manages to save up to 50% of the execution time by moving to arrays in an example of 200,000+ rows to process

Hope the general suggestion helps you, the thrust of you post suggest you have a good grip on the basics which is a good stating point.

Cheers

[greencroft]

Thanks for your help on this jmac1947

Hopefully I can clarify a few points on my earlier request for help:

Each Excel model just deals with a single property which can comprise of 1 to many (max is usually about 300 for a big shopping centre for example) units.

The code I use at the moment for the "roll-up" task using the Copy - Paste Special Add approach is as follows:

Please Login or Register  to view this content.

I do not need to retain a record of each individual unit cashflow as these can be called up on demand by setting a cell named TenRef to the ref number of whichever unit you want to view.

I am not attempting to move the actual calculation of the individual unit cashflow to VBA.

As you can see from the code above, I am simply copying a range named "Rollup_CopyAll" which is 120 columns x 66 rows (so 7,920 cell values) to an identical sized range named "Rollup_PasteAll" but crucially am adding the pasted values to what is there already such that by the end of the For ... Next loop, the Rollup_PasteAll range has the aggregate values for all units within the property.

This roll-up procedure is the section that I want to explore replacing with an array variable approach.

I am not sure I understand what you are suggesting in the later part of your post.

In there, is "Unit cash flow" itself an array variable?

Many thanks again for your help here.

[greencroft]

Just as an addition to what I posted this morning, below is some test code I have written to compare the 2 methods.

This first part replicated the original copy - paste special add method:

Please Login or Register  to view this content.

The second part, I try to do it using arrays. I am not at all confident that this is the best or fastest way of doing this but it produces the same result and is about 40% quicker on my machine for the range I have adopted which as per my earlier post is 120 columns by 66 rows.

Please Login or Register  to view this content.

If there is a method whereby 2 equal size arrays can be added together without cycling through each individual element, I would love to know it.

Many thanks.

[jmac1947]

good morning greencroft (well at least it is morning here in OZ)

Firstly, I am personally not aware of an "add two arrays together" option but will do a bit of a search to see what is around.

Clearly you seem to have array manipulation under control and have seen the performance improvements. I am still a little unclear as to exactly what the required output would look like and the actual inputs.

Is it possible to upload a sample workbook that shows the inputs and the expected output for a single property. Armed with that I may be able to suggest an alternative method of "skinning the same cat"