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

How to save excel file using VBA

 To save excel file we must know file extension because in excel too many format for example *.xls, *.xlsx, *.xlsm and etc. For XlFileFormat enumeration we can refer Here. 

Complete syntax we can refer here

Workbook.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

The most importance input is FileName and FileFormat the rest is optional. For FileName we must provide full path and file name. For example .xlsx (51),.xlam (55) and etc.

Below completed code to create Workbook and Save instantly:

Option Explicit
Sub CreateWorkBookAndSaveInSpecificFolder()
    
    'Variables Declaration
    Dim MstWB As Workbook
    Dim StrFullPath As String, StrFldrPath As String
    
    'Assign Variable according to folder path (Ex. folder name is "My Analysis" at Desktop
    StrFldrPath = VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "My Analysis"
    
    'Assign Variable according to full path
    StrFullPath = StrFldrPath & "\" & "mytemplate.xlsx"
    
    'To check folder exist or not and create if none
    If Dir(StrFldrPath, vbDirectory) <> "" Then
        GoTo Line1
    Else
        MkDir StrFldrPath
    End If
Line1:
    
    'Create New Workbook with normal template
    Set MstWB = Workbooks.Add(1)
    
    'Create Workbook Content for ex. Header
    With MstWB.ActiveSheet
        .Range("A1") = "No."
        .Range("B1") = "Name"
        .Range("C1") = "Address"
        .Range("D1") = "Phone No."
    End With
    
    'Stop prompt user to replace file or not but it will replace instantly
    Application.DisplayAlerts = False
    
    'Save workbook according to file extension
    MstWB.SaveAs Filename:=StrFullPath, FileFormat:=51, CreateBackup:=False
    
    'Reset Stop prompt user back to default
    Application.DisplayAlerts = True
    
    'Close Workbook without saving
    MstWB.Close False

End Sub

Thank You

Microsoft Reference (Save As)
Microsoft Reference (File Format)
Microsoft Reference (Display Alert)

How to create new workbook with customize template Excel Vba

To start we must create our own template and save this template some where, in this example we save this template under Desktop and file name as mytemplate.xlxs.

The Path should be C:\Users\UserName\Desktop\mytemplate.xlxs, but we can't use UserName directly because different user the name given will different. We must replace UserName as below:

 VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "mytemplate.xlsx"

Refer my posting about Folder and Sub Folder Here.

Below completed code to create Workbook with customize Template:

Option Explicit
Sub CreateWorkBookWithCustomTemplate()
    
    'Variables Declaration
    Dim MstWB As Workbook
    Dim StrPath As String
    
    'Assign Variable according to template path
    StrPath = VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "mytemplate.xlsx"
    
    If Dir(StrPath, vbDirectory) <> "" Then
    
        'Create New Workbook with Custom template
        Set MstWB = Workbooks.Add(StrPath)
    Else
        'To prompt message if no template found
        MsgBox "No Template found. Please create template at Desktop."
    End If

End Sub

Thank You