Duplicates (Highlight duplicates in Bold Red)

There are times you need to highlight duplicate data in your worksheet. This macro does the trick.

Sub DupsRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

Deleting Empty Rows

To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.

Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub

The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".

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

Current Date

It's a good idea to insert the current date when you save the file so that you can tell if it's the latest version. Of course this is shown under file properties but how many people know where to find it? You could also put the current date in the footer of your print out. It is ideal if the date does not change unless the file is saved. You can use this code. (On the drop down list that says declaration, select before save and you will see the 1st line of code shown below - more details refer to Auto Run macro)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1") = Now 'Select any cell you want
End Sub

Counter

To use a counter in your macro, just assign any cell to retain the value. In this example the cell A1 is chosen. Each time the macro is run, it adds the value 1 to the cell A1.

Sub Count()
mycount = Range("a1") + 1
Range("a1") = mycount
End Sub

Copying A Range

Copy data from a specific range can be done with this macro. Here data is copied from the current sheet to the activecell. (Refer to Active Cell)

Sub CopyRange()
Range("A1:A3").Copy Destination:=ActiveCell
End Sub

To copy from a range in another sheet (eg Sheet3) to the active cell you need to change the code to;

Sheets("sheet3").Range("A1:A3").Copy Destination:=ActiveCell

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 myCount()
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 myCount2()
mycount = Application.Sheets.Count
MsgBox (mycount)
End Sub

Active Cell

An active cell is the current cell that is selected. This term is used in many macros. This can be used as a marker. A good example is when you need to move from your current cell. Refer to Moving your cursor macro.

Sheets("sheet3").Range("A1:A3").Copy Destination:=ActiveCell

SendMail Method in VBA

The SendMail Method is very easy to use and will send any specified Excel Workbook as an attachment to specified recipients.

As the code below specifies the Active Workbook the code is best stored in your Personal.xls

Sub SendActiveWorkbook()

ActiveWorkbook.SendMail _

Recipients:="chithu.palaniappan@gmail.com", _

Subject:="Try Me " & Format(Date, "dd/mmm/yy")

End Sub

If you only wish to send one Worksheet from a Workbook we can use the method shown below. It creates a new Workbook housing

ONLY the sheet we copy. It then sends the 1 sheet Workbook as an attachment, then closes the new Workbook without saving.

Send mail in JavaScript

function TriggerOutlook(to,project_name,from)
{
var body = "Access Required for eTracker project";
var subject = "Access Required for eTracker project :" + ;
window.location.href = "_mailto:?body="+body+"&subject="+subject;
}

Send mail in VBScript

Sub SendMailOutlook(aTo, Subject, TextBody, aFrom)
Dim Outlook 'As New Outlook.Application
Set Outlook = CreateObject("Outlook.Application")
Dim Message 'As Outlook.MailItem
Set Message = Outlook.CreateItem(olMailItem)
With Message
.Subject = Subject
.HTMLBody = TextBody
.Recipients.Add (aTo)
Const olOriginator = 0
.Send
End With
End Sub

Auto open function

Auto open function to automatically executing the macro function.

Private Sub Auto_Open()
MsgBox "This is auto open macro in Module"
End Sub

Open workbook trigger events

Private Sub Workbook_Open()
MsgBox "This is auto open macro in This workbook"
End Sub

Viewing Many Workbooks

To programmatically refer to a workbook, access the Item property and pass either the index or the file name of the workbook

to it. Here is an example:

Private Sub cmdSelectWorkbook_Click()
Workbooks.Item (2)
End Sub


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

Microsoft Excel as an MDI

To support the ability the programmatically access a workbook, the Workbook class is equipped with a method named Activate.

Its syntax is:

Workbook.Activate()This method takes no argument. Therefore, to call it, you can get a reference to the workbook you want to

access, then call the Activate() method. Here is an example:

Private Sub cmdSelectWorkbook_Click()
Dim SchoolRecords As Workbook

Set SchoolRecords = Workbooks.Item(2)
SchoolRecords.Activate
End SubYou can also do this with less code by applying the index directly to the Workbooks collection. Here is an example:

Private Sub cmdSelectWorkbook_Click()
Workbooks(2).Activate
End Sub

Opening Workbooks

The ability to programmatically open a workbook is handled by the Workbooks collection. To support this, the Workbooks class

