Creating a Workbook

A workbook is an object of type Workbook and it is part of the Workbooks collection. To support the ability to create a new

workbook, the Workbooks collection is equipped with a method named Add. Its syntax is:

Workbooks.Add(Template) As WorkbookYou start with the Workbooks class, a period, and the Add method. This method takes only

one argument but the argument is optional. This means that you can call the method without an argument and without

parentheses. Here is an example:

Private Sub cmdNewWorkbook_Click()
Workbooks.Add
End Sub

When the method is called like this, a new workbook would be created and presented to you. After creating a workbook, you may

want to change some of its characteristics. To prepare for this, notice that the Add() method returns a Workbook object.
Therefore, when creating a workbook, get a reference to it. To do this, assign the called method to a Workbook variable. Here

is an example:

Private Sub cmdNewWorkbook_Click()
Dim SchoolRecords As Workbook

Set SchoolRecords = Workbooks.Add
End Sub

After doing this, you can then use the new variable to change the properties of the workbook.

Workbooks Fundamentals

In the VBA language, a workbook is an object that belongs to a collection called Workbooks. Each workbook of the Workbooks

collection is an object of type Workbook, which is a class.

Each workbook of the Workbooks collection can be identified using the Item property. To programmatically refer to a workbook,

access the Item property and pass either the index or the file name of the workbook to it.

After referring to a workbook, if you want to perform an action on it, you must get a reference to it. To do this, declare a

Workbook variable and assign the calling Item() to it. This would be done as follows:

Private Sub cmdSelectWorkbook_Click()
Dim SchoolRecords As Workbook

Set SchoolRecords = Workbooks.Item(2)
End Sub

Sum Unique/Distinct Values in Excel

Sum Unique/Distinct Values in Excel:-

Public Function DISTINCTSUM(Rg As range)

Dim rCell As range

Dim cCells As New Collection

Dim vValue As Variant



' create a unique no duplicate value collection

For Each rCell In Rg

On Error Resume Next

cCells.Add rCell.Value, CStr(rCell.Value)

Next rCell


' sum all the data in previous collection

For Each vValue In cCells

DISTINCTSUM = vValue + DISTINCTSUM

Next vValue



Set cCells = Nothing

End Function

Remove Non-AlphaNumeric Characters from String

Remove Non-AlphaNumeric Characters from String:-


Function GETALPHANUMERIC(text)

str_all = "abcdefghijklmnopqrstuvwxyz1234567890"

For lenstr = 1 To Len(text)

If InStr(str_all, LCase(Mid(text, lenstr, 1))) Then

GETALPHANUMERIC = GETALPHANUMERIC & Mid(text, lenstr, 1)

End If

Next

End Function

How to Get a Full File Path in Excel?

Function FULLFILENAME()
FULLFILENAME = Application.ActiveWorkbook.FullName
End Function

Empty Clipboard Excel Using Windows Empty Clipboard function

Empty Clipboard Excel Using Windows Empty Clipboard function:-

Public Declare Function OpenClipboard Lib "user32" (ByVal NewOwner As Long) As Boolean

Public Declare Function EmptyClipboard Lib "user32" () As Boolean

Public Declare Function CloseClipboard Lib "user32" () As Boolean


Sub ClearClipboard()

' call ClearClipboard() from your other VBA macro to clear/empty Windows Clipboard

If OpenClipboard(0) Then

EmptyClipboard

CloseClipboard

End If

End Sub

Empty Clipboard Excel Using Excel Application properties

Empty Clipboard Excel Using Excel Application properties:-

Application.CopyObjectsWithCells = False

Get Position of Last Column Containing Data with Excel VBA:-

Function LastColumn() As Long

Dim ix As Long

ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

LastColumn = ix

End Function