Remove 11000 external array formulae & reduce process ti

[PeregrinTook]

Ok I’ve made quite a few posts now on this forum and am delighted to say that virtually all my queries have been solved by a number of resident VBA pro’s. Most of the posts so far have been in relation to a particular project I’ve been working on where I’ve been automating a number of reports from exported Access queries. Now however, since I’m the only person on my project who has the faintest idea about VBA, I’ve been asked to start looking at automating some more reports for other people – and since the bulk of what I’ve learned so far has been on an extremely ad-hoc manner I thought it best to try and start this first task in a much more structured fashion. Which is what leads me straight back here, to the home of the experts!

Current process for generating aged stock summary:

Export csv file (currently around 5500 rows) from oracle db then open in excel & copy all to tab ‘stock’ on totals.xls
Sort by stock_entry_date descending
Insert a column next to stock_entry_date and manually enter a category value next to each date from 1 (less than one month) to 8 (over 2 years) or 0 for no date
Resort by location_code ascending
Cut each set of rows with the same location_code and paste to appropriate tab of the same location_code name – end result is 39 tabs populated each populated with its own data, and tab ‘stock’ is blank.
Save spreadsheet

Open aged stock summary.xls & hit update external links
Go and make a cup of tea, perhaps even a sandwich – heck possibly time for a game of golf when this puppy opens!
This summary sheet also contains 39 tabs with location_code names, and each has a table in it showing the sum of all stock across each product (31 columns) against the 9 date ranges using array formulae back to the corresponding location tab on totals.xls. There are in fact just shy of 11,000 external array formulae to be updated each time this sheet is opened – then of course there are the other 9 higher level summary tabs which link to these 39 location_code tabs, but they are nothing in comparison & there are no array formulae there.

It currently takes someone between 30 mins & an hour to sort out the totals.xls update. Then whenever any of the managers want to see the high level summaries they don’t know how recent the data is so they always hit update, which takes between 15-30 mins and can hang their whole systems occasionally (granted the pc’s they use could do with being brought into the 21st century...).

So how to speed things up a little? Well I have an idea but I thought before I got started on coding I would seek some expert opinions on the way forward. Here’s my idea:

Have a blank version of aged stock summary.xls which opens at a ‘file summary’ tab
Firstly all array formulae must be stripped out as external links are no good (especially 11,000 array ones!) and remove need for totals.xls completely
Tab ‘file summary’ has three user-maintained tables in it, for location codes (currently 39) as rngLocationCodes, stock codes (currently 31) as rngProductCodes and date codes (9) as rngDateCodes, which auto-updates based on now() function
User who normally carries out update process exports csv file to pre-defined directory clicks ‘update’ button which kicks off VBA routine to do the following: (mixture of VBA/plain english - just rough draft at the moment!)

Sub Update()
Dim all variables/strings etc...

Open csv file

‘compare each value in column N to the predefined strTimeCategory (from rngDateCodes) and insert into column M (dead field, always comes out blank from DB)
For i = Range("N" & Rows.Count).End(xlUp).Row To 2 Step -1
	If Cells(i, "N") <= strTimeCategory1 And Cells(i, "N") > strTimeCategory2 Then
		Cells(i, "M") = 2
	End If
	If Cells(i, "N") <= strTimeCategory2 And Cells(i, "N") > strTimeCategory3 Then
		Cells(i, "M") = 3
	End If
   Etc More Ifs
   ...
   For all 0-9 strTimeCategory’s
Next i

