PDA

View Full Version : Conundrum



magelan
11-26-2012, 09:20 AM
Hi All.

Trying to design a new program here. I have the UI built, but now it comes down to the code.

Essentially, this program is going to be reading 3 or 4 files of raw data lists that are 20 thousand lines or so long. I need to take all these lines, and copy them into a new workbook, as well as perform some operations on a specific column. Here's my question...

Should i copy all the data into an array and then either output a list, perform my operations, and save that list as the new workbook? I'm kind of nervous about this because a program that does 10k lines already takes 15 seconds to run [and this would be 60k lines...] though the user has apparently no problem with it taking a while [considering it takes hours and hours to do it by hand]

Or should I copy all the data into a new workbook, and then just operate on that [without an array or building a new list..]. This sounds like the best method to me, and I know how to open a second workbook with VBA, but not how to then copy a sheet into a third workbook.

How would you design this to work most effectively?

magelan
11-26-2012, 01:10 PM
=(
20 views and no replies? Just seeking some advice from other programmers...

Bob Phillips
11-26-2012, 03:57 PM
Have you got Excel 2010? If so, maybe PowerPivot could help you.

magelan
11-29-2012, 01:00 PM
hm.. I am on 2007.

I'm approaching this from the standpoint of a programmer, not a "guy working in excel" though. Last time i asked for how to pull data from a website and someone told me "go to the data tab.." which of course is useless to VBA

snb
11-29-2012, 01:22 PM
I have the UI built,

I can't see any....

Vague questions elicit vague or no answers...

magelan
11-29-2012, 01:48 PM
I can't see any....

Vague questions elicit vague or no answers... Well, considering i wasnt asking for help on a specific project, but rather on a bit of theory... i was mostly curious as how to best load in 60k+ lines of data without slowing it down. Seems like worksheet copying did all 60k lines in about 3 seconds though.

Here's my program if you want to see it.

snb
11-29-2012, 02:25 PM
Why don't you use:


for each fl in Application.GetOpenFilename (Title:="Open Files", MultiSelect:=True)
' _/\_/\_
next


And why don't you use a 'userform' ?

magelan
11-29-2012, 03:22 PM
Why don't you use:


for each fl in Application.GetOpenFilename (Title:="Open Files", MultiSelect:=True)
' _/\_/\_
next

And why don't you use a 'userform' ?

This is actually a standalone program that users are going to be using - specifically one user wants to select up to 4 files and see the files she is selecting as well as the file names that will be output.

I havent coded or used a userform before - I find desiging UI's like this myself is easy enough and have never had a reason to stop.

snb
11-29-2012, 03:43 PM
Why do you think MS introduced userforms, and why would they have called those by that name ?

magelan
11-29-2012, 04:05 PM
I dont recall ever seeing userforms in c++ java php assembler..... I kind of jumped into VBA a short while ago and never required userforms or had to find out what they do. It made sense to me to make a nice, easy UI and put all of its code in the sheet1 object to separate it from the real code.

snb
11-30-2012, 01:38 AM
A sheet is designed to hold data. A userform is designed to .... (what's in a name ?)