Excel VBA Code Snippet
Excel VBA Code Snippet
Author: Amit Shah Version: Version 1.0
Table of Contents
Code Snippet ................................................................................................................................................. 7 Code to Open Existing Workbook ................................................................................................................. 8
Code to Create Excel Object:- ................................................................................................................... 8 Code to Open Excel and Create Object for the same:- ............................................................................. 8 Code to open txt file in the new workbook:-............................................................................................ 8 Code to Suppressing Excel Warnings:-...................................................................................................... 9 Code to make excel visible:- ..................................................................................................................... 9 Code to check whether a Sheet in an open workbook exists:-................................................................. 9 Code to create object to access specific worksheet:-............................................................................... 9 Code to make worksheet unprotect during runtime:- ........................................................................... 10 Code to make worksheet protected during runtime:-............................................................................ 10 Code to Make Specified range / column / cell locked during runtime:-................................................. 10 Code to get Row Count of Specific worksheet:- ..................................................................................... 10 Code to get Column Count of Specific worksheet:- ................................................................................ 11 Code to select Single cell, Contiguous, Non Contiguous range:- ............................................................ 11 Code to get address of the specified cell:-.............................................................................................. 11 Code to get value of the specified cell:-.................................................................................................. 11 Code to get Formula of the Specified cell:-............................................................................................. 11 Code to use Offset Range Property:- ...................................................................................................... 12 Code to Removes duplicate values:-....................................................................................................... 12 Code to Creates a data series in the specified range:- ........................................................................... 12 Code to change font of the specified Range or Cell:- ............................................................................. 13 Code to change font Style of the specified Range or Cell:-..................................................................... 13 Code to change font Size of the specified Range or Cell:- ...................................................................... 13 Code to change font color of the specified Range or Cell:- .................................................................... 13 Code to fill Red color of the specified Range or Cell:- ............................................................................ 13 Code to change worksheets / tab color:-................................................................................................ 14 Code to move worksheets:- .................................................................................................................... 14 Code to change horizontal alignment of the specified Range or Cell:- .................................................. 14 Code to change vertical alignment of the specified Range or Cell:-....................................................... 14 Code to wrap text of the specified Range or Cell:- ................................................................................. 14 Code to assign column width for the specified column:- ....................................................................... 15 Code to make columns data autofit:- ..................................................................................................... 15 Code to add validation at the specified column during runtime:-.......................................................... 15 Code to delete validation at the specified column during runtime........................................................ 16
Code to Performs an autofill on the cells in the specified range:- ......................................................... 16 Code to Represents the collection of hyperlinks for a worksheet or range:-......................................... 16 Code to Clear Contents of the specified Range or Cell:-......................................................................... 16 Code to Merge selected range:- ............................................................................................................. 16 Code to UnMerge selected Cell:- ............................................................................................................ 17 Code to change the Formatcell:-............................................................................................................. 17 Code to change the Formatcell:-............................................................................................................. 17 Code to AutoFilter with Single Criteria for the column:- ........................................................................ 17 Code to AutoFilter using RegEx with Single Criteria for the column:- .................................................... 17 Code to AutoFilter Using Cell Color with Single Criterial for the Column:- ............................................ 17 Code to AutoFilter with Multiple Criteria for the column:- .................................................................... 18 Code to sort the values in the worksheet in the ascending & descending order:- ................................ 18 Code to do AdvancedFilter on the specified range:- .............................................................................. 18 Code to let you modify any sequence using characters Property:-........................................................ 19 Code to get address of the range from Filtered Cells:-........................................................................... 19 Code to insert Column during runtime:-................................................................................................. 19 Code to Delete Column during runtime:- ............................................................................................... 19 Code to insert Row during runtime:- ...................................................................................................... 19 Code to Delete row during runtime:- ..................................................................................................... 19 Code to Add Conditional formatting to a specified cell or range:- ......................................................... 20 Code to Delete Conditional formatting to a specified cell or range:-..................................................... 20 Code to Add Comment to a specified cell or range:-.............................................................................. 20 Code to Delete Comment to a specified cell or range:-.......................................................................... 21 Code to copy the specified range during runtime:-................................................................................ 21 Code to Paste the specified range during runtime:-............................................................................... 21 Code to PasteSpecial the specified range during runtime:- ................................................................... 21 Code to delete worksheets:-................................................................................................................... 21 Code to Save the workbook:-.................................................................................................................. 22 Code to close the workbook:- ................................................................................................................. 22 Code to Quit the workbook:- .................................................................................................................. 22 Formula to get count of total working days in the month:- ................................................................... 22 MkDir Statement:- .................................................................................................................................. 22 RmDir Statement:- .................................................................................................................................. 22 FileCopy Statement:- .............................................................................................................................. 23 FileLen Function:-.................................................................................................................................... 23 FileDateTime Function:-.......................................................................................................................... 23
Kill Statement:- ....................................................................................................................................... 23 VLookup Function:- ................................................................................................................................. 24 HLookup Function:-................................................................................................................................. 24 PIVOT Table Function:- ........................................................................................................................... 25 CurrentRegion Property:-........................................................................................................................ 27 Code to Create New Workbook and SaveAs on the Local .......................................................................... 28 Code to add workbook during run-time using excel object:- ................................................................. 28 Code to add worksheet during run-time using excel object:- ................................................................ 28 Code to SaveAs Workbook during run-time using excel object:- ........................................................... 28 Code to delete worksheets:-................................................................................................................... 28 Code to Save ActiveWorkbooks:-............................................................................................................ 28 Code to Close the workbook:- ................................................................................................................ 29 Code to Quit the workbook:- .................................................................................................................. 29 Excel VBA Properties, Methods & Events ................................................................................................... 30 WORKBOOK:- .......................................................................................................................................... 30
Properties -.......................................................................................................................................... 30 Methods -............................................................................................................................................ 30 Events -................................................................................................................................................ 30 WORKSHEETS:-........................................................................................................................................ 30 Properties -.......................................................................................................................................... 30 Methods -............................................................................................................................................ 30 Events -................................................................................................................................................ 30 RANGE:-................................................................................................................................................... 31 Properties -.......................................................................................................................................... 31 Methods -............................................................................................................................................ 31 Excel VBA Programming STRING Functions ................................................................................................ 32 ASC:- ........................................................................................................................................................ 32 CHR:- ....................................................................................................................................................... 32 Instr:-....................................................................................................................................................... 33 InstrB:-..................................................................................................................................................... 33 InstrRev:-................................................................................................................................................. 33 Len:- ........................................................................................................................................................ 34 Left:- ........................................................................................................................................................ 34 Right:-...................................................................................................................................................... 34 Trim, LTrim & RTrim:-.............................................................................................................................. 34 Lcase:- ..................................................................................................................................................... 35
Ucase:- .................................................................................................................................................... 35 Space:-..................................................................................................................................................... 35 Mid:-........................................................................................................................................................ 35 Replace:- ................................................................................................................................................. 35 StrReverse: .............................................................................................................................................. 36 StrComp:- ................................................................................................................................................ 36 String:-..................................................................................................................................................... 36 Split:- ....................................................................................................................................................... 36 LBound:- .................................................................................................................................................. 37 UBound:- ................................................................................................................................................. 37 Date:- ...................................................................................................................................................... 37 DateAdd:- ................................................................................................................................................ 37 DateDiff:-................................................................................................................................................. 38 DateSerial:-.............................................................................................................................................. 38 Msgbox:- ................................................................................................................................................. 38 InputBox:-................................................................................................................................................ 38 Join:-........................................................................................................................................................ 39 Filter:-...................................................................................................................................................... 39 Excel VBA Programming INTEGER Functions .............................................................................................. 40 SQR:- ....................................................................................................................................................... 40 EXP:- ........................................................................................................................................................ 40 LOG:- ....................................................................................................................................................... 40 RND:- ....................................................................................................................................................... 41 ABS:- ........................................................................................................................................................ 41 SGN:- ....................................................................................................................................................... 41 INT & FIX:- ............................................................................................................................................... 42 HEX:-........................................................................................................................................................ 42 Excel VBA Programming TYPE CHECK Functions......................................................................................... 43 isArray:- ................................................................................................................................................... 43 isDate:- .................................................................................................................................................... 43 isEmpty:- ................................................................................................................................................. 43 isNull:- ..................................................................................................................................................... 44 isNumeric:-.............................................................................................................................................. 44 Excel VBA Conditional Statements.............................................................................................................. 45 If...Then...Else Statements:- .................................................................................................................... 45 Select Case Statements:- ........................................................................................................................ 46
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- color rank count name controlling it all in proc report
- how to apply conditional formatting to a cell
- excel vba code snippet
- excellence with excel quiz questions
- how to achieve highlight and font color change in a cell
- 224 2008 proc report in color what s your style
- how you use excel in banking amazon web services
- excel formatting best practices in financial models important
Related searches
- how to write vba code in excel
- excel vba userform listbox multiselect
- free excel vba code samples
- excel vba commands
- excel vba code library
- adding vba code to excel
- excel vba quick reference pdf
- excel vba listbox multiple selection
- vba code library free
- vba code examples for access
- excel vba character code
- vba code syntax