is equipped with a method named Open. Its syntax is:

Workbooks.Open(FileName,
UpdateLinks,
ReadOnly,
Format,
Password,
WriteResPassword,
IgnoreReadOnlyRecommended,
Origin,
Delimiter,
Editable,
Notify,
Converter,
AddToMru,
Local,
CorruptLoad)

FileName is the only required argument. When calling this method, you must provide the name of the file or its path.

Saving a Workbook

To visually save a workbook, you can click the Office Button and click Save. You can also press Ctrl + S. If the document was

saved already, it would be saved behind the scenes without your doing anything else.

To support the ability to programmatically save a workbook, the Workbook class is equipped with a method named Save. Its

syntax is:

Workbook.Save()

As you can see, this method takes no argument. If you click the Office Button and click Save or if you call the

Workbook.Save() method on a work that was not saved yet, you would be prompted to provide a name to the workbook.

To save a workbook to a different location, you can click the Office Button, position the mouse on Save As and select from

the presented options. You can also press F12. To assist you with programmatically saving a workbook, the Workbook class is

equipped with a method named SaveAs. Its syntax is:

Workbook.SaveAs(FileName,
FileFormat,
Password,
WriteResPassword,
ReadOnlyRecommended,
CreateBackup,
AccessMode,
ConflictResolution,
AddToMru,
TextCodepage,
TextVisualLayout,
Local)

The first argument is the only required one. It holds the name or path to the file. Therefore, you can provide only a name of

the file with extension when you call it. Here is an example:

Private Sub cmdNewWorkbook_Click()
Dim SchoolRecords As Workbook

Set SchoolRecords = Workbooks.Add

SchoolRecords.SaveAs "SchoolRecords.xlsx"
End Sub

If you provide only the name of a file when calling this method, the new workbook would be saved in the current directory or

in My Documents (Documents in Windows Vista). If you want, an alternative is to provide a complete path to the file.

Saving Workbooks

After working on a new workbook, you can save it. After programmatically creating a workbook, if you want to keep it when the

user closes Microsoft Excel or when the computer shuts down, you must save it. You and the user have the option of using the

Save As dialog box.

The Default File Location:-

To support the ability to programmatically change the default folder, the Application class is equipped with a property named

DefaultFilePath. Therefore, to programmatically specify the default folder, assign its string to the

Application.DefaultFilePath property. Here is an example:

Private Sub Exercise()

Application.DefaultFilePath = "C:\chidambaram\document and settings\my documents\"

End Sub

When this code has executed, the Default File Location of the Excel Options dialog box would be changed.

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

Excel Spin Buttons

Spin Button

In the toolbox the spin button has this icon .

You can ask a user to enter a value directly in a text box but you can make things a little more attaractive by using a text box and a spin button.

The spin button is not really used by itself. Because the spin button does not show its value it is usually used with a text box. The text box shows a number and by clicking on the arrows of the spin button the value in the text box is increased (or decreased) by 1, or 5 or 10...by whatever value that is set within the properties of the spin button.

Properties

Among the other properties of the spin buttons are:

- Min is the minimum value of the spin button. It can be negative
- Max is the maximum value of the spin button. It can be negative
- Small is the value of the change when the user clicks on the arrows
- Large is the value of the change when the user clicks on the scroll bar of the spin button.

Command Buttons in VBA for Excel

In the toolbox the command button has this icon . The command button is a very active control and there is always VBA code behind it.

The command buttons are usually placed at the bottom of the form and serve to complete the transaction for which the form has been created. The caption of these buttons are usually "Go" , "Run" , "Submit" , "Cancel" , etc.

Properties

Among the other properties of the command button are:

- WordWrap to be able to write more that one line on a button,
- ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button,

Adding a Command Button to a Userform

To add a command button to a userform you left click on its icon in the toolbox. You move the cursor to the userform, you click again and the command button appears. You can then resize it to your liking. If you double click on the command button icon in the toolbox you can then click on the form as many times as you need command buttons. When you are finished adding command buttons just click once on the command button icon of the toolbox.

VBA Code

Most of the VBA code (VBA sentences) is created within the command button when you develop simple userforms. Here are two exercises creating VBA code within the command button.

VBA for Excel for Worksheets

To develop a VBA procedure that is triggered by an event relating to the worksheet (when you select it, when you leave it...) see the VBA lesson on events.

Sheets

