Excel VBA Lesson 2 Message Box
Excel VBA Lesson 1(a) Message Box
Syntax
MsgBox(prompt[,buttons][,title][,helpfile,context])
Message Box type
vbOKOnly Shows only the OK button
vbOKCancel Shows the OK and Cancel buttons
vbAbortRetryIgnore Shows the Abort, Retry, and Ignore buttons
vbYesNo Shows the Yes and No buttons
vbYesNoCancel Shows the Yes, No, and Cancel buttons
vbRetryCancel Shows the Retry and Cancel buttons
vbMsgBoxHelpButton Shows the Help button. For this to work, you need to use the help and context arguments in the MsgBox function
Example:-
Sub MsgBoxYesNo() MsgBox "Should we stop?", vbYesNo End Sub
Excel VBA Lesson 4 Range
Excel VBA Lesson 4 Range
Activate
AddComment
AdvancedFilter
Calculate
Clear
ClearComments
ClearContents
ClearFormats
ClearHyperlinks
Copy
CopyFromRecordset
Cut
Delete
Find
Insert
Merge
PasteSpecial
------------------------------------------------------------------------------------
VBA Activate Range – Examples
Sub Activating_Single_Cell()
Range("A2").Activate
'or
Cells(2, 1).Activate
End Sub
------------------------------
Sub Activating_multiple_Cells()
Range("A2:C3").Activate
End Sub
-----------------------------------
Sub Activating_multiple_Cells()
Range(Cells(2, 1), Cells(3, 3)).Activate
End Sub
----------------------------------------------------------------------------
VBA Add Comments to Excel Range -
Range("A2").AddComment "Hello, this is a comment text"
-------------------------------------------------------------------------
Excel VBA Range Advanced Filter-
Sub VBA_Range_Advanced_Filter()
Range("A3:B8").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("B1:B2"), Unique:=False
End Sub
-----------------------------------------------------
VBA Range Calculate –
Sub VBA_Calculate_Range()
Range("A2:D10").Calculate
End Sub
--------------------------------------------------------
VBA Clear Range in Excel explained
Range("A2:D10").Clear
Excel VBA to ClearComments in a Range
Range("A2:D10").ClearComments
Excel VBA to ClearContents in a Range
Range("A2:D10").ClearContents
Excel VBA to ClearFormats in a Range
Range("A2:D10").ClearFormats
Excel VBA to ClearHyperlinks in a Range
Range("A2:D10").ClearHyperlinks
--------------------------------------------------------------------
VBA to Copy Range in Excel
Sub Copy_Range_To_Clipboard()
Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
--------------------------------------------------------------------
VBA to Copy Range to Destination in Excel
Sub Copy_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")
End Sub
--------------------------------------------------------------
VBA to Cut Range in Excel
Sub Cut_Range_To_Clipboard()
Range("A2:D10").Cut 'This will cut the source range and copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
--------------------------------------------
VBA to Cut and paste the Range to Destination in Excel
Sub Cut_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Cut Destination:=Range("E2")
End Sub
----------------------------------------------------------------
VBA to Copy Range in Excel –
Sub Copy_Range_To_Clipboard()
Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
------------------------------------------------------------
VBA to Copy Range to Destination in Excel
Sub Copy_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")
End Sub
-------------------------------------------------------------------
VBA to Copy Range in Excel
Sub Copy_Range_To_Clipboard()
Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
-----------------------------------------------------------------------
VBA to Copy Range to Destination in Exce
Sub Copy_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")
End Sub
-----------------------------------------------------------------------
VBA to Cut Range in Excel
Sub Cut_Range_To_Clipboard()
Range("A2:D10").Cut 'This will cut the source range and copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
--------------------------------------------------------------------------
VBA to Cut and paste the Range to Destination in Excel
Sub Cut_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Cut Destination:=Range("E2")
End Sub
---------------------------------------------------------
VBA to Delete Range in Excel
Sub Delete_Range_To_ShiftLeft()
Range("B2:D10").Delete Shift:=xlToLeft
End Sub
-------------------------------------------------
VBA to Delete Range in Excel – Example:Shift:=xlToUp
Sub Delete_Range_To_ShiftUp()
Range("B2:D10").Delete Shift:=xlToUp
End Sub
-----------------------------------------------------
VBA to Delete Range in Excel – Example: EntireRow
Sub Delete_Range_EntireRow()
Range("B2:D10").EntireRow.Delete
End Sub
------------------------------------------------
VBA to Delete Range in Excel – Example: EntireColumn
Sub Delete_Range_EntireColumn()
Range("B2:D10").EntireColumn.Delete
End Sub
----------------------------------------------------
VBA to Find Value in a Range
Sub Find_Range()
MsgBox Range("B2:D10").Find(What:="ab").Address
End Sub
VBA to Find Value in a Range –
Sub Find_Range_After()
MsgBox Range("B2:D10").Find(What:="ab", After:=Range("B3")).Address
End Sub
VBA to Find Value in a Range – MatchCase
Sub Find_Range_CaseSensitive()
MsgBox Range("B2:D10").Find(What:="ab", After:=Range("B3")).Address, MatchCase:=True
End Sub
------------------------------------------------------------------------------------------
VBA Insert Range in a Worksheet – xlDown
Range("C7").Insert Shift:=xlDown
VBA Insert Range in a Worksheet – xlToRight
Range("C7").Insert Shift:=xlToRight
VBA Insert Range in a Worksheet – EntireRow
Range("B2:D10").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
VBA Insert Range in a Worksheet – EntireColumn
Range("B2:D10").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
--------------------------------------------------------------------------------------
VBA Merge Range –
Range("A1:D10").Merge
VBA Merge Range – Example2
Range("A1:D10").Merge(True)
VBA Merge Rows-
Range("5:10").Merge
VBA Merge Column –
Range("B:E").Merge
---------------------------------------------------------------------------------------
VBA PasteSpecial method of Range –
Sub Range_PasteSpecial_Values()
Range("C6:D11").Copy
Range("G6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
----------------------------------------------------------------------------------------
Paste Special properties
xlPasteValues
xlPasteComments
xlPasteFormulas
xlPasteFormats
xlPasteAll
xlPasteValidation
xlPasteAllExceptBorders
xlPasteColumnWidths
xlPasteFormulasAndNumberFormats
xlPasteValuesAndNumberFormats
xlPasteAllUsingSourceTheme
xlPasteAllMergingConditionalFormats
xlPasteSpecialOperationAdd
xlPasteSpecialOperationSubtract
xlPasteSpecialOperationMultiply
xlPasteSpecialOperationDivide
xlPasteSpecialOperationNone
|
Code |
|
How
to Range value assign. |
|
Range("A1").Value = "Hello" |
|
How
to Range value assign . With using
application , workbooks and worksheets object |
|
Application.Workbooks("create-a-macro").Worksheets(1).Range("A1").Value
= "Hello" |
|
Using the worksheet name. |
|
Worksheets("Sales").Range("A1").Value
= "Hello" |
|
Using the index number (1 is the first worksheet starting from
the left). |
|
Worksheets(1).Range("A1").Value =
"Hello" |
|
3. Using the CodeName. |
|
Sheet1.Range("A1").Value = "Hello" |
|
Value Put in range |
|
Range("B3").Value = 2 |
|
Value Put in Multiple range |
|
Range("A1:A4").Value = 5 |
|
Below Range Assign and Interior Color Green |
|
Dim Rng As Range |
|
Set Rng = Selection |
|
Selection.Interior.Color = vbGreen |
|
Value Put in Multiple range |
|
Range("A1:A2,B3:C4").Value = 10 |
|
Range Value copy |
|
Range("C3:C4").Value =
Range("A1:A2").Value |
|
Range("A1").ClearContents |
|
Range("A1").Value = "" |
|
Range CurrentRegion |
|
Range("A1").CurrentRegion.Select |
|
Resize |
|
Range("A1:C4").Resize(3, 2).Select |
|
value put B3 Cell |
|
Cells(3, 2).Value = 2 |
|
value put A1 AND D1 Cell |
|
Range(Cells(1, 1), Cells(4, 1)).Value = 5 |
Excel VBA Lesson 3 Worksheet | Excel VBA Worksheet Object
VBA Worksheet Method:-
Activate
Copy
Delete
Move
Protect
Select
UnProtect
---------------------------------------------------------------------------------
VBA Activate Worksheet
Sub Activate_Sheet()
Worksheets("Project1").Activate
'Or
Sheets("Project1").Activate
End Sub
---------------------------------------------------------------------------------
Example-2
Sub Activate_Sheet_BasedOnIndex()
Worksheets(1).Activate
'Or
Sheets(1).Activate
End Sub
----------------------------------------------------------------------------
VBA Copy Worksheet Method –Before: Example 1
Sub CopySheet_Beginning()
Worksheets("Sheet3").Copy Before:=Worksheets(1)
End Sub
---------------------------------------------------------------------------------
Sub CopySheet_Beginning1()
ActiveSheet.Copy Before:=Worksheets(1)
End Sub
----------------------------------------------------------------------------------
Sub CopySheet_End()
Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count)
End Sub
----------------------------------------------------------------------------------------
Sub CopySheet_End()
ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
End Sub
-----------------------------------------------------------------------------------
VBA Delete Worksheet Method: Example
Sub Delete_Sheet()
Sheets("Sheet2").Delete
‘Or
Sheet2.Delete
End Sub
--------------------------------------------------------------------------------------
Sub Delete_ActiveSheet()
ActiveSheet.Delete
End Sub
--------------------------------------------------------------------------------------
Sub Delete_Sheet_WithoutWarningMessage()
Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Application.DisplayAlerts = True
End Sub
-----------------------------------------------------------------------------------
VBA Move Worksheet Method: Before
Sub MoveSheet_Beginning()
Worksheets("Sheet3").Move Before:=Worksheets(1)
End Sub
------------------------------------------------------------------------------------
Sub MoveSheet_Beginning1()
ActiveSheet.Move Before:=Worksheets(1)
End Sub
--------------------------------------------------------------------------------
Sub MoveSheet_End()
Worksheets("Sheet3").Move After:=Worksheets(Worksheets.Count)
End Sub
------------------------------------------------------------------------
Sub MoveSheet_End()
ActiveSheet.Move After:=Worksheets(Worksheets.Count)
End Sub
----------------------------------------------------------------------
VBA Protect Worksheet Method – Without Password
Sub Protect_WorkSheet_Without_Password()
Sheets("Sheet1").Protect
End Sub
-----------------------------------------------------------------------
VBA Protect Worksheet Method – With Password
Sub Protect_WorkSheet_With_Password()
Sheets("Sheet1").Protect "YourPassword", True, True
End Sub
-----------------------------------------------------------------------
VBA Select Worksheet Method
Sub Select_Range()
Worksheets("Project1").Activate
Range("A1").Select
End Sub
----------------------------------------------------------------------
VBA Select Worksheet Method: Example 2
Sub Select_Range()
Worksheets(1).Activate
Range("A1").Select
End Sub
------------------------------------------------------------------------
VBA UnProtect Worksheet Method –
Sub Unprotect_WorkSheet_Without_Password()
Sheets("Sheet1").Unprotect
End Sub
------------------------------------------------------------------------
Sub Unprotect_WorkSheet_With_Password()
Sheets("Sheet1").Unprotect "YourPassword"
End Sub
Excel VBA Lesson 2- Workbook | Excel VBA Workbook Object
Excel VBA Workbook Object Methods
Activate
Close
Protect
ProtectSharing
RefreshAll
RejectAllChanges
RemoveUser
Save
SaveAs
SaveCopyAs
UnProtect
UnProtectSharing
Method :- Active Example
Workbooks("Project1").Activate
Workbooks(2).Activate
Workbooks("Book3").Activate
Method :- Close Example
ActiveWorkbook.Close
Example 2:-
Workbooks (“ D:\Sample.xlsx").Close Savechanges:=False
Method :- Protect Example
ActiveWorkbook.Protect Password:="YourPassword", Structure:=True, Windows:=True
Method :- Protect Share Example
ActiveWorkbook.protectSharing Password:=”YourPassword”, SharingPassword:=”SharedPassword”
Method :- Refresh All Example
Workbooks(“Your Workbook Name”).RefreshAll
VBA RefreshAll Workbook Method-2
ActiveWorkbook.RefreshAll
VBA RefreshAll Workbook Method-3
Workbooks("Sample.xls").RefreshAll
It will refreshes the second workbook.
Workbooks(2).RefreshAll
-----------------------------------------------------------------------------
VBA RemoveUser Workbook:Example 1
Sub Remove_User1()
Dim UsrList()
UsrList = ThisWorkbook.UserStatus
If UBound(UsrList) > 1 Then
ThisWorkbook.RemoveUser (2)
End If
End Sub
----------------------------------------------------------------------------------
VBA Save Workbook: Example 1
Active Workbook.Save
VBA Save Workbook: Example 2
Sub Save_Workbook()
Dim Wkb As Workbook
Set Wkb = Workbooks.Add
Wkb.Save
End Sub
----------------------------------------------------------
VBA SaveAS Workbook Example 1
Sub SaveAs_Workbook()
Dim Wkb As Workbook
Set Wkb = Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\Sample.xlsm"
End Sub
-----------------------------------------------------------------
VBA SaveCopyAS Workbook:Example 1
Sub Workbook_SaveCopyAs()
ThisWorkbook.SaveCopyAs ThisWorkbook.Name & "_Ver1"
End Sub
-----------------------------------------------------------------------
VBA Unprotect Workbook: Example 1
Sub Unprotect_Sheet()
ActiveWorkbook.Unprotect “YourPassword”, True, True
End Sub
------------------------------------------------------------------
VBA UnProtectSharing Workbook:Example 1
Sub Unprotect_Sheet()
ActiveWorkbook.UnprotectSharing “YourPassword”
End Sub
--------------------------------------------------------
VBA RemoveUser Workbook:Example 2
Sub Remove_User2()
Dim UsrList()
UsrList = ThisWorkbook.UserStatus
For i = 1 To UBound(UsrList)
ThisWorkbook.RemoveUser (i)
Next
End Sub
---------------------------------------------------------
Excel VBA Lesson 1 - Application Object | Excel VBA Application Object
Excel VBA Application Object:-
TO DISPLAY Scroll Bars
Application.DisplayScrollBars = True
TO HIDE Scroll Bars
Application.DisplayScrollBars = False
TO DISPLAY Status Bar
Application.DisplayStatusBar= True
TO HIDE Status Bar
Application.DisplayStatusBar= False
To remove last copied data from clipart
Application.CutCopyMode = False
To make excel window as invisible
Application.Visible = False
To make excel window as visible
Application.Visible = True
Excel VBA Lesson | Excel VBA Tutorial |Excel VBA Notebook
Excel VBA Lesson | Excel VBA Tutorial |Excel VBA Notebook
Lesson 1:-Application
Lesson 2:- Workbook
Lesson 3:- Worksheets
Lesson 4 :- Range
Lesson 5 :- Selection
