@TutorialHorizon
Excel-WorkBook
Using VBA (Visual Basic for Applications) , you can create Excel workbook at the runtime by using Add() method.
If you won’t provide any name for the workbook it will be created as BookN, where N will increase automatically, starting from 0, as you call WorkBook.Add() function.
Function FnCreateWorkBook()
Dim objWorkBook As Workbook
Set objWorkBook = Workbooks.Add
objWorkBook.SaveAs Filename:="NewWorkBook.xls"
End Function
CurrentRegion analyses the filled cells and the blank cells surrounded by the rangeyou provide. Its returns a Range object as a region in Mxn shape.
Syntax:
RangeObject. CurrentRegion
Example:
Function FnCurrentRegion() Dim mainWorkBook As Workbook Set mainWorkBook = ActiveWorkbook
ActiveCell, as the name clearly indicates that the cell which is presently active in your worksheet, in other words you can if u start typing the value u entered will go to active cell.
Example:
Function FnActiveCell()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
mainWorkBook.Sheets("Sheet1").Activate
ActiveCell.Value = 5
By using FillAcrossSheets you can copy the Range of one worksheet to other multiple worksheets.
Steps:
Select:
You can Select a cells or cells for performing many activities like putting some value in cell or doing formatting or copy-paste the data. You can put or enter values in cells with/without selecting the cells. Select is used mostly in the cases of copy-paste operation where you have to tell the compiler specifically that from which cell it has to copy the data and in which cell it needs to be pasted.
Note:
Select works only with actives worksheets, if you use Select on cells before activating the respective sheet, the select method will fail and you will get “Run-time error ‘1004’: Application defined or object defined error”
Referring Excel cells using For…Next -Loop
For i = 1 to 10 Range(“A” & i).Value = 1 Next
Enter ‘1’ in Cells from A1 to A10.
Referring Columns of excels using For-Loop
For i = 1 To 26 Range(Chr(65 + i) & "1").Value = 2
You can combine more than one Ranges into one Range using Union Method and later on instead of writing all … Read more
You don’t have to type Range every time, you have shortcut for that, ‘Brackets’.
Shortcut for referring one cell.
Worksheets(“Sheet1”).[G5].Interior.ColorIndex = 45
Instead of using Range(“G5”), you can use [G5].
Shortcut for referring range of cells.
Worksheets(“Sheet1”).[E3:F5].Value = 4
Instead of using Range(“E3:F5”), you can use [E3:F5].
In the earlier tutorial (Range), you have seen how you use Range property. In this tutorial you will see the many different ways to use Range property to refer the excel sheet’s rows and columns.
Reference – Range (“A1”)
– Refer Cell A1
Reference – Range (“A1:D10”)
– Refer all the cells from A1 to D10 ( The entire MxN matrix)
Reference – Range(“A1:C3”,”D5:H7”)
– Refer all the cells from A1 to C3 and D5 to H7
In VBA-Excel, Copy and paste the data plays an important role, we can copy data from one place and paste it at some other place in the same way like how we do it manually, and when you copy some data its goes to the Clipboard from there you can paste it at some other location.
Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook
Copy the Entire data from one sheet to another
For example if you want to copy the data “Sheet1” and paste it to in “Sheet2”.
For copy the data present in “Sheet1”
mainworkBook.Sheets(“Sheet1”).UsedRange.Copy
In VBA-Excel, Copy and paste the data plays an important role, we can copy data from one place and paste it at some other place in the same way like how we do it manually, and when you copy some data its goes to the Clipboard from there you can paste it at some other location.
Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook
Copy the Entire row data and paste it to another row.
For example if you want to copy the data from row 5 of “Sheet1” and paste it to in “Sheet2”.
For copy the data present in row 5 from “Sheet1”
mainworkBook.Sheets(“Sheet1”).Rows(5).EntireRow.Copy
In VBA-Excel, Copy and paste the data plays an important role, we can copy data from one place and paste it at some other place in the same way like how we do it manually, when you copy some data its goes to the Clipboard from there you can paste it at some other location.
Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook
Copy a data from a call and paste it to another cell
For example if you want to copy the data from cell “C6” of “Sheet1” and paste it to “B2” in “Sheet2”.
First copy the data present in cell “C6” from “Sheet1”
mainworkBook.Sheets(“Sheet1”).Range(“C6”).Copy
VBA-Excel: Clear data from Excel Sheet(WorkSheet)
Format: WorkBook.WorkSheet.Range.Clear
Examples:
mainworkBook.Sheets(3).UsedRange.Clear
Clear data from particular cell:
Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook
mainworkBook.Sheets(“MyFirstMacro”).Range(“A2”).Clear
In VBA-Excel, UsedRange is very effective property when it comes to deal with the excel file which contains data. Right from formatting options of excel sheet to copy pasting the data, getting informations about rows or columns or clearing the data from excel.
As the name clearly states that it talks about all the cells in an Excel which are filled. ( All the Range which is used)
Format:
WorkBook.WorkSheet.UsedRange
Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook
Get the number of Used rows
Sub FnGetSheetsName() Dim mainworkBook As Workbook Set mainworkBook = ActiveWorkbook For i = 1 To mainworkBook.Sheets.count ‘Either we can put all names in an array , here we are printing all the names in Sheet 2 mainworkBook.Sheets(“Sheet2”).Range(“A” & i) = mainworkBook.Sheets(i).Name Next i
Sometimes deleting the blank rows from you Excel sheet is a tedious task to do especially when your sheet contains lots of data, say 10k-15k rows and having some blank rows in between and you need to delete these rows. Just imagine to delete these blank rows manually, but VBA Codes are life saver here.
Sub FnDeleteBlankRows()
Dim mwb As Workbook
Set mwb = ActiveWorkbook
For x = mwb.Sheets("1").Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(mwb.Sheets("1").Rows(x)) = 0 Then
mwb.Sheets("1").Rows(x).Delete
End If
Next
Offset:
Format:
Range(“StartingPoint”).Offset(NoOfRowsToBeMoved , NoOfColumnsToBeMoved)
Example :
Range(“A2”).Offset(1,0).Select
And compiler reads, move one cell down from A2 , which is A3.
Range(“A2”).Offset(-1,0).Select
Format: Range (“ColumnNameRowNumber”)
Ex: In VBA-Excel when you say Range(“A5”) it’s very easy to understand that you are talking about 5th row in 1st column (“A”)
As the name states Range, you can select a range is cells using this function
Ex: Range (“A1:B5”).Copy means copy all the cells between A1 to B5
To know more about Copy/Paste in VBA-Excel click here (link to VBA-Excel Copy Paste)
OR
Range (“A1, A5, A9, C1:C8”).Select
In VBA Excel Macro, Message Box (Msgbox) is one of the important features. Right from warning messages to calculated values in your code or anything. Whenever the code has something to communicate to the user, it can be done through Message box.
Sub FnPainText
Msgbox “Just A Plain Text”
End Sub
FileSystemObject:OpenTextFile Method
Description :
This function opens a file and returns a TextStream object and later you can use that TextStream object to perform various activities on that file like reading or writing
Format :
objectOfFileSystemObject. OpenTextFile(filename[, iomode[, create[, format]]])
Arguments: