VBA Statement - Public

Public statement is to declare public variables and allocate storage space at module level.Instead of using Dim Statement we can use Public Statement. The different is Dim Statement declare inside the Sub procedure and Public Statement on top of module follow by Sub procedures. The variables declare by using Public statement can be use by all Sub procedures in all modules.

Syntax : Public Variable Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Public and Variable name, the rest is same as Dim Statement and Private Statement.

Note:
The only different between Private and Public statement is Public statement are available to all procedures in all modules but Private statement is limited to procedures inside specific module where variables is declare.

Read more about Public statement, excel training beginners, coding in vba,
excel training online, visual basic for applications at below links.

Microsoft Reference-Public-statement
Other Reference-Lifetime-scope-global-level

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Private

Private statement is to declare private variables and allocate storage space in module level. Instead of using Dim Statement we can use Private Statement. The different is Dim Statement declare inside the Sub procedure and Private Statement on top of module follow by Sub procedures. The variables declare using Private statement can be use by all Sub procedures inside the module where this variables are declares. This variables can't use outside the declare module.

Syntax : Private Variable Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Private and Variable name, the rest is same as Dim Statement.

VBA Vode:

Option Explicit
Private MyVarA As String, MyVarB As String
Sub Macro1()
    MyVarA = "My Country"
    myVarB = "Malaysia"
    Debug.Print MyVarA, myVarB
End Sub
Sub Macro2()
    Debug.Print MyVarA, myVarB
End Sub

Note:
For the above example we have 2 variables MyVarA and MyVarB, we have 2 Sub procedures which is Macro1 and Macro2. If we run Macro1 then follow by Macro2 by using the same variables then we get the same answer unless we reset these variables each time before exit Sub procedures.

Read more about Private statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Private-statement
Other Reference-Lifetime-scope-module-level

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Sub, Private Sub, Public Sub

