Every time I search for a VBA tutorial I kept finding the wrong solutions. It's nice to finally have an applicable use of VBA from M&I. Thanks yet again
@@vladkovalenko9222 We have an entire set of VBA lessons in our Excel course. Beyond that, you can find VBA books, but there isn't much specifically tailored to IB/PE because you tend to use VBA in fairly simple ways in those fields, and you don't even use features such as User Forms that take up entire chapters of other books.
That's a great suggestion. Recording macros and decoding the coding part later. It helps in understanding faster. Why don't you guys launch affordable pricing options for the Indian market?
Because we don't serve the Indian market. The finance industry is much smaller than in places like the US, UK, and HK, and prices are also reduced... so we'd be spending the same amount of time and money to go after a market with far less in potential revenue.
I can't say without seeing your exact Excel setup and settings. You might have somehow disabled macros or parts of the VBA object model within the "Trust Center" in the Excel settings, or you might have multiple files open, and it might not be saving in the correct spot. But these are just guesses.
For your input cell example, wouldn't you just setup & use Styles? If your manager later wants all input boxes to be a different colour, you can just modify the particular style and the whole model will be updated. Your Ctrl+Shift+I macro won't be able to. Great video nonetheless!
That is a good point. Styles are another way to do it, but the disadvantage there is that you have to select all the cells manually and then apply the style first. Updates or changes are easier to handle with Styles, but cell styles themselves tend not to change much in financial models (as different firms/groups have their own preferences). And you could always just press Ctrl + A and run the macro again to change or update the styles.
Sure, SQL knowledge helps, but I'd say it's even less likely to come in IB/PE roles than VBA or Python. It's probably more useful if you're in corporate finance at a normal company and you need to retrieve a lot of data from internal sources for use in reports. So: maybe learn the basics of SELECT statements, JOINs, conditionals, etc., but don't go crazy with it. A lot of programmers actually know shockingly little about SQL but somehow do their jobs anyway.
4 роки тому
I'm curious, did you really need your write code? Wouldn't it be faster to just record a macro without division deep into coding with visual basic?
Sure, it's always faster to record the macro and leave it alone. But sometimes you want to add extra features, error checks, different conditions, etc. that you cannot do within the recorder. Also, the code produced by the macro recorder tends to be ugly and difficult to maintain.
Format Painter is less powerful because 1) It also copies number formats, which we don't want - we only want to copy fills and borders but NOT font colors or number formats; and 2) With Format Painter, you have to select the exact cells you want to copy all the formats to. With our final macro, we can just select a big area of cells, or even an entire spreadsheet, and it automatically finds which ones are input cells and which ones aren't... without us having to select each input cell or range of cells separately.
Can you state which Excel version you are using? My bank uses Excel 2010 and it seems that the syntax is slightly different in my excel which leads to the code not working.
This was done in Excel 2019, but Office 365 is effectively the same. Yes, some of the older versions have syntax differences; we generally say that Excel 2013 is the minimum for our current tutorials, as there are too many differences prior to that (once you go beyond basic shortcuts).
VBA is more useful for roles such as investment banking, private equity, fundamental-based hedge funds, corporate development, and corporate finance. Python is more useful for roles like trading, quant funds, data science, or anything else where you work with huge sets of data (i.e., millions of rows rather than thousands of rows as in Excel). Best way to learn VBA syntax is to record yourself performing actions as macros, then look at the code and modify it. Our new Excel course will also include around 7-8 hours of VBA tutorials as an introduction.
Sorry but can you explain briefly why in the code you are using “if not” statement instead of just “if”. I’m a rookie, but I’m assuming within the “union” section of your code, you’re using a double negative basically saying that if the formula cells selected are not nothing (aka there is something in the cell) then apply this rule. Can you confirm this is what you’re doing?
Because "If Not" (double negative) is the easiest way to verify that the user has actually selected cells rather than a blank range. This is just the way VBA is set up and one of the quirks of the language.
Doesn't work as well because it can't detect the difference between true input cells with numeric constants vs. empty cells and text. Also, it incorrectly changes number formats. So it's really no different than Paste Formats.
You guys are so underrated
Thanks for watching!
Every time I search for a VBA tutorial I kept finding the wrong solutions. It's nice to finally have an applicable use of VBA from M&I. Thanks yet again
Thanks, glad to hear it! More coming soon...
@@financialmodeling Hey guys! Thanks for the video. Can you please recommend some other useful for IB, PE VBA tutorials
@@vladkovalenko9222 We have an entire set of VBA lessons in our Excel course. Beyond that, you can find VBA books, but there isn't much specifically tailored to IB/PE because you tend to use VBA in fairly simple ways in those fields, and you don't even use features such as User Forms that take up entire chapters of other books.
Thanks for u cleared my hesitation about languages. Thanks a lot.
Thanks for watching!
Excellent, concise and clear.
Thanks for watching!
4:50 you can just use the format button to fill the input box
See the other comments/threads here for why none of the other options (styles, copy/paste formats, formatting brush, etc.) do exactly what we want.
This is amazing. Thank you so much
Thanks for watching!
This video motivated me to quit VBA!
Glad to help!
That's a great suggestion. Recording macros and decoding the coding part later. It helps in understanding faster. Why don't you guys launch affordable pricing options for the Indian market?
Because we don't serve the Indian market. The finance industry is much smaller than in places like the US, UK, and HK, and prices are also reduced... so we'd be spending the same amount of time and money to go after a market with far less in potential revenue.
@@financialmodeling "Less in potential revenue". That's funny.
Thanks for the video, do you know why I recorded macro successfully and its working but there is no code in VBA? (module 1)
I can't say without seeing your exact Excel setup and settings. You might have somehow disabled macros or parts of the VBA object model within the "Trust Center" in the Excel settings, or you might have multiple files open, and it might not be saving in the correct spot. But these are just guesses.
really useful
Thanks for watching!
For your input cell example, wouldn't you just setup & use Styles? If your manager later wants all input boxes to be a different colour, you can just modify the particular style and the whole model will be updated. Your Ctrl+Shift+I macro won't be able to. Great video nonetheless!
That is a good point. Styles are another way to do it, but the disadvantage there is that you have to select all the cells manually and then apply the style first. Updates or changes are easier to handle with Styles, but cell styles themselves tend not to change much in financial models (as different firms/groups have their own preferences). And you could always just press Ctrl + A and run the macro again to change or update the styles.
What are some books did you read to learn this?
Check out Joseph D. Moore's books on Excel & VBA (may be more recent editions available now, but probably no huge changes).
Hi, can you pls help me with what softwares to use for making a video
Camtasia
This is Amazing, can you please give us the code you built in this video ? I could re use it for my modeling tasks
Click "Show More" and click on the links under Resources. The VBA code is in the .xlsm file there.
Hello,
What about SQL knowledge?
thanks
Sure, SQL knowledge helps, but I'd say it's even less likely to come in IB/PE roles than VBA or Python. It's probably more useful if you're in corporate finance at a normal company and you need to retrieve a lot of data from internal sources for use in reports. So: maybe learn the basics of SELECT statements, JOINs, conditionals, etc., but don't go crazy with it. A lot of programmers actually know shockingly little about SQL but somehow do their jobs anyway.
I'm curious, did you really need your write code? Wouldn't it be faster to just record a macro without division deep into coding with visual basic?
Sure, it's always faster to record the macro and leave it alone. But sometimes you want to add extra features, error checks, different conditions, etc. that you cannot do within the recorder. Also, the code produced by the macro recorder tends to be ugly and difficult to maintain.
Just curious
Why not use format painter instead of going for such trouble
Or do cell color formatting before inputting numbers..
Format Painter is less powerful because 1) It also copies number formats, which we don't want - we only want to copy fills and borders but NOT font colors or number formats; and 2) With Format Painter, you have to select the exact cells you want to copy all the formats to. With our final macro, we can just select a big area of cells, or even an entire spreadsheet, and it automatically finds which ones are input cells and which ones aren't... without us having to select each input cell or range of cells separately.
Mergers & Inquisitions / Breaking Into Wall Street
I see. That makes a lot of sense.
Can you state which Excel version you are using? My bank uses Excel 2010 and it seems that the syntax is slightly different in my excel which leads to the code not working.
This was done in Excel 2019, but Office 365 is effectively the same. Yes, some of the older versions have syntax differences; we generally say that Excel 2013 is the minimum for our current tutorials, as there are too many differences prior to that (once you go beyond basic shortcuts).
Really helpful vid! Recent grad looking to learn VBA or Python, which would you recommend? Also, what is the best way to learn VBA syntax? Thanks!
VBA is more useful for roles such as investment banking, private equity, fundamental-based hedge funds, corporate development, and corporate finance. Python is more useful for roles like trading, quant funds, data science, or anything else where you work with huge sets of data (i.e., millions of rows rather than thousands of rows as in Excel). Best way to learn VBA syntax is to record yourself performing actions as macros, then look at the code and modify it. Our new Excel course will also include around 7-8 hours of VBA tutorials as an introduction.
@@financialmodeling Thank you!
No sample file?
Click "More" or "Show More" and scroll to the resources.
100K anyday now!
Thanks! Just reached that mark.
Sorry but can you explain briefly why in the code you are using “if not” statement instead of just “if”. I’m a rookie, but I’m assuming within the “union” section of your code, you’re using a double negative basically saying that if the formula cells selected are not nothing (aka there is something in the cell) then apply this rule. Can you confirm this is what you’re doing?
Because "If Not" (double negative) is the easiest way to verify that the user has actually selected cells rather than a blank range. This is just the way VBA is set up and one of the quirks of the language.
@@financialmodeling thank you. So my understanding is correct?
@@michaelnassif7393 Yes
Format painter my friend
Doesn't work as well because it can't detect the difference between true input cells with numeric constants vs. empty cells and text. Also, it incorrectly changes number formats. So it's really no different than Paste Formats.