How to create new workbook with specific template Excel Vba

To create Workbook with Excel VBA, first thing we must specify which template we need to create such as Workbook with blank sheet, Chart, Macro, International Macro or Dialog. Each template represent by number or special command as below:

Workbook with Blank worksheet

Workbooks.Add(1)
    OR
Workbooks.Add(xlWBATWorksheet)

Workbook with Chart Worksheet

Workbooks.Add(2)
    OR
Workbooks.Add(xlWBATChart)

Workbook with Macro Worksheet

Workbooks.Add(3)
    OR
Workbooks.Add(xlWBATExcel4MacroSheet)

Workbook with International Macro Worksheet

Workbooks.Add(4)
    OR
Workbooks.Add(xlWBATExcel4IntlMacroSheet)

Base on my testing if we use Workbooks.Add(7) it will create Workbook with Dialog Worksheet. If we use number 5 or 6 the result is the same as Workbooks.Add(1) which Blank worksheet. If the number is greater than 7 "Run Time Error 1004 , Method Add of Object Workbooks Failed".

Below completed code to create Workbook with Blank Worksheet:

Option Explicit
Sub CreateWorkbook()
    
    'Variables Declaration
    Dim MstWB As Workbook
    
    'Change the number according to template
    Set MstWB = Workbooks.Add(1)
    
End Sub

Thank You

Microsoft Reference (Workbooks)
Microsoft Reference (Workbooks.Add)
Microsoft Reference (xlwbatemplate)

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)

Working with variables

To have better understanding in variables lets take look below example:
Sample without Variable:

Option Explicit
Sub example1()
  MsgBox(“Hisyam”)
End sub

Sample with Variable:

Option Explicit
Dim myName as String
  myName = “Hisyam”
  MsgBox(myName)
End sub

Open your Excel then VBE and insert module, copy paste the above code into this module and try to run both example1 and 2. There is no different in between because end result is the same. The different example1 we use constants and example2 we use variable to declare our name with myName. If you notice when we use constants we need to use quote before and after but not for variables. Use must remember this rule before get started because variables is more effective in repeating process like looping and for next. 

Constant = MsgBox(“Hisyam”) with quote before and after. 

Variable = MsgBox(myName) without quote.

Click here if you have any new project! Let us create for u for free. 

Thanks Good luck

How to declare variables in VBA?

Basically same with Visual Basic when declaring variables, you usually use a Dim statement. A declaration statement can be placed within a procedure to create a procedure-level variable. Or it may be placed at the top of a module, in the Declarations section, to create a module-level variable. Another method is to use Public statement to make this variable available to all procedures in the project. For Examples: Dim x as Integer (Commonly use) Public x as Integer x is our variables and the data range is in between -32,768 to 32,767 (Integer) which is reserve by computer to store x. Meaning if your data is more or less when you run this macro your computer either ignore or error message to trigger value is out of range. To have clearer picture lets try below example:

Option Explicit
Sub CalculateAndB()

  Dim ColA as Integer
  Dim ColB as Integer
  Dim Result as Integer
  ColA = Range(“A1”).Value
  ColB = Range(“B1”).Value
  Result = ColA*ColB ‘Multiply value of Range A1 and B1
  Msgbox (Result)

End Sub

Now copy the above code into your module and try. 1. Input 8 into Range A1 and 100 into Range B1 and Run the macro Your message box will prompt 800 (No problem because still within integer range) 2. Input 80 into Range A1 and 1000 into Range B1 and Run the macro You will receive error message “Run time error 6 Overflow”. This is because after multiply result will be 80,000 (Out of integer range) From the above example you should declare “Result” as Long instead of integer because Long can accept data range in between -2,147,483,648 to 2,147,483,648 Repeat the above testing but this time changed Dim Result as Integer ---à Dim Result as Long Sometimes we tend to forgot to declare if we have too many variables. As a reminder you can use “Option Explicit” statement above Sub or 1st line in a module. This will force you declare. Click here if you have any new project! Let us create for u for free. Thanks

What and Why we use variables in programming?

The opposite of a variable is a constant. Constants are values that never change. Because of their inflexibility, constants are used less often than variables in programming. Therefore when we need data which always change we need to use variables for example this expression X + Y. X and Y are variables. Normally we use variables when we know the data range only for example user input data, calculation and etc.
Below Table is to show what data type to declare and what range it will be. 1st Table is for Numeric (Numbers) and 2nd Table is for Non Numeric (Other than numbers).

Numeric Data Types


Non numeric Data Types


Every variable has a name, called the variable name, and a data type. A variable's data type indicates what sort of value the variable represents, such as whether it is an integer, a floating-point number, or a character. Variable name is not limit to X and Y only but you can use any name up to 255 characters if I’m not mistaken.

If you go to help in VBE the definition of variable as below:

Variable is a named storage location that can contain data that can be modified during program execution. Each variable has a name that uniquely identifies it within its scope. A data type can be specified or not.
Variable names must begin with an alphabetic character, must be unique within the same scope, can't be longer than 255 characters, and can't contain an embedded period or type-declaration character.
Actually you can use variable without specified data type but you have disadvantages:

1. If you have very long code then it will be very difficult for you to do the editing because you do not know which variables belong to which function.
2. Your program will be slower because it will consume your memory. If I’m not mistaken data type will be assume as variant (Highest range).
3. Affect your program accuracy because sometime you only need only numbers but end result may be not numbers.
   

Therefore please consider to declare your data type for variables when you start writing.
Good Luck
Click here if you have any new project! Let us create for u for free.
Thanks