Excel is outdated !

Publié parBoris Noro26 juin 2020Publié dansBlogÉtiquettes :Excel

 

This page contains macros and videos that explain macros that allow to work efficiently with financial models. Many of the subjects in this page are in a file name “Generic Macros” which includes a whole bunch of macros and functions that may be helpful in just about any excel analysis that you are making. In particular there are two macros for colouring and for copying to the right — SHIFT,CNTL,R and CNTL, ALT,C (also SHIFT,CNTL,C). To use the Generic Macros macros, you just need to have the file open — the macros can be operated from any open file. Videos at the bottom of this page explain how to use macros such as copying a series to the right and colouring cells depending on the sheet colour. All of the macros and the VBA code is open and transparent meaning that you can try to understand the code, copy the code and modify the code. Ohter macros in the Generic Macros file contains VBA code that fixes various problems in excel such as finding and colouring external links, deleting blank rows, clearing sheets, finding the number or rows in a sheet and, making the lookup function into an interpolate function.

In addition to describing how to use, modify and make VBA code in the file named “Generic Macros”, I have included some general descriptions of how to use short-cut keys, how to effectively work with user forms and how create simple macros that help the goal seek a lot. These subjects are presented immediately below.

Video Review of Short-cuts

There are probably thousands of short cuts in excel (I don’t really know). Sometimes when I teach a class, I am told by participants that they will not be able to remember any of the short-cut keys I show them one day after the class is finished. My response is that you just need to know what you can do and the rest is like a cookbook. This is why I have created the files and videos below. The first point to understand is that some short-cuts are easy and really help and are worth a tiny investment in practicing. But like a lot of other things in life, you can take things to the extreme and over-use the short-cuts. So I have tried to discuss a few selected few of the short-cuts are really helpful.

Helpful short-cuts include ALT, E,I, S (Everybody Is Stupid, Egyptian Intelligence Service, Elvis Is Sleeping) that works really well with the SHIFT,CNTL,R macro explained in the Generic Macro section below. Other helpful short-cuts include ALT,E,M to move sheets around, ALT,E,L to delete a sheet, ALT E,S to paste special and SHIFT, ALT, right arrow and SHIFT, ALT, left arrow, The best short-cut may by the F11 key to make a graph and the ALT, F1 key to put the graph on the current page. To or delete a row or a column, the SHIFT, SPACE and the SHIFT CNTL keys combined with SHIFT,CNTL, + and SHIFT,CNTL,- can be helpful. All of these short-cuts are directly from excel and not associated with any of the files that I have made (i.e. Generic Macros”). Other useful macros create a new sheet, CNTL, N and format cells, SHIFT, CNTL, 1 to 7. One of the new short-cut keys is good for quickly freezing the panes and then un-freezing — ALT, W, F, F.

The video below walks you through these and other short-cut keys in the context of a credit analysis model and the files listed below are used in the videos. In the file named Project and Corporate Credit Example I have listed some of the short-cuts that I think are really helpful. The Project and Corporate Finance Example also is the file that is associated with the video below.

 

Video of Creating and Using Forms (Spinner Box, Combo Box, Check Box and List Box)

As with using short-cut keys, participants in classes that I teach sometimes complain that they will not remember how to use the forms — spinner boxes, drop-down boxes (called combo boxes), check boxes and list boxes. The video and file below are intended to be a reminder for how to used the developer tab and these forms. User forms or contols allow you to control inputs from different pages. This means that if you have an input in the assumptions page, say for the gearing percent of a project, this input can be controlled from multiple places including a summary sheet and a graph. These form controls can be very helpful in summarising data and making presentations with excel or just showing off to your boss. I use these forms extensively in other videos and files but I do not know if there is a place where I explain them slowly. The video below does this. It begins with explaining how to find the developer tab and then use the WINDSCREEN method to allow you to move the files (thanks to Tataynia in Russia). My son tells me the developer tab is like a secret handshake in excel — when somebody tells you they are really good at excel and there is no developer tab on their computer, you say yes you are really good. But you don’t believe them.

Video for Simple Macro with Goal Seek

As with the short-cut keys, participants in my classes sometimes say they will not remember how to make a macro. This little section is intended to be a reminder. You can do really fancy things with macros and functions that I hope are explained at various points in this website. This includes reading data from the internet, creating fancy scenarios, fixing excel to interpolate data, and resolving circular references. If you have not made a macro, however, do not be intimidated. The first macro I ever made sometime in the early 1990’s was to fix the goal seek in excel so you could repeat it without having to keep going back to the menu and keep changing the target etc. All you have to do is press the record button and then make your goal seek and then make absolutely sure to stop the record button once you have finished with the goal seek. This process is described in the video below. The file that I have used to explain this simple macro is associated with my WACC and interest shield stuff and listed below the video.

Video for Creating your Own Short-Cuts