Sub statement is subroutine also known as Procedures or Macros (When we record macro the default name is Sub Macro1() follow by series of code and close with End Sub. Macro1 is the default name of procedure recorded. Same case when we create on our own.we start with Sub Procedure Name() and Close with End Sub.

Syntax :
Sub Procedure Name (ArgList)
    Statements (Code)
    Exit Sub
    Statements (Code)
End Sub

Actually the syntax consist many optional parts but required only 3 which is Sub, Procedure name and End Sub to explain in detail refer below:

Case Study 1: 

Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This is the most typical of Sub Statement, it can be accessible from anywhere in the project and listed in macros and can run directly.

Case Study 2: 

Sub Procedure Name (ByVal and ByRef Variables)
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement only can be access from another Sub Statement in the project because we need to specify variables inside open and close bracket and not listed in macros.

Case Study 3: 

Public Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement same as typical Sub Statement above, it can be accessible from anywhere in the project and listed in macros and can run directly.

Case Study 4: 

Private Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement only can be access from another Sub Statement within the same module and not listed in macros.

Note:

1. There are another Sub which Friend Sub (only class module) and Static Sub (preserved variables) but seldom use for beginner.
2. The procedure name must be related with the macro task to ease accessible process and there are rules to follow ex. no space, certain characters and etc.

Read more about Sub statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Sub-statement
Other Reference-Subroutines

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Redim and Redim Preserve

Redim statement is to reallocate storage space for dynamic array variables and declare at procedure level within your Sub and End Sub. Redim statement only can be use after we declare Dim Statement for array for example Dim MyVar() As String and data remain unchanged. Array number in bracket is not specify.

Syntax : Redim Variables Name As Data Type Or
               Redim Preserve Variables Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Redim and Variable name, to explain in detail refer below:

Case Study 1:
Dim myVar(5) As String
Lower Bound (LBound) control by Option Base 1 (Declare at module level) if present then LBound = 1 otherwise 0, Upper Bound (UBound) is 5. For this case we don't have to use ReDim statement because LBound and UBound already specify. If we try to use myVar(6) or more then Run time error '9' : Subscript out of range. If we try to resize by using Redim myVar(6) or more then Compile error: Array already dimensioned.

Case Study 2:
Dim myVar(5 to 10) As String
This case same as above except Lower bound already specify equal to 5 and Upper Bound equal to 10. Redim is not necessary.

Case Study 3:
Dim myVar() As String
This is refer to dynamic array and Redim Statement is compulsory. For example we already Redim myVar(1) and assigned the value, later we Redim myVar(2) and assigned the value. The myVar(1) value will be deleted or erase. To avoid this we must use Redim Preserve myVar(1) and Redim Preserve myVar(2). For Redim Preserve once we reverse the sequence the data also lost for the sub sequence number.

Note:
Redim Statement - Will erase all previous data.
Redim Preserve Statement - Will not erase previous data.

Read more about Redim statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Redim-statement
Other Reference-Redim-statement
Other Reference-Redim-preserve

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Dim

Dim statement is to declares variables and allocates storage space. For example Dim StrVar as String, Dim Int As integer and etc which is assigning any variables to data type or object.

Syntax : Dim Variable Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Dim and Variable name, to explain in detail refer below:

1. Dim VarName
   This to declare VarName as default which is Variant.
   
2. Dim VarName as String
   This to declare VarName as String.
3. Dim VarName1 as string, VarName2 as string
   This to declare both VarName1 and 2 as String.
4. Dim VarName1,VarName2 As Integer,VarName3 as Integer
   This to declare VarName1 default (Variant),VarName2 as integer and VarName3 as integer.

 Basically these 4 methods we can use to declare Variables.

VBA Vode:

Option Explicit
Sub Examples_DimDeclares()
    
    Dim myVar
    Dim MyVar1, MyVar2
    Dim MyVar3 As String, myVar4 As Integer
    Dim MyVar5, MyVar6 As Integer, MyVar7 As String

End Sub

Note:

1. Don't use same Variable name with number because more likely become array. The above example just for reference.
2. If we declare Variables with same name then Compile error: Duplicate declaration in current scope.

Read more about Dim statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Dim-statement
Other Reference-Dim-statement

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

Working with Select Case Statement

Select case statement is more readable compare with If Then Else statement if the condition to many level for example below table:

No.

Condition

Grade

Bg Color

1

Score More Than >80

A

Green

2

Score More Than >60

B

Yellow

3

Score More Than >40

C

Blue

4

Other Score

D

Red

Base on the above table we have 4 conditions to test and give different result into below table.

VBA Code:

Option Explicit
Sub SelectCaseExample()

    Dim i As Integer
    Dim IntScore As Integer
    With ActiveSheet
        i = 2
        Do
            IntScore = .Range("C" & i)
            Select Case IntScore
                Case Is > 80
                    .Range("D" & i) = "A"
                    .Range("D" & i).Interior.ColorIndex = 4
                Case Is > 60
                    .Range("D" & i) = "B"
                    .Range("D" & i).Interior.ColorIndex = 6
                Case Is > 40
                    .Range("D" & i) = "C"
                    .Range("D" & i).Interior.ColorIndex = 5
                Case Else
                    .Range("D" & i) = "D"
                    .Range("D" & i).Interior.ColorIndex = 3
            End Select
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With

End Sub

Result after run above code.

Microsoft Reference - Select Case Statement

Practice makes perfect. Thank You.

Convert Excel Table into HTML table with VBA code

Actually to convert Excel table into HTML table is to use Save As web page either whole sheet or selection but we also can use VBA code below to convert by selecting table range example below and run the code. New pop up window will open notepad with HTML code inside and immediately can paste into your blog or webpage.

Run below code to get HTML code inside notepad.

VBA Code:

Option Explicit
Sub ToCreateTableForSelectedRange()
    Dim OpnShell As Variant
    Dim FSO, fs As Object
    Dim StrPath As String
    
    Dim iRow As Integer, iCol As Integer
    Dim Cell As Range, Rng As Range
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, x As Integer
    
    Dim DbWitdh() As Double, DbWitdhTot As Double
    Dim DbWitdhP() As String, DbWitdhAcc As Double
    Dim iColSp As Byte
    
    Dim Qto As String, ClEnd As String
    Dim BdOpn As String, BdCls As String
    Dim TdOpn As String, TdCls As String
    Dim TrOpn As String, TrCls As String
    Dim TblOpn As String, TblCls As String
    Dim StrStyle As String, StrTblWd As String
    Dim StrCellSp As String, StrCellPd As String
    Dim StrBdrCol As String, StrBdr As String
    Dim TBdOpn As String, TBdCls As String
    Dim StrCS As String, StrWd As String, StrAl As String
    
    Qto = """": ClEnd = ">"
    BdOpn = "<b>": BdCls = "</b>"
    TdOpn = "<td": TdCls = "</td>"
    TrOpn = "<tr>": TrCls = "</tr>"
    StrStyle = " style=" & Qto & "border-collapse: collapse;"
    StrTblWd = " width: 500px;" & Qto
    StrCellSp = " cellspacing=" & Qto & "0" & Qto
    StrCellPd = " cellpadding=" & Qto & "3" & Qto
    StrBdrCol = " bordercolor=" & Qto & "#000000" & Qto
    StrBdr = " border=" & Qto & "1" & Qto
    TblOpn = "<table" & StrStyle & StrTblWd & StrCellSp & _
    StrCellPd & StrBdrCol & StrBdr & ClEnd
    TblCls = "</table>"
    TBdOpn = "<tbody>": TBdCls = "</tbody>"
    StrCS = " colspan=" & Qto: StrWd = " width=" & Qto
    StrAl = " align=" & Qto & "Center" & Qto
    
    Set Rng = Selection
    If Rng.Rows.Count < 2 Or Rng.Columns.Count < 2 Then
        MsgBox "Please select range with table!": GoTo Line1
    End If
    
    'To Create new Text File
    StrPath = VBA.Environ("UserProfile") & "\Desktop\TableGenerator.txt"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'To Check files existance and delete
    On Error Resume Next
    If Dir(StrPath) <> "" Then Kill StrPath
    
    k = 1
    For Each Cell In Rng
        If Cell.Row > iRow And iRow > 0 Then Exit For
        If iRow = 0 Then iRow = Cell.Row
        If iCol = 0 Then iCol = Cell.Column
        ReDim Preserve DbWitdh(k): DbWitdh(k) = Cell.ColumnWidth
        DbWitdhTot = DbWitdhTot + DbWitdh(k)
        iColSp = k
        k = k + 1
    Next

    For x = LBound(DbWitdh) + 1 To UBound(DbWitdh)
        ReDim Preserve DbWitdhP(x)
        If x < UBound(DbWitdh) Then
            DbWitdhP(x) = Round((DbWitdh(x) / DbWitdhTot) * 100, 0)
            DbWitdhAcc = DbWitdhAcc + DbWitdhP(x)
        Else
            DbWitdhP(x) = 100 - DbWitdhAcc
        End If
    Next x
    
    k = 1
    If Dir(StrPath) = "" Then
        Set fs = FSO.CreateTextFile(StrPath, 2)
        With fs
            .WriteLine TblOpn & TBdOpn
            For i = iRow To iRow + Rng.Rows.Count - 1
                l = 1
                .WriteLine TrOpn
                For j = iCol To iCol + iColSp - 1
                    If k = 1 Then
                        .WriteLine TdOpn & StrCS & iColSp & Qto & _
                        StrWd & DbWitdhP(l) & "%" & Qto & StrAl & _
                        ClEnd & BdOpn & Cells(i, j) & BdCls & TdCls
                    Else
                        .WriteLine TdOpn & StrCS & iColSp & Qto & _
                        StrWd & DbWitdhP(l) & "%" & Qto & StrAl & _
                        ClEnd & Cells(i, j) & TdCls
                    End If
                    l = l + 1
                Next j
                .WriteLine TrCls
                k = k + 1
            Next i
            .WriteLine TBdCls & TblCls
        End With
    End If
    
    'Just To Open NotePad Created
    If Err = 0 Then
        OpnShell = Shell("C:\Windows\System32\notepad.exe " & _
        StrPath, vbNormalFocus)
    End If
    On Error GoTo 0
    
Line1:
    'Reset Variables
    iRow = 0: iCol = 0
    Set Cell = Nothing: Set Rng = Nothing
    Erase DbWitdh: DbWitdhTot = 0
    Erase DbWitdhP: DbWitdhAcc = 0
    iColSp = 0
End Sub

The result in HTML as below:

No.				Subject				Score				Grade
1				English				100
2				Mathematics				65
3				Science				30
4				Physics				55
5				History				20
6				Chemistry				48
7				Biology				70
8				Geography				90

Practice makes perfect. Thank You.

Working with If...Then...Else

If...Then...Else statement is to test certain condition either true or false and can be tested as many level as per required. Basically can be divide into 4 types:

Below example base on Table subject vs score, decide what to do base on score.

1. Single condition with single line return (If...Then)
   
   
   

   No.			Condition			Return
   1			Score more than 50			Range background color to green

   VBA Code:

   Option Explicit
   Sub IFStatemantsSingleConditionSingleReturn()
   
       Dim i As Integer
       With ActiveSheet
           i = 2
           Do
               If .Range("C" & i) > 50 Then _
               Range("A" & i & ":C" & i).Interior.ColorIndex = 4
               i = i + 1
           Loop While .Range("A" & i) <> ""
       End With
       
   End Sub
   
   
2. Single condition with multiple line return (If...Then...End If)
   
   
   

   No.			Condition			Return
   1			Score more than 50			Range background color to green Font text bold

   VBA Code:

   Option Explicit
   Sub IFStatemantsSingleConditionMultipleReturn()
       
       Dim i As Integer
       With ActiveSheet
           i = 2
           Do
               If .Range("C" & i) > 50 Then
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 4
                   Range("A" & i & ":C" & i).Font.Bold = True
               End If
               i = i + 1
           Loop While .Range("A" & i) <> ""
       End With
       
   End Sub
   
   
3. Double condition with multiple line return (If...Then...Else...End if)
   
   
   

   No.			Condition			Return
   1			Score more than 50			Range background color to green Font text bold
   2			Others			Range background color to Yellow

   VBA Code:

   Option Explicit
   Sub IFStatemantsDoubleConditionMultipleReturn()
       
       Dim i As Integer
       With ActiveSheet
           i = 2
           Do
               If .Range("C" & i) > 50 Then
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 4
                   Range("A" & i & ":C" & i).Font.Bold = True
               Else
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 6
               End If
               i = i + 1
           Loop While .Range("A" & i) <> ""
       End With
       
   End Sub
   
   
4. Multiple condition with multiple line return (If...Then...Elseif...Then...Else...End if)
   
   
   

   No.			Condition			Return
   1			Score more than 80			Range background color to green Font text bold
   2			Score more than 50			Range background color to Yellow
   3			Others			Range background color to Red

   VBA Code:

   Option Explicit
   Sub IFStatemantsMultipleConditionMultipleReturn()
       
       Dim i As Integer
       With ActiveSheet
           i = 2
           Do
               If .Range("C" & i) > 80 Then
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 4
                   Range("A" & i & ":C" & i).Font.Bold = True
               ElseIf .Range("C" & i) > 50 Then
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 6
               Else
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 3
               End If
               i = i + 1
           Loop While .Range("A" & i) <> ""
       End With
       
   End Sub

Note:
Normally select case statement is more readable instead or using If...Then...Else statement with multiple condition and If...Then...Else statement can be place inside another if statement but be careful to close with End if properly.

Microsoft Reference - If..Then..Else statement

Practice makes perfect. Thank You.

Working with For...Next statements

For...Next statement is another type of loop but without check or condition to test. The loop is fix between numbers. Base on table below we are going to use For...Next statement to get total monthly sales.

VBA Code:

Option Explicit
Sub ForNextStatements()
    
    Dim i As Integer
    For i = 3 To 14
        Range("D" & i) = Range("B" & i) + Range("C" & i)
    Next i
    
End Sub

To do in reverse way:

Option Explicit
Sub ForNextStatementsRev()
    
    Dim i As Integer
    For i = 14 To 3 Step -1
        Range("D" & i) = Range("B" & i) + Range("C" & i)
    Next i
    
End Sub

Note:
After Next without mentioned i also work perfectly but prefer to add i after Next because whenever your code getting complicated then difficult to trace For..Next loop overlapping.

Microsoft Reference - For..Next statement

Practice makes perfect. Thank You.

Working with For Each...Next statements

For Each...Next statement is another type of loop but without check or condition to test. The loop is fix between certain range or numbers. Base on table below we are going to use For Each...Next statement to get total monthly sales.

Before proceed we must understand basic offset function.

The formula for offset = Range().Offset ([RowOffset], [ColumnOffset]) for example:
Range("A1") = Range("B1").offest(0,-1) which is refer to same cell or range.

VBA Code:

Option Explicit
Sub ForEachNextStatements()

    Dim RngS As Range
    Dim MyRng As Range
    
    Set MyRng = ActiveSheet.Range("D3:D14")
    For Each RngS In MyRng
        RngS = RngS.Offset(0, -2) + RngS.Offset(0, -1)
    Next
    
End Sub

Another Example to Loop every Sheets:

Option Explicit
Sub ForEachNextStatementsSht()
    
    Dim MySht As Worksheet

    For Each MySht In Worksheets
        'Your code goes here
        Debug.Print MySht.Name
    Next
    
End Sub

Another Example to Loop every Cells:

Option Explicit
Sub ForEachNextStatementsCell()
    
    Dim Cell As Range

    For Each Cell In ActiveSheet.UsedRange
        'Your code goes here
        Debug.Print Cell.Value
    Next
    
End Sub

Microsoft Reference - For Each..Next statement

Practice makes perfect. Thank You.

Working with Do...Loop statement

 Basically Do...Loop statement consist 2 types:

• While Condition
  Either Check First or Check Last
  
• Until Condition
  Either Check First or Check Last

Before proceed we must understand basic operator as below:

Basic Operator
1	<	Less than
2	<=	Less than or equal to
3	>	Greater than
4	>=	Greater than or equal to
5	=	Equal to
6	<>	Not equal to
7	""	Empty

Base on table below we are going to use Do..Loop statement to get total monthly sales.

In case we use Range("A3") to Range("A14"). For Do...Loop While Check whether Not Equal to empty (<> "") but for Do...Loop Until  Check whether Equal to empty (=""). Under certain circumstances the result may be different can cause bug in your code. Please be careful.

VBA Code:

1) While Condition Check first.

Option Explicit
Sub DoWhile_CheckFirst()
    
    Dim i As Integer
    i = 3
    With ActiveSheet
        Do While .Range("A" & i) <> ""
            .Range("D" & i) = .Range("B" & i) + .Range("C" & i)
            i = i + 1
        Loop
    End With

End Sub

2) While Condition Check last.

Option Explicit
Sub DoWhile_CheckLast()
    
    Dim i As Integer
    i = 3
    With ActiveSheet
        Do
            .Range("D" & i) = .Range("B" & i) + .Range("C" & i)
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With

End Sub

3) Until Condition Check first.

Option Explicit
Sub DoUntil_CheckFirst()
    
    Dim i As Integer
    i = 3
    With ActiveSheet
        Do Until .Range("A" & i) = ""
            .Range("D" & i) = .Range("B" & i) + .Range("C" & i)
            i = i + 1
        Loop
    End With

End Sub

4) While Condition Check last.

Option Explicit
Sub DoUntil_CheckLast()
    
    Dim i As Integer
    i = 3
    With ActiveSheet
        Do
            .Range("D" & i) = .Range("B" & i) + .Range("C" & i)
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note:
Do Loop without counter will create infinite loop and your computer will hang. The counter for above example is i = i + 1.

Microsoft Reference - Do..Loop statement
Microsoft Reference - Using Do..Loop statement

Practice makes perfect. Thank You.

How to force variables declaration before start VBA code?

Variables declaration is very importance before start writing any code in VBA because:

• More organize whenever code is getting complicated or longer otherwise we get lost somewhere and stuck (Normally variables declaration is on top of any procedure).
• Computer memory limitation slowing down the processing because without declaration default is set as variant.
• More easy to write especially on object example Workbook, Worksheet and etc. This is because we can use Tools tip suggestion whenever we key in dot after variables.
• Simplify code because we only assigned this variables one time but can be use many time. For example:
  Dim StrPath As String
  StrPath = "C:\Users\UserName\Desktop\MyFiles.xlxs"
  We use StrPath instead of full path and can be repeated many time.

To force declaration either Dim, Private, Public, ReDim, or Static statements, we use Option Explicit statement on top of module before any procedures. Variables without declaration will be warn as below.

Microsoft Visual Basic for Applications: Compile error: Variable not defined

To set as default whenever module inserted:

• Under Visual Basic Editor Click Tools follow by Option.
  
  
  
  
  
• Under Options window select Editor Tab and tick Require Variable Declaration.
  
  
  
  
  
• Click OK. Done

Note: Every time Module inserted "Option Explicit" always on top.

Microsoft Reference - Option Explicit Statement

Practice makes perfect. Thank You.

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 create Folder and Subfolders with vba code

Normally to create a new folder just right click empty area at Desktop or inside any folders then click at New and click folder. By default the folder name is New Folder and we have to rename accordingly but if the folder name already exist then we have give another name. 

How to create Folder and Sub folders with VBA code?

A few items need to consider in order to create folder:

• Path or Directory
• Folder Existence
• Create Folder
  

Path or Directory - Common Directory:

• C (C:)
  
• Documents (C:\Users\Username\Documents)
  
• Desktop (C:\Users\Username\Desktop)

The problem is Username not fixed, it could be Admin, Guest or any name given. In order to get actual Username we use below code:

VBA.Environ ("UserProfile") 'This will give us C:\Users\Username
    'OR
VBA.Environ ("Username") 'This will give us Username

Folder existence

To check folder exists or not we use below code:   

YourPath = Environ("UserProfile") & "\" & "Desktop" & "\" & "Your Folder Name Here"
If Dir(YourPath, vbDirectory) = "" Then
     MkDir YourPath
End If

Create Folder

To create folder we use

MkDir YourPath

Below completed code to create folder and sub folder:

Option Explicit

Sub CreatingFolderAndSubFolder()
'Declare Variables
Dim StrOuterPath As String, StrInnerPath As String

'To Create New Folder name "Main Folder"
StrOuterPath = VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "Main Folder"

'To Create New Folder Inside Main Folder name "Sub Folder"
StrInnerPath = VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "Main Folder" & "\" & "Sub Folder"

'To Check Main Folder existence
If Dir(StrOuterPath, vbDirectory) = "" Then
    'To Create Main Folder
    MkDir StrOuterPath
    'To Create Sub Folder
    MkDir StrInnerPath
    MsgBox "Main and Sub Folder Successfully created"
Else
    'To Check Sub Folder existence
    If Dir(StrInnerPath, vbDirectory) = "" Then
        'To Create Sub Folder
        MkDir StrInnerPath
        MsgBox "Sub Folder Successfully created"
    Else
        MsgBox "Main Folder and Sub Folder already exist"
    End If
End If

'Reset Variables
StrOuterPath = "": StrInnerPath = ""

End Sub

Thank You

Microsoft Reference (MkDir)