How use to write programme that involve with a lot of active calculation? In excel 1M+ Row and 20+ column

Question

First, I dont have any experience with programming. If I ever start, then this would be probably my first. I keep looking for answer until I found this site.

I am looking outside the box because in excel doing a data of 1 million + row and 20 + column would take a very long time just to wait for the calculation to be done and the copy and paste with formula would take longer. Imagine I have to let the computer running for 8+ hours with the helps of marco and F4 (repeat). All my formula have to paste into number only with I have done with the formula. And even I break the files into piece, the files sizes are 20MB to 110MB without active formula. Opening the file is taking forever.

I wonder how to write a programme with 1) dialog box, 2) the excel command and formula (sort, delimiter, concatenate), 3) ability to create graph, 4) with tabs to view different set of data or graph 5) add in a set of data 6) limiting the number (1-100000), etc. Outlook something look like utorrent.

What compiler suitable for this programme? It's easier you tell me which 'book' to read that me finding which 'book' is suitable because even if it is I might flip it through and go on to the next one. 'book' may refer to book, way, steps, etc.

Answer 1

If you know excel then Excel VBA is a VERY capable language to do all this. I would suggest you go to the VBA Dev Center here to get started.

Answer 2

I'm not sure what you actually want. With 1M+ rows and 20+ columns, an Excel sheet doesn't seem to be the right tool for the job. So do you...

• want to keep using Excel, but automate the job? Use Excel VBA like renick suggested. It's the language that Excel uses internally for macros, but you can write any kind of automated processing you'd like. Beware, however, that VBA is not exactly the best language to start a programming experience with. (That's my personal opinion, and what matters is of course whether you get the job done).

• want to switch to something else? A database management system seems better suited for the amount of data you have. Microsoft Access is part of Office and might already be on your system. Getting your data into and out of the database could be a problem, but the advantage you have is that a database is built to handle colossal amounts of data and will happily munch your figures for several days without failing. You can access the data using the Structured Query Language (SQL), which is not really a programming language, but very powerful (and it most certainly has CONCATENATE, ADD etc.). Graphing is more difficult, but can also be done.

Answer 3

I can't believe I'm about to say this (for most things I do it would be the wrong choice) but:

If the computations aren't that complex (just lots of them) Python might be a good bet.

If you can get the input as a CSV file than, for about 10 lines of code, you can write a loop that will be run for each line of input and hands you the values to play with.

for line in open('filename', 'w')
  values = line.split(',')
  #values has the values from this line as strings.
  #these can be converted to numbers:

  x = float(values[0])
  n = int(values[1])
  #... and then processed

That might not be the cleanest/best approach but it's simple and straight forward.

p.s. For 1M+ rows, don't expect it to be blazing fast (10 sec to a min or so, depending on what you do to the data)