Some short-cut keys that would be really good are not included in excel. These may be short-cut keys to format your currency, to underline groups really fast or to put extra decimals in cells formatted as a percent. I have seen some people include all sorts of add-ins in their excel menu with scads of short-cut keys and other functions. I hope you agree that this is not very necessary. Instead, you can make you own customised short-cut keys. Then you will know what is going on and you will not cede control of your excel to anybody else.

The video below demonstrates how you can make your own macros. There is only one big rule here. Please place your cursor on the cell to be formatted and do not move it before you are finished recording the macro.

Introduction to Generic Macros

The generic macros file below is one of the most important on the website. It has a macro to copy to the right SHIFT, CNTL, R and it has macro name SHIFT,CNTL,C where you can colour cells depending on which sheet they came from. To run this macro, all you have to do is have the file open because you run macros from other open files (e.g. say you are in mybook.xlsm, you can open generic macros and run any of the macros in your sheet). I don’t like to take control of your computer and make add-ins, instead I think it is much better to just open the generic macros sheet and enable macros. Then the SHIFT, CNTL, C and the SHIFT, CNTL, R should work just fine. The generic macro file also has a lot of other macros that may be useful that are explained in the next sheet.

I have tried to make the macros flexible where you can search more rows and or columns to colour (which slows down excel). You can also adjust the SHIFT, CNTL, R to look up to different rows and test other things. To adjust the generic macro file, you go to the first sheet of the file and change the parameters. After you change the parameters however you must run the implement macros macro.

For every single one of the more than 1000 excel files on this website I have not put any passwords on the macros or functions. This means you can look at the code and try to improve it yourself. (If you have some good improvements I would really like it if you send me your work). You can also copy the macros from this file into your file or your personal workbook if you do not want to open generic macros every time you open your file.

You can go to google and find a list of short-cuts that probably has more than a thousand short-cuts (I have not bothered to go and check). I use just a few short cuts that I think are effective. Some are old — for example ALT E, I, S and ALT E, L and ALT, E, M which come from an old version of excel. These can be very helpful when used together with the SHIFT, CNLT, R macro. I have put the short-cut keys that I like in the file name Short-cuts.xlsx which is attached below. I have also made a video for the short-cut keys that I am in the process of revising.

 

Miscellaneous Other Subroutines in Generic Macros

The generic macros file and a workbook named fm.xls includes a lot of different functions and macros. Some of the more important macros that create table of contents and remove the current sheet name from formulas and finds the links are shown below. As explained above, none of these macros have any irritating passwords and you can copy the macros into your files. The fm.xlsm file below has some of the older macros that I made a few years ago. The example calander shows how you can create a macro that puts a password in a file and uses MATCH and INDEX over and over again with conditional formatting to make a calander in excel (I doubt very much that you will use this).

Sub x_replace_sheet_name() Takes away current name of sheet in formulas
Sub x_Delete_cols() Deletes columns that are blank
Sub x_Delete_rows() Deletes rows that are blank
Sub x_fix_decimal() Fixes Problems with auto formatting
Sub x_find_rows() Finds number of rows in sheet
Sub x_find_cols() Finds number of cols in sheet
Sub x_colourTitles() Colours titles for the first column
Sub x_Create_Table_of_Contents_From_Sheet_Names() Creates Contents with links
Sub x_find_externl_links() Find External Links
Sub x_hide_sheets_after() Hide Sheets after Given Name

Functions in Generic Macros

In addition to macros, I put a bunch of user defined functions in the generic macros file. These macros do things like find the sheet name or the file name. They can also show who saved the file last and make a payback function. Unlinke the subroutine macros, you cannot just have the file open the functions. Instead, you must find the functions and then copy them into your file.

Function sheet_name(cell)
Function sum_labels(series)
Function File_name() As Variant
Function MyUDF(LastSaved1 As Boolean) As Double
Function Last_save_by() As Variant
Function LastSaved() As String
Function lookup_NA(lookup_value, test_array, result_array)
Function match_adj(lookup_value, lookup_array)
Function payback(series)
Function dpayback(d_rate, series)
Function period_of_year(period, timing)
Function show_formula(cell)
Function show_formulaR(cell)
Function show_formulaL(cell)

Functions for Working with Dates

The files below have functions that allow you to work with dates. Excel has a nice function that can be used a lot with the SUMIF function to aggregate data by quarter. But excel does not have a similar function for the end of a quarter. Functions that deal with dates are shown below. Another function that can be useful is one that evaluates how much time has occured between a month. This is very useful if you have a monthly model and are not assuming that each project starts or ends at the beginning or the end of a month.

 

Timing Functions

The file below has functions that allow you to interpolate with a lookup type function. To use this, you need to copy the functions into your file. You can make a blank macro in your file. Then go to the Lookup and Interpolate macro and copy the entire contents of the Function into the macro you created. It works like the LOOKUP function in excel.

