100%... Digi, Visi, Mini, SAS all seem like a lifetime ago
Posted on 06/16/2017 6:48:23 PM PDT by RoosterRedux
I have been learning VBA for Excel for a few weeks. Have used Excel for years (I am an adept mathematician) but haven't taken the time to learn the language which supports it.
I have been searching videos that teach this subject and, so far, this is the best I have found.
I welcome advice from Freepers who know better than me.
My professional career grew up in the period prior to excel, but not prior to computers.
If I had Excel in grad school, I would still be in the basement running numbers.
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.
Same here. Even though I gained a deep knowledge of Excel over the years, there always was a special place in my heart for the DOS days of Lotus 1-2-3.
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 suspect you didn’t properly define your data types.
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.
Great advice, all of it.
One downside, no matter how cool the stuff is you do in Excell... there will be some who can’t get past the fact it is Excell.
“If I had Excel in grad school, I would still be in the basement running numbers.”
LOL. You sure would be.
100%... Digi, Visi, Mini, SAS all seem like a lifetime ago
“Excel was the “killer app” that made me buy a home PC in 1992.”My first work for hire was constructed using VisiCalc for Apple II.Lotus 1-2-3 was the killer app that made me buy a PC in 1983.
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
My first work PC was an IBM PC Jr. It had a infrared keyboard, no mouse, a 5-1/4" floppy drive and 2 cartridges on which Lotus 1-2-3 was loaded. It didn't even have a word-processing program - and no, Edlin, the DOS line editor doesn't count.
Since it didn't have any word-processing capabilities, I created a worksheet macro that let me do the same thing. It would justify text, let me do indents, wrap text and generally do anything I wanted. I would save my document onto a floppy, print it from a co-worker's PC that DID have a printer attached to it, then hand it to the secretary so she could do her magic on the Xerox real word-processor.
Everybody in the office thought I was a genius for coming up with the program.
I’ve been doing some genealogy.
Try using the DATE function in Excel for a date prior to 1900.
You’re right.
I just tried it in VBA and it doesn’t work.
I’ll see if I can find an answer.
I had 123, wordstar and a modem program all on one 5 1/4 floppy.
Excel uses some type of integer to represent dates beginning Jan 1, 1900 = 1 and goes up to 9999. Earlier dates are negative numbers and considered by Excel to be errors.
Google Sheets and Libre Office just use the negative numbers as dates and that way they stay compatible with Excel. You would think, after all these years, MS would have fixed that.
I looked around for an answer and there doesn’t appear to be one.
Use a string and do your own conversion to integers if you require date math. If you don’t need date math, then a formatted string is just fine.
Great. Thx.
Disclaimer: Opinions posted on Free Republic are those of the individual posters and do not necessarily represent the opinion of Free Republic or its management. All materials posted herein are protected by copyright law and the exemption for fair use of copyrighted works.