How to add a VBA code that you’ve find on the internet into your Excel spreadsheet?

I see users of Excel shy away from using the VBA by saying that they don’t know VBA. I am a user of VBA and not a developer. I get most of the codes I use from online sources and make minor tweaks and adopt them into my own financial models. if like my friend who didn’t want to use the UDF because he thinks he doesn’t know how to write a code under VBA, then I want to tell you not to be intimidated by any of these things or don’t be a perfectionist and say I don’t know how to write the code so I will not use it! 95% of things we are using on daily basis, we are ignorent on how they function including electricity. So, just get into it and learn by doing instead of studying.

If you are new to VBA, I want you to follow these steps and insert your first VBA code in your model.

Task: Let’s say you want to add a table of content in your financial model and because you are lazy and smart at the same time, you want to automate this task.

Step 1: If you have the developer tab in your Excel ribbon then go to step 2. If however, you don’t have it, then follow the below stapes:

On the File tab, go to Options > Customize Ribbon.
Under Customize the Ribbon and under Main Tabs, select the Developer check box.

Step 2: Let’s find the code: So, go to google and search for “VBA code add table of content”. I am using the one from Microsoft community.

Copy to Clipboard

Option Explicit 
Sub Create_TOC() 
Dim wbBook As Workbook 
Dim wsActive As Worksheet 
Dim wsSheet As Worksheet 
Dim lnRow As Long 
Dim lnPages As Long 
Dim lnCount As Long 
Set wbBook = ActiveWorkbook 
With Application 
    .DisplayAlerts = False 
    .ScreenUpdating = False 
End With 
'If the TOC sheet already exist delete it and add a new 
'worksheet. 
On Error Resume Next 
With wbBook 
    .Worksheets("TOC").Delete 
    .Worksheets.Add Before:=.Worksheets(1) 
End With 
On Error GoTo 0 
Set wsActive = wbBook.ActiveSheet 
With wsActive 
    .Name = "TOC" 
    With .Range("A1:B1") 
        .Value = VBA.Array("Table of Contents", "Sheet # - # of Pages") 
        .Font.Bold = True 
    End With 
End With 
lnRow = 2 
lnCount = 1 
'Iterate through the worksheets in the workbook and create 
'sheetnames, add hyperlink and count & write the running number 
'of pages to be printed for each sheet on the TOC sheet. 
For Each wsSheet In wbBook.Worksheets 
    If wsSheet.Name <> wsActive.Name Then 
        wsSheet.Activate 
        With wsActive 
            .Hyperlinks.Add .Cells(lnRow, 1), "", _ 
            SubAddress:="'" & wsSheet.Name & "'!A1", _ 
            TextToDisplay:=wsSheet.Name 
            lnPages = wsSheet.PageSetup.Pages().Count 
            .Cells(lnRow, 2).Value = "'" & lnCount & "-" & lnPages 
        End With 
        lnRow = lnRow + 1 
        lnCount = lnCount + 1 
    End If 
Next wsSheet 
wsActive.Activate 
wsActive.Columns("A:B").EntireColumn.AutoFit 
With Application 
    .DisplayAlerts = True 
    .ScreenUpdating = True 
End With 
End Sub

Step 3: Go to your Excel file and open the VBA editor.

On the Developer tab, go to Visual Basic.
If you don’t see the Project Explorer window (something like the below), then you can activate it from the view tab.

Step 4: Now, on the project explorer, you see all the Excel file open. You want to insert the code in your workbook and not in the other workbooks.

under your workbook you should see 2 folders. One called Microsoft Excel Objects and another one called Modules. We want to add another module and insert the code.
right click on the module and Insert/Module

You should have a blank window, just copy and paste the code from the website in the blank window and you are done.

Close the VBA editor and run the macro

Step 4: don’t forget to mention the source where you’ve got the code from inside your VBA macro code.

Share This Story, Choose Your Platform!

Related Posts

My Intention When Reviewing a Financial Model: What I Can Improve and What I Can Learn

Project Finance Construction Risk Mitigation Measures: Funded Contingency Provision

What I will never tell to an entrepreneur