Online Excel VBA Courses
Do you guys have any recommendations for an online VBA course? I see there are YouTube tutorials and cheap programs, but I want the good stuff. Anyone have any strong recommendations?
Do you guys have any recommendations for an online VBA course? I see there are YouTube tutorials and cheap programs, but I want the good stuff. Anyone have any strong recommendations?
+84 | Those Of You Who Have Made It To Millionaire Status, What Advice Do You Have? | 32 | 4h | |
+58 | Etiquette for Giving Notice | 18 | 13h | |
+47 | Family Office Exit Ops | 15 | 7h | |
+44 | NYU SPS MSRE Reputation (and others) | 16 | 3h | |
+42 | Asset management salary in NYC seems god awful | 22 | 2d | |
+30 | Contemplating job opportunity | 8 | 13h | |
+24 | Assessing / Analyzing Office RE | 2 | 1h | |
+20 | Palatine Partners | 4 | 1d | |
+20 | Have few weeks to get ready for RE interview with zero relevant experience | 5 | 1d | |
+20 | Pref/Mezz for LIHTC | 18 | 2d |
Career Resources
I self-taught VBA about a year ago. The VBA subreddit has some really great resources, but I used this MOOC to learn the language. If you have any programming experience it is going to be pretty basic, but if your new to programming it should lay a very solid foundation. I did the course in a weekend, it doesn't take too long.
I've never come across a proforma in RE that required VBA
Honestly most everything the vast majority of users need to do in excel can be done without VBA, but there are occasions where it saves a ton of time. Being able to define custom functions and keyboard shortcuts is extremely useful. One of the more interesting things I've done is create a spreadsheet where I can input a data-set of first names, last names, and their company's URL, and then the macro can search the web for their email address while verifying whether or not it is a valid email address. Obviously this isn't a big deal if your searching for 1 or 2 email addresses, but if you're sifting through 1000 people it can save a tremendous amount of time.
Lynda.com has some basic courses...
I also self-taught myself VBA (Google can take you places), though having a CS background helps (e.g., learning to read syntax, programming concepts etc.). Short of taking courses or watching YouTube videos, if you're new to VBA, I'd suggest using the macro-recorder, but really only to get used to the syntax of the code that gets spit out; it won't be as useful for doing more advanced coding. But after that, like I alluded to, Google is your best friend.
Not sure what kind of "good stuff" you're looking for, but the VBA code is either already on the Internet, or you can leverage it and write your own; it really depends on what you want to do with VBA.
I'm serious about Google-ing too. And after the 2349842432 time of searching online, you begin to build a knowledge bank of code that you can reference. Some examples I've leveraged and/or written: -wrote my own Excel-add in that adds a ton of functionality/shortcuts such as: * Disable F1 key * Alt+{RIGHT} / Alt+{UP} to autofit column width / row height, respectively (as fun as it is to mash Alt>H>O>I and Alt>H>O>A) * Alt+S to toggle negative on cell formula/values * Ctrl+, to toggle between Accounting format no decimals/two decimals (replaces Alt>H>K and Alt>H>0 x2) * Alt+, / Alt+. to increase/decrease decimal places * Ctrl+Shift+P to toggle % format 2 decimals / 1 decimal / 0 decimal (default Excel % format has no decimals, which is kind of useless for me) * tons of other formatting/functionality (mapped to shortcut keys) such as toggling colors/borders, print-related (e.g., set print area to 1x1, page orientation/size)
(I'm an expert at mashing key sequences which some people refer to as "shortcuts", but actual shortcuts such as some I mentioned above that I mapped are much faster)
-when my Fund Administrator emails that new files are posted on FTP server, automatically downloads and emails my team with the linked share drive directory (no more manually logging onto FTP!) -open specific applications/programs at a specified time before I get into the office every morning (this one is Outlook based with the specific time criteria) -monthly request to send a specific file to team members is now done in the background (via Outlook) -just for kicks, wrote code that turns logging into Seamless into a button on my Ribbon (but also leveraged this code to log onto other websites as well) (and yes, I've heard of LastPass)
Hope this inspires you; if you ask yourself, can VBA do ?, the answer is probably yes.
I want your column auto fit code!
If you can't learn through YouTube or googling you're f'd and worthless in life and the transitioning economy.
Here you go TippyTop11:
In the below code, I'm literally instructing Excel to hit the actual Alt>H>O>I "AutoFit Column Width" button Sub AutoFitColWidth() 'whatever you want to call your subroutine Application.CommandBars.ExecuteMso ("ColumnWidthAutoFit") End Sub
However, if writing general code within a file, I would recommend using the Range.AutoFit method.
And then, I map it to Alt+Right (or the shortcut of your choice) with Application.OnKey "%{RIGHT}", "AutoFitColWidth" in the Workbook_Open event of my self-written .xlam file.
Also self-taught over here. Google and stackoverflow will be your best friend. Most of the questions or problems you come across have in some variation been answered and you can either use it as it stands or add on features you see fit. The goal is to go through enough of those exercises where you're able to start thinking of ways to approach and structure the solution yourself, with using stackoverflow as a last resort. This will help you develop the logic and experience needed for more advanced problems. While for most things in excel you won't need VBA, there are situations where it definitely comes in handy.
Some examples:
Printing a few selected worksheets in a workbook that varies in page orientation and size depending on what scenario you're printing, you throw that all in a code module and link it to a button and have your team thank your booty for wanting to streamline a tenuous process.
A macro that generates a template worksheet and calls a separate macro that does some calculation for 1000+ props and saves it into its own separate file.
Running a refi/hold analysis that was fed from assumptions from multiple tabs -> now from a single user form macro.
Pulling data that feeds into a model. Pulling said data that resides in folders and subfolders (maybe 200 files total) where worksheet name and area of where the data table resides might be different (i.e. file 1 has it in worksheet name data1 and in cells A1:G100; file 2 - data_5 and in cells F10:K200) for each file. This took me a while but the tables of data contained similar key words so that's how I narrowed my crawl.
For loops, do while, onerror goto, etc. These will be your go to's. I'm by no means an expert but if you have any questions, feel free to send me a pm and I'll be happy to take a look.
Accusantium quaerat expedita voluptas dolores ratione qui. Optio et porro voluptatem. Sequi eius dolores qui nulla iusto quam et.
Illo dicta a reprehenderit qui eius. Dolorem aut autem repellat et asperiores. Dolor et nam quia libero vero. Eligendi reprehenderit ea quisquam ut harum deserunt magnam.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...