You access a worksheet named " Balance" with:
Sheets("Balance").Select
Note that the word "Sheets" is plural and always use the quotes within the parenthesis

You cannot select a sheet that is hidden so you will need to write:
Sheets("Balance").Visible= True
Sheets("Balance").Select
and then if you want to hide the sheet again:
Sheets("Balance").Visible= False

The name of a sheet must not have more than 31 characters and should not include certain special characters like " ? : \ / [ ]" . If you do not respect these rules your procedure will crash.

The following lines of code will generate an error message:
Sheets("Sheet1").Name= "Balance and Introduction to Numbers" because there are more than 31 characters including the spaces
Sheets("Sheet1").Name= " Balance: Introduction" because of the special character :
Sheets("Sheet1" ).Name= " " because the name cannot be blank

You can not go directly from a sheet to a cell on another sheet. For example if the active sheet is "Balance" and you want tot go to cell A1 of a sheet named " Results" you cannot write:
Sheets("Results").Range("A1").Select
You must take two steps:
Sheets("Results").Select
Range("A1").Select

Cells, Ranges, Columns and Rows in VBA for Excel

A lot of VBA beginners start their career using Cells. For example:
Cells(1,1).Select is the same thing as Range("A1").Select and
Cells(11,31).Select is the same as Range("AE11").Select.

We strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes your sentences much clearer and you are not forced to remember that column AE is column 31.

The only time that you will use Cells is when you want to select all the cells of a worksheet. For example:
Cells.Select
To select all cells and then to empty all cells of values or formulas you will use:
Cells.ClearContents

Range
To select a single cell you will write:
Range("A1").Select

To select a set of contiguous cells you will use the colon and write:
Range("A1:G5").Select

To select a set of non contiguous cells you will use the comma and write:
Range("A1,A5,B4").Select

To select a set of non contiguous cells and a range you will use both the colon and the comma:
Range("A1,A5,B4:B8").Select

Offset
The Offset property is the one that you will use the most with Range to move around the sheet.

To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select
To move one cell to the right (from B2 to C2): Range("B2").Offset(0,1).Select
To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select

To move one cell down from the selected cell:
ActiveCell.Offset(1,0).Select

As you notice the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need:
Range("A1").Offset(5,6).Select

You will use very often the following piece of code . It selects a cell and 4 more to the right to be copied/pasted somewhere else:
Range(ActiveCell,ActiveCell.Offset(0,4)).Copy
Notice the comma after the first ActiveCell and the double closing parentheses before the Copy.

Message and Input Boxes (MsgBox, InputBox) in Excel

In VBA for Excel the message box (MsgBox) is the primary tool to interact with the user. For example you might want to tell the user that a long macro has finished running.

Step 1: Open a new workbook and use the ALT/F11 keys to move to the Visual Basic Editor.

Step 2: Copy/Paste the following macro from here into the code window of any sheet.

Sub InputBoxTest()
Sheets("Sheet1").Select
Range("A1").Value = 695
MsgBox "The macro has finished running!"
End Sub

Notice the space following MsgBox and the use of quotation marks surrounding the text

Step 3: Use the ALT/F11 keys to go back to Excel and run the macro InputBoxTest.

The value 695 is entered in cell A1 and the following message box appears.

Step 4: Delete the macro in the Visual Basic Editor and the value 695 from cell A1

Unprotect and Protect Sheet using VBA code

Here is a sample to unprotect a sheet and write some values and then protect the sheet again

Sub Unprotect_And_ThenProtect()

ActiveSheet.Unprotect
Range("A2").Value = Now()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

AutomaticHyphenation feature

Word does automatic hyphenation at the end of line when the AutomaticHyphenation feature is turned on


ActiveDocument.AutoHyphenation = True

You can test it by try selecting the Hyphen (which is not there physically)

The following code converts all automatic hyphens to manual ones


ActiveDocument.ConvertAutoHyphens

How to Specify the Password in SaveAs option in PowerPoint VBA

Unlike SaveAs in Word/Excel, which takes the Password as part of the argument, Powerpoint SaveAs function doesn't specify it.

Here is a way to do it through VBA


Sub Save_Presentation_With_Password()

Dim oPS As PowerPoint.Presentation
Dim sTempPath As String

Set oPS = Presentations.Add
oPS.Slides.Add 1, ppLayoutTitle

