$10 or $20 grand for a few weeks work. Can now be done on Excel with VBA...or so I think.
Google “why not to use Excel for statistics” (assuming you are an academic, it’s OK for business).
I am a contributor to a site called excel forum. A lot of knowledgeable people there and even though I’m listed as a forum expert i learn a lot from the other contributors. Stop by the site if you haven’t seen it already. They have tutorials and a VBA section.
“I welcome advice from Freepers who know better than me.”
What type of advice do you want?
I was self taught years ago in my programming days. I also used the excel forum mentioned by one Freeper, which was very helpful. In general, I would record steps and then read the code it generated. I also googled ‘loops’ and other common programming techniques to understand the language. If you already understand programming logic, you can ascertain the language with forums and using the record function. Good luck!
Excel was the “killer app” that made me buy a home PC in 1992.
Excel is very good with integers. Not so much with fractions....
I cut my teeth on DigiCalc which only ran on VAX/VMS. It had dimensions before dimensions were cool.
I did this a couple decades ago, but it is Visual Basic and you can record what you do so it writes it’s own code. Then you can go back in and edit it.
I am a VBA guru. Folks at work think I’m a magician. It’s good stuff.
It really all comes down to parsing a known entity. Let’s take English and its mix in math for example, Recursion is real ...
expr(); term(); factor(); solve(); expr();
1. Google is your friend. If you get stuck you can most always find a solution to a problem, or find out that what you're doing is impossible or results in a weird MS error for which there is no workaround. You'll then have fellow programmers to commiserate with. You will also find out about lots of great bulletin boards where you can get help.
2. Useful bulletin boards: www.experts-exchange.com and www.stackoverflow.com are some decent sites for getting specific questions answered.
3. Use the Record Macro feature where possible to generate the beginnings of your program. BUT DO NOT EVER STOP THERE.
4. Try never to write code that explicitly activates worksheets or selects cells. This will slow down your programs and make them more susceptible to interference if you decide to try and work on something else while your program is chugging along. When you record macros you will get lots of lines of code that explicitly activate sheets and select cells. You need to rewrite these lines.
5. Always try and be as explicit as possible when referencing workbooks, worksheets, cells, etc. Recorded macros seldom make it explicit exactly what worksheet or cell they are working on. This can also cause problems if you try and do other work while your program is chugging along, and they can make debugging your code later much more difficult.
Here are some examples:
Bad Code: (similar to what is generated by Record Macro)
ActiveSheet.Range("A11").Select
Selection.Formula = "=SUM(A1:A10)"
Better Code:
Dim ws as Worksheet, c as Range
Set ws = ThisWorkbook.Worksheets("MySheet")
Set c = ws.Range("A11")
c.Formula = "=SUM(A1:A10)"
6. If you begin your module with an 'OPTION EXPLICIT' statement then it will force you to explicitly create and assign all of your variables. This can sometimes be annoying if you just want to use a throwaway variable as an index, etc. but it is better in the long run especially if you have to come back months or years later to debug/enhance your program and want to have a better idea of what every variable is doing.
7. If you find that you are spending a lot of your program storing values in large ordered tables then you might want to consider moving your application to MS Access if you have access to a copy. Yes there are ways to treat Excel tables as if they were database tables, but they are much more susceptible to corruption and errors than Access DB tables.
8. If your program is only being used by you then you will want to use early binding for any libraries (Tools > Resources) that you use. You will get access to the Object view and autocomplete functions. However, if you are developing for other users who might have different versions of Excel on their machines then you will want to shift to late binding so that your program will work on pretty much any platform. You can google early and late binding for more info on the pluses and minuses.
8a. For example, you can bind the MS Outlook library and send email out from your Excel workbook if/when that makes sense. Or bind the MS PowerPoint library to automatically generate PowerPoint presentations from any charts you create.
9. Charts are handled bassackward in Excel as far as I'm concerned. I would like to create and format my charts first and then add the data series to them. However, it seems that when you create a chart it has to have at least one series to begin with or you will get all sorts of strange errors. A trick is to create a chart that has a series with a single point, format your chart, add your real series to the chart, and then remove the single point series. NOTE: I believe that in Excel 2007 (or maybe Excel 2003) Record Macro will not record steps associated with creating charts.
10. One of the best resources for Excel VBA help is to go to the MS Object Model. Here is the site for the Excel 2013 Object model: MS Object Model. From there you can walk down the tree to the object/method you want help with. The help will be terse and the examples will be criminally simple and mostly useless, but it is what it is.
VBA is generally derided and looked down upon by "real programmers", but it is very useful especially if you are supplying code to other users who are most familiar with Excel, or if you are writing code as an employee outside of the IT department and have limited access to "real" programming environments and compilers.
I have done some pretty cool things with VBA including:
1. Generating Excel workbooks out of Access
2. Generating PowerPoint presentations and Word Docs out of Access and Excel
3. Invoking Matlab for more difficult and time-consuming calculations
4. Querying Oracle databases
5. Creating a simple scripting interface so average users could create their own customizable data processing scripts by stringing together canned macros
I use Excel with vba very frequently. One of our departments asked IT to do a task for them and IT said it was impossible. I got great satisfaction solving that puzzle.
A great way to learn vba is to have Excel record macros and then review the code.
I do a lot of looping with vba. Find example code that finds the number of used columns and rows then learn For Next looping. Usually like:
For x = 1 to z
...
Next x
I’ve coded in just about everything except autocoder and assembler. That looks like a good tutorial i looked the debug video and it seems like things are explained well. Once you learn vba you can code in Access and Word as well. same language and create some really great database applications. stuff that can approach mainframe quality for desktop. Stay away from macros. write in VB.
“If I had Excel in grad school, I would still be in the basement running numbers.”
LOL. You sure would be.
I just scratch the surface of all that excel can do with the spreadsheets that I use at work but I couldn’t do my job without them
You can use Google search, just enter"
"Excel macro" and some specific action you want the macro to do, such as "copy rows to new sheet" or "find matching rows", or "connect to database".
Very few users even know about the vast potential of Excel using the macros.