Deleting Range Names :-

To delete all the range names in your workbook, this macro will do the trick.




Sub DeleteNames()

Dim NameX As Name

For Each NameX In Names

ActiveWorkbook.Names(NameX.Name).Delete

Next NameX

End Sub

Emailing Workbook :-

To email your current workbook the following code.



Sub Email()

ActiveWorkbook.SendMail recipients:="julsn@yahoo.com"

End Sub

Collections & Methods

Collections:-
Some objects are collections of other objects. For example, a workbook is a collection of all the objects it contains (ex. sheets, cells, ranges, charts, VBA modules, etc.). A VBA statement that makes reference to the workbook names “Loan Calculator.xls” would appear like this:

Workbooks(“Loan Calculator.xls”)

Methods:-
A method is an action that can be performed on an object. Excel VBA objects are separated from their methods by a period. For example, if you wanted to save a particular file as part of a VBA program you could include the following sentence in the code:

Workbooks(“Loan Calculator.xls”).Save

Current Cell Content

Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this example

a message box is displayed. Replace this with a macro should you require another course of action.

Sub ContentChk()
If Application.IsText(ActiveCell) = True Then
MsgBox "Text" 'replace this line with your macro
Else
If ActiveCell = "" Then
MsgBox "Blank cell" 'replace this line with your macro
Else
End If
If ActiveCell.HasFormula Then
MsgBox "formula" 'replace this line with your macro
Else
End If
If IsDate(ActiveCell.Value) = True Then
MsgBox "date" 'replace this line with your macro
Else
End If
End If
End Sub

Close All Files

Sometimes you may want to close all files without saving. Doing it manually is a hassle with the question "Do you wanna save?"

Sub CloseAll()
Application.DisplayAlerts = False
myTotal = Workbooks.Count
For i = 1 To myTotal
ActiveWorkbook.Close
Next i
End Sub

Carriage Return

Sometimes you may want to put a line of text on the next row and not let it continue on the first row. See this example in a message box.

Sub TwoLines()
MsgBox "Line 1" & vbCrLf & "Line 2"
End Sub

Counting Rows & Columns & Sheets

When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information

can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro

will do the trick.

Sub Count()
myCount = Selection.Rows.Count 'Change Rows to Columns to count columns
MsgBox myCount
End Sub

The next macro counts the number of sheets instead. Refer to Protecting all sheets macro which uses this method.

Sub Count2()
myCount = Application.Sheets.Count
MsgBox myCount
End Sub