' ----------------------------
' Coded by chithu for VBACT.Blogspot.com
' ----------------------------

sTempPath = Environ("Temp") & "\"

oPS.Password = "PWD2PPT"


oPS.SaveAs FileName:=sTempPath & "PPTSample1.ppt", FileFormat:=ppSaveAsDefault
oPS.Close


End Sub

Hide / Unhide Sheet Tab using Excel VBA

If you want to hide the Sheet Tab you can do that using Excel Options

1) Uncheck the Show sheet tabs checkbox from Advanced Tab of Options Menu.
2) Go to the advanced tab and you can enable/disable through the show sheet tabs options.


You can do the same through Excel VBA

ActiveWindow.DisplayWorkbookTabs = False

HOW TO EXTRACT ALL FORMULA'S IN EXCEL SHEET USING VBA

Highlight all cells containing Formulas using Excel VBA

The following snippet highlights all cells that contain formula


Sub HighLight_Formula_Cells()

Dim oWS As Worksheet
Dim oCell As Range

Set oWS = ActiveSheet

For Each oCell In oWS.Cells.SpecialCells(xlCellTypeFormulas)
oCell.Interior.ColorIndex = 36
MsgBox oCell.Formula
Next oCell


End Sub

Combining Text Files using VBA

Multiple utilities are available to split & merge text files. However, here is a simple one my friend uses to merge around 30 ascii files into one

It uses File System Object and you need to add a reference of Microsoft Scripting Runtime

Sub Append_Text_Files()

Dim oFS As FileSystemObject
Dim oFS1 As FileSystemObject

Dim oTS As TextStream
Dim oTS1 As TextStream

Dim vTemp

Set oFS = New FileSystemObject
Set oFS1 = New FileSystemObject


For i1 = 1 To 30

Set oTS = oFS.OpenTextFile("c:\Sheet" & i1 & ".txt", ForReading)
vTemp = oTS.ReadAll

Set oTS1 = oFS.OpenTextFile("c:\CombinedTemp.txt", ForAppending, True)
oTS1.Write (vTemp)

Next i1

End Sub

The code is simple.. it searches for files from Sheet1.txt ...Sheet30.txt and copies the content into one variable. Then it appends the content to CombinedTemp.txt

Query Table with Excel as Data Source