For each strLocCode in rngLocationCodes
	For each strProdCode in rngProductCodes
		For each strDateCode in rngDateCodes
			With csv file
				strAnswer = Evaluate("=SUMPRODUCT( (Q:Q=strLocCode), (C:C=strProdCode), (M:M=strDateCode))
			End with
         worksheets(strLocCode).range(vRow,vCol)=strAnswer
		Next strDateCode
	Next strProdCode
Next strLocCode
Worksheets(FileSummary).range(LastUpdateTime)=now()
Activeworkbook.SaveAs Filename:= strFilePathAndName & Format(Date, "yyyymmdd")
End Sub

Would still need to suss out how to define the paste range, but I’m thinking vRow & vCol will increment with each step of the defining for/next loop. Would also need to deal with error handling (ie worksheet doesn’t exist for newly created vLocCode etc).

But the above, after a great deal of refining, will hopefully populate each cell in the table of each tab in turn with a value rather than a formula or external link, thus making the opening time almost instant. I believe it should cut the update time at least by some (as opposed to array formulae within the spreadsheet which update on open) but I don’t know how much. This is where I’m looking for opinions!

Is the way I’ve suggested above a viable option, and do you think it will be significantly quicker than the previous method of array formulae? But the biggest question of all I guess, is – is there an easier way to do it?!?

All thoughts very welcome, as always - indeed the more opinions the better so lemme hear them please!!

All the best,
PT

 

[VicRauch]

PT,
You did say "All thoughts very welcome" so I'll give you my thoughts. Why not use Access? Everything I read in your post sounds like a database, not a spreadsheet application. I'm sure I did not understand your whole system, but I do know that Access can normally handle the raw data to summarized reports quicker than Excel can, and (this is a big one, I know) IF you know Access, it is also much easier than doing it in Excel.

 

[PeregrinTook]

Vic,

I did indeed mean all thoughts, so thanks for yours!

I do know Access quite well now too (though not as well as Excel) but the big problem here is that the team I'm doing this for do not have Access & are not willing to put it in - that was my first suggestion too but they said 'not interested'!

Hence why I'm having to stick with the Excel route unfortunately...



So in that case, any more thoughts then on the for/next loop process ppl?

Many thanks
PT

 

[VicRauch]

I don't give up easily. Can you give this team their data by attaching the spreadsheet to an Access database that resides on the network? This way, Access can do all the processing and the team members can be attached to the processed data. The only part of this that would be a problem, the way I understand the situation, would be the Update Process that it seems anyone can do. My thinking is that a button within the spreadsheet could, via VBA, either run a procedure in Access, or set a timer in Access so a procedure will run in Access, therefore allow them to do an Update within Access. I would think your network people are not going to allow this to happen on a server, so it would have to be on someone's PC where the Access database would be stored. I know, probably not going to happen, but I still like to throw it out for the brain-storming aspects.

I have written an Access app to generate an Excel spreadsheet is one reason why I know Access is so much faster. But I'll keep this is in the back of the brain and see what happens.

Good luck!

 

[PeregrinTook]

Sorry Vic just got round to replying just now, main tranche of current project went live yesterday & has all been a bit crazy!

Ok, I think as far as storing an Access db on the server goes, it should probably not be an issue - so although the team may prefer not to use Access directly, it sounds like with your method they may not need to know they're actually using Access so I'm keen to know more!

I'm intrigued by your suggestion that your excel app actually generates a spreadsheet - how does this actually work for you?

The reason I ask is that my limited use of Access has generally been to do processes like the following:

Access has a number of stored queries on linked SQL Server tables. User clicks a button which outputs the queries to Excel, then opens the master macro workbook and calls a macro(s) to calculate stuff & reformat, then populates a template file set to receive the data & saves in appropriate location with timestamp in filename.

But it sounds like your method generates the entire spreadsheet from scratch, directly from Access. Is that correct?

If so then I'm very interested in knowing more!

The particular system I'm looking to build for this team has an end result which is a monstrous spreadsheet. Like I mentioned previously there are currently 39 location tabs which each have a table showing product vs age.

There are then a further 7 tabs which are compilations of the 39 locations based on region of the country. And finally a master sheet which shows summaries of every location on the one report.

Are you saying that you would generate a brand new spreadsheet with these 47 tabs every time an update is done? I would have thought that would be much slower than the method I had suggested - purely because of having to generate the higher level summaries as well as the 39 data sheets, then also doing the reformatting etc to get the reports looking the way they do now. Surely just calculating the base data I need to for the 39 location tabs then populating a spreadsheet template which is already in the format I need, with no further updates being required for the higher level summaries, would be quicker?

Perhaps I have completely misread your suggestion however, and am way off the mark with what you were trying advise - and please don't think I'm just shunning your suggestion here, far from it! I just can't get my head round how generating a spreadsheet this size from scratch through Access could be quicker than populating a ready-built template?

Thanks again
PT

 

[VicRauch]

Very High Level Overview:
Access has all data from distribution points (locations within USA)
One complete summary of the data is generated in Access, then this data is output to Excel via TransferSpreadsheet. About 50 times faster than Access building spreadsheet one cell at a time which was the method before I got the project. Then, from this data (still in Access) individual sheets are created in one workbook of selected charts. In this case the data is copied to the sheet, then Excel macro is used to build and format the chart.

From your last message, I'm getting the idea that the Update function is NOT a complete re-write of the spreadsheet. The project I took over was a complete re-write of the spreadsheet. I would not be a good judge as to which would be faster because of my limited Excel experience.

Now, down to the gritty of how I created the spreadsheet in Access. I had a lot of summarized detail, then needed to have sub-total lines, and total lines after all the summarized detail. To do that, within Access I created a clone data database that had specific, and captured, criteria for the summarized data. For example, one of the data databases would be for the first quarter, the next data database would be the second quarter, and the third data database might be for the first 6 months of the year. These various data databases could be selected one at a time, and then the charts would be created using the data from this data database.
So, what I ended up with was one Excel spreadsheet per data database within Access. That way the users at each location could look at the Excel spreadsheet (which they already knew) and the charts created from that same data by Access (which they did not need to know). The corporate user had control of the data databases for creating new ones, and deleting old ones no longer needed.
I don't know how much that will be a help to you, but at least some I hope.

 

[PeregrinTook]

Ok Vic, I think I understand where you're coming from a bit better now.

Had a look at TransferSpreadsheet method, and it looks quite similar to the DoCmd.OutputTo method - which is what I currently use when exporting queries to excel (using acFormatXLS). And it sounds to me like the processes we follow are in fact also quite similar, in that you also call a macro in Excel to build & format the chart once you have done the TransferSpreadsheet part.

You are correct that the Update Function for the project I'm talking about does not require an entire rewrite of the spreadsheet, so I'm not sure that Access is the way to go with this after all.

I think the code I submitted in my first post will probably do the trick for what I'm after (following a good deal of refinement of course) - but my real query was to see if there was a faster way to do those 11,000+ calculations than the method I had suggested.

I really appreciate you taking the time to respond Vic - but hopefully someone will be able to let me know their thoughts on the code in my initial post now, in terms of how much faster they think it will process the array formulae, and if there is maybe a better way to go about this (without using Access).

Keep thinking outside the box people!

Many thanks
PT

 

[VicRauch]

PT,

You might want to start another thread because this one has so many replys, I feel most people will not be looking at it now. Sorry. {

But, before I go, I did look at your code a lot closer, and I feel I can help with the first part, but not the second part. Rather than having a string of If statements, you can turn this into one If and then several ElseIf statements:

If Cells(i, "N") > strTimeCategory1 Then
Cells(i, "M") = 1
Else If Cells(i, "N") > strTimeCategory2 Then
Cells(i, "M") = 2
Else If Cells(i, "N") > strTimeCategory3 Then
Cells(i, "M") = 3
etc, etc, etc.
I'm assuming that strTimeCategory1 is the largest time, 2 is the next largest, etc.
This way, you do one test per Time Category, and you quit testing when you find an answer rather than two tests per possible Time Category for every row. Don't think this will make a big difference, but at least some.
Good luck on the rest of the code.

 

[PeregrinTook]

Thanks Vic - I see what you mean, one If with ElseIf's will indeed reduce the processing time, maybe only a little but it all counts - and I'm always looking to streamline code as much as possible, learning VBA from the macro recorder was a very bad way to start now that I've seen so much more efficient code on this site...

Don't think I'll start another thread cos Mr Excel may stamp down his heavy-heeled boot of authority & reprimand me for reposting a question which has been suitably viewed with no comments other than your own - I reckon I'll just forge ahead with my idea & maybe post back with specific queries when I get down to the nuts & bolts of the routines...

Whats the worst that can happen?!? It currently takes this lad around an hour & a half each day to go through his horribly manual process, so I figure even if I can get that down to 5 mins or less with no interaction other than clicking a button then its a pretty good improvement - so an extra minute or so off will be trivial to him by comparison...

Charge on!

All the best
PT

 

[acw]

PT

Haven't read through it all in detail, but as a follow on from Vic's comment, a select statement would be better than the multiple if statements or the if with else if.

With either of those, each option has to be processed. With a select case, once the solution is found, it doesn't process the other options available. Saves more processing, especially if you can work your order so that the most common is in the first case statement.


Tony