List Box with Multiple Entries

The file below has macros that allow you to use the LISTBOX Form with MULTIPLE selections. It is also described on the graph page, but I included it here because you much attach a macro to the list box to make it work with multiple selections. The list box can be effective in making graphs and many other things. The way you need to attach the Listbox to a macro is illustrated in the file below.

 

The videos for various macros and the associated files are summarised in the following table:

 

	Subject		File		Video		Chapter Reference		Page Reference
	A video showing the short-cut keys that I use		Short Cuts.xlsm		https://www.youtube.com/watch?v=GprfRpmTPl0		Chapter 4		40
	Short-cuts and Data Table		Short Cuts and Bond Valuation		https://www.youtube.com/watch?v=f_t_hbLaVik		Chapter 4		40
	Overview of Generic Macros – Operations		Generic Macros.bas		https://www.youtube.com/watch?v=Hz8GfMv7VP8		Chapter 5		45
	Creating an Input Colour Macro from the F5 Function		Colour Macro.bas		https://www.youtube.com/watch?v=3Ino3YtZtcU		Chapter 5		48
	Working with macro that colours cells depending on the source tab colour		Colour Sheets Macro.bas		https://www.youtube.com/watch?v=UJ3pAKVbukg		Chapter 5		48
	Explanation of Shift CNTL R Macro that automatically copies to right from above		Fill to Right.bas		https://www.youtube.com/watch?v=YTZMPRt8J6I		Chapter 5		45
	Update and Improved SHIFT, CNTL, R		Fill to Right.xlsm		https://www.youtube.com/watch?v=ps1cXeJN-j8		Chapter 5		48
	Explanation of how to use and make Table of Contents Macro		Table of Contents Macro.bas		https://www.youtube.com/watch?v=FrPE0tJWfOA		Chapter 5		48
	How to make a macro that displays the comments in the sheet with check box		Comment box		https://www.youtube.com/watch?v=6gz1gHHmuwU		Chapter 5		48
	How to create User Defined Functions		Financial Library.xlsm		https://www.youtube.com/watch?v=QY743V2BGrw		Chapter 5		48
	Demonstrates how to make a better LOOKUP function without NA		Interpolate and Lookup		https://www.youtube.com/watch?v=D9vJYvjf3i8		Chapter 5		48
	Shows how to create look up function with interpolation		Lookup-Interpolate.xlsm		https://www.youtube.com/watch?v=sfokve3pRT0		Chapter 5		48
	Demonstrates making a Look up function with NA Adjustment to Zero		Lookup_NA.xlsm		https://www.youtube.com/watch?v=oVBLRPmaE3M		Chapter 5		48
	How to use the generic macro file with exporting and changing macros		Generic Macros.bas		https://www.youtube.com/watch?v=JpztPJxDmOI		Chapter 5		48
	How to make an Add Text function that combines text		Add Text.xlsm				Chapter 5		48
	Add Item to Menu in the Generic Macros file		Generic Macros.bas		https://www.youtube.com/watch?v=vEfTv3hVPDk		Chapter 5		48
	Create Function for Payback Period with intermediate decimal values		Payback		https://www.youtube.com/watch?v=7Cmbh_XMnks		Chapter 5		48
	Interpolate in Excel without Macro		Interpolate macro		https://www.youtube.com/watch?v=UAvwf-2nDlQ		Chapter 5		48
	Interpolate Macro with Selection.Cells		Interpolate macro		https://www.youtube.com/watch?v=3ArFFHZXI0g		Chapter 5		48
	Making a calander in excel		Calander		https://www.youtube.com/watch?v=FLtNFnnzs9Y		Chapter 5		48
	Installing Macros with File Import		Fill to Right.bas		https://www.youtube.com/watch?v=JvRndAlhp1w		Chapter 5		48
	Overview of Generic Macros – Code		Generic Macros.bas		https://www.youtube.com/watch?v=8-ezOD2YIkc		Chapter 5		45
	Installing Macros with File Import		Fill to Right.bas		https://www.youtube.com/watch?v=JvRndAlhp1w		Chapter 5		45
	Alternative ways to Import		Generic Macros.bas		https://www.youtube.com/watch?v=HBNxSzFXKR8		Chapter 5		45
	Importing Macros to Excel		Generic Macros.bas		https://www.youtube.com/watch?v=44Cp4cvq-LQ		Chapter 5		48
	Function and Macro (fm) File		fm.xlsm				Chapter 5		48
	Shows how to make macro to clear zeros from blank rows		western customer list.xlsm		https://www.youtube.com/watch?v=-di-zpxYlVQ
	…………………………………………………………………………………………….	…..	………………………………………………………..	….	………………………………………………………………………………………………	….	………………………………………………	….	…………………………………………………….