How to open Visual Basic Editor under Microsoft Excel?

Visual Basic Editor (VBE) is the place to write and store all Visual Basic code and Macro recorded code. This VBE attached to all office application for example Microsoft Word, Microsoft PowerPoint and etc. but in this blog our focus only VBE in Microsoft Excel. To open this VBE:

Two ways to open Visual Basic Editor in Excel:

1. 1st Method: Under Developer tab click at Visual Basic.
   
   
   
   
   
   
   
   
   
   
   
   Click here if Developer tab is not visible.
   
   
2. 2nd Method: Short cut key by Pressing Alt + F11 button.
   
   
3. Visual Basic Editor will open as below:
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

 

Practice makes perfect. Thank You.

Get started with all Function and Formula in Excel

To be a good programmer in Microsoft Excel we should understand every function in excel itself. To open all these function:

1. Click Home tab.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Under Home tab Click at Arrow down button beside sum symbol.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Click at More Functions.
   
   
4. Select a category.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
5. Select a function and this function or formula will be inserted into selected cell.
   

All this functions already build in and can be use instantly or call using VBA code. To create new function we must create using VBA code inside Excel workbook and this will appeared under this insert function dialog box.

The most importance function is under Math & Trig which is related to number  and Text which is related to string.

Get started with recording macros to learn VBA code

Better way to learn VBA code is to record every move or action and check the recorded code. From here we can learn and modify the code to suit your need. 

To record macro:

1. Under Developer tab Click at Record Macro.
   
   
   
   
   
   
   
   
   
   
   
   
2. Under Record Macro give your Macro name.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   Default name given is Macro1,2,3..... but we can rename to differentiate the function.
   Macro name must begin with letter, maximum character 255 and must not contains space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.
   Detail we refer here:
   Microsoft Reference - Macro naming rules
   
   Note: Under this dialog box you can choose to store recorded macro under This workbook, New Workbook or Personal Macro Workbook and also create short cut key to run this macro later.
   
   
3. Click OK to begin recording and continue your work in Excel as usual.
   
   
4. Upon completed click at Stop Recording.
   
   
   
   
    
   
   
   
   
   
   
   
5. Save this file under Excel Macro-Enabled Workbook(*.xlsm).
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

 

 To playback recorded macro:

1. Under Developer tab Click at Macros.
   
   
   
   
   
   
   
   
   
   
   
   
2. Under Macro select Macro name: and click Run.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   Note: Under this dialog box you can choose macros in This Workbook or All Open Workbook.
   
   
3. All the step recorded before run automatically.

To check recorded code:

1. Under Developer tab Click at Macros.
   
   
   
   
   
   
   
   
   
   
2. Under Macro select Macro name: and click Edit.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Visual Basic Editor will open and show code.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   The code will be under module, try to understand and edit the code and run again and again.
   

Practice makes perfect. Thank You.

How to set security to enable macros in Microsoft Excel?

By default any Macro or VBA code inside or Excel file will be disable for security reason. Therefore we must understand the risk but for me as long we know the source then should be OK.

Follow this step to change or set the security to enable macros:

METHOD 1:

1. Open any Excel file and click at File top left.
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Click at Options below left.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Click at Trust Center under Excel Option.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
4. Click at Trust Center Setting under Excel Option.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
5. Click at Macro Settings under Trust Center.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   Choose either one but since we know the source code prefer to choose no 4 which is
   "Enable all macros (not recommended; potentially dangerous code can run)" to ease our VBA code writing later on.
   
   
6.  Click OK.
   

          OR

METHOD 2:

1. Click at Developer tab on top follow by Macro Security.
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Repeat Step 5 and 6 in Method 1.
   

Macro security setting is completed.

Microsoft Reference

How to show Developer tab in Microsoft Excel?

Before started your 1st code in VBA we need to show Developer tab in order to use all function for example Visual Basic Editor, Macros, Record Macro, Macro Security, Add-ins, Control and XML as below:

 

By default is hidden we have to show this tab by using below step:

1. Open any Excel file and click at File top left.
   
   
   
   
   
   
   
   
   
   
   
2. Click at Options below left.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Click at Customize Ribbon, Under this check Developer check box.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
4. Click OK. 
   

The Developer tab automatically show. Done.

How to Get a List of Folders and Files Name from Selected Folder with VBA

 To compared files inside between folders it will be much easier if we have a list of folders and files name inside folder 1 and folder 2 in excel sheet then we can use formula true and false  or VLOOKUP function. To get the list we can use VBA code below:

Option Explicit
Sub GetFordersAndFilesNameInSelectedFolder()
    Dim pPath As String
    Dim FileName As String
    Dim MstWB As Workbook, MstWS As Worksheet
    Dim i As Integer
    
    'Open Dialog Box To Select Folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo Line1
        pPath = .SelectedItems(1)
    End With
    
    'To ensure path end with slash
    If Right(pPath, 1) <> "\" Then
        pPath = pPath & "\"
    End If
    
    'Assign Filename string
    FileName = Dir(pPath, vbDirectory)
    
    'Create New Workbook with normal template
    Set MstWB = Workbooks.Add(1)
    Set MstWS = MstWB.ActiveSheet
    
    'Create Header
    MstWS.Range("A1") = "No."
    MstWS.Range("B1") = "Name"
    
    'Start Row to fill in
    i = 2
    
    'Loop To Get All File and Folrder name inside the folder
    Do While FileName <> ""
        If Left(FileName, 1) <> "." Then
            MstWS.Range("A" & i) = i - 1
            MstWS.Range("B" & i) = FileName
            i = i + 1
        End If
        FileName = Dir()
    Loop
    
    'Formatting
    MstWB.Activate
    MstWS.Rows(1).Font.Bold = True
    MstWS.Cells.EntireColumn.AutoFit
    MstWS.Cells.HorizontalAlignment = xlLeft
    ActiveWindow.WindowState = xlMaximized
    
Line1:
    
    'Clear Variables
    Set MstWB = Nothing
    Set MstWS = Nothing
    FileName = "": pPath = ""
End Sub

To use this code:

• Copy this and paste into module and Run this code
• Select any single folder
• New workbook will be created
• All folders and files name will be listed in Sheet1

Please try and give us feedback.Thanks You