Sub Excel_QueryTable()

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
Destination:=Range("B1"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

Query Table for Text / CSV Files

If you update some Excel frequently, you can keep it as shared and then ask your fellow colleagues to check if often (refresh)

One of the good option is to have them as CSV file and use query table to update it regularly

Sub TXT_QueryTable()

Dim ConnString As String

Dim qt As QueryTable

ConnString = "TEXT;C:\Temp.txt"


Set qt = Worksheets(1).QueryTables.Add(Connection:=ConnString, _
Destination:=Range("B1"))

qt.Refresh

End Sub

The Refresh method causes Microsoft Excel to connect to the query table’s data source, execute the SQL query, and return data to the query table destination range. Until this method is called, the query table doesn’t communicate with the data source.

SQL Server 2005 - Remote Connection

If you have downloaded the express edition, you may not connect to the server from a remote machine.

In the SQL Server Configuration Manager (SSCM) enable the Remote Connections Option

Under the SQL Native Client Configuration enable TCP/IP protocol.

ADO connection string for Excel

If are a diehard ADO user. Here is the connection string for Excel

sXL = "c:\CTWebEg.xls"
Set Cn = New ADODB.ConnectionCn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sXL & ";Extended Properties=Excel 8.0;Persist Security Info=False"Cn.ConnectionTimeout = 40

Cn.Open

The rest is the usual ADO recordset retrieving technique

Open and create a E-mail?

Sub Open_Create_E_Mail()

Dim wbBook As Workbook

Set wbBook = ThisWorkbook

wbBook.FollowHyperlink "mailto:"

End Sub

Activate IE Browser?

Sub Activate_IE()

Dim oIE As Object

Set oIE = CreateObject("InternetExplorer.Application")

With oIE

.Visible = True

.Navigate "http://www.xldennis.com"

End With

Set oIE = Nothing

End Sub

Open_Access

Sub Open_Access()

Dim oAccess As Object

Set oAccess = CreateObject("Access.Application")

With oAccess

.Visible = True

.UserControl = True

End With

End Sub

MS Outlook

Sub Open_OutLook()

Dim oOutlook As Object

Dim oNameSpace As Object

Dim oInbox As Object



Set oOutlook = CreateObject("Outlook.Application")

Set oNameSpace = oOutlook.GetNamespace("MAPI")

Set oInbox = oNameSpace.Folders(1)

Set oInbox = oInbox.Folders("Inbox")

oInbox.Display

End Sub

How to find the last row or column or cell on a worksheet?

How to find the last row with data on a worksheet:

On Error Resume NextMsgBox ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row
How to find the last column with data on a worksheet:

On Error Resume NextMsgBox ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column
How to find the last cell with data on a worksheet:

On Error Resume NextdxLastCol= ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column
dxLastRow= ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row
dxLastCell=cells(ldxLastRow,dxLastCol).address
', '

How to open and edit Linked Excel files from Word using VBA:-

One can insert an object in word by either linking or embedding. We have already seen How to Read and Edit Embedded objects using VBA, The following code will throw light on accessing a linked object from Word (Excel sheet) and editing the same.

Sub Edit_Linked_Excel_Objects()
Dim oXL As Excel.Application ' Excel App Object

Dim oWB As Excel.Workbook ' Workbook Object

Dim sWB As String ' Linked String

Dim oIShape As InlineShape ' Inline Shape Object

On Error GoTo Err_Report
Set oXL = New Excel.Application

For Each oIShape In ActiveDocument.InlineShapes

If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then

' Check if the Object is Linked

If oIShape.Type = wdInlineShapeLinkedOLEObject Then

' Get the Source Name of Linked Workbook

sWB = oIShape.LinkFormat.SourceFullName

If Len(Dir(sWB)) <> 0 Then

Set oWB = oXL.Workbooks.Open(sWB, , False)

oWB.Sheets(1).Range("A1").Value = "ID"

oWB.Save

oWB.Close False

oIShape.LinkFormat.Update

Else

MsgBox "Linked file not found"

End If

End If

End If

Next oIShape

Finally:

oXL.Quit

If Not oXL Is Nothing Then Set oXL = Nothing

If Not oWB Is Nothing Then Set oWB = Nothing

If Not oIShape Is Nothing Then Set oIShape = Nothing

Exit Sub

Err_Report:

MsgBox Err.Description & " - " & Err.Number

Err.Clear

GoTo Finally

End Sub

How to Extract All Formula's in Excel Sheet using VBA:-

The following snippet highlights all cells that contain formula


Sub HighLight_Formula_Cells()

Dim oWS As Worksheet
Dim oCell As Range

Set oWS = ActiveSheet

For Each oCell In oWS.Cells.SpecialCells(xlCellTypeFormulas)
oCell.Interior.ColorIndex = 36
MsgBox oCell.Formula
Next oCell


End Sub

Excel VBA Autofilter - Specify Multiple Criteria using Array:-

After long time let us revisit our good old Autofilter Fruits example.
If you need to filter say Oranges and Apples alone, you can either pass both criteria (Yes! I have avoided using - Mutliple criteria) or can try using an Array where you can pass multiple values



Sub AutoFilter_Using_Arrays()

Dim oWS As Worksheet

On Error GoTo Err_Filter

Dim arCriteria(0 To 1) As String

Set oWS = ActiveSheet

arCriteria(0) = "Apple"
arCriteria(1) = "Orange"

oWS.UsedRange.AutoFilter Field:=2, Criteria1:=arCriteria, Operator:=xlFilterValues

Finally:

If Not oWS Is Nothing Then Set oWS = Nothing

Err_Filter:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
GoTo Finally
End If
End Sub


If you leave out the Operator in Excel VBA Autofilter- Only Last Value of the Array Will be displayed.

How to enable Developer Tab in Office 2010:-

If the developer tab is not showing on your Ribbon UI, you can enable it from Application Options-->Customize Ribbon.

How to check compatibility issues in an Office Document:-

Microsoft Office is getting polished rapidly. Upgrades from 2003 to 2010 saw sea change in functionality. If you are using 2010 and sending it to your friend who hasn’t upgraded, It is better to do a compatibility check

A check mark appears next to the name of the mode that the document is in.

1. Click the File tab.

2. Click Info.

3. In the Prepare for Sharing section, click Check for Issues, and then click Check Compatibility.

4. Click Select versions to show.