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.

 

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.

 

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.

 
Best Response

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.

Online Excel Training Classes Near Me | We are near to your location just sign up on yodalearning.com for best excel online courses Excel VBA Course

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”