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.

Testing Macros in the Visual Basic Editor for Excel

Testing the VBA procedure step by step

Testing is the most time-consuming part of any VBA project. During the development of a project you will use 20% of your time analysing and designing, 15% programming and 65% testing.

During the testing phase, you will correct bugs, typos and the logical errors. More importantly you will improve your original project, fine tune it, discover better ways to do things and add code.

You have created your first macro and tested it using the "Run" button. You can also test a macro step by step.

Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in topic.

(i) Go to Excel and make sure that cells A1, A2 and A3 of Sheet1 are empty.

(ii) In VBE go to the Code window of Sheet1 and copy/paste the following macro:

Sub PreTest()
Range("A1").Value = 09
Range("A2").Value = 22
Range("A3").Formula = "=A1+A2"
Range("A1").Select
End Sub

(iii) Click anywhere within the macro and then press the F8 key at the top of your keyboard. VBE highlights the first line of code in yellow.

(iv) Right-click on the small yellow arrow and see a menu appear

(v) Press on "F8" a second time. No line has been executed yet and if you go to Excel you will see that cells A1 to A3 are still empty. The next time you press "F8" , VBE will execute the yellow-highlighted line.

(vi) Press "F8" a third time. The yellow-highlighted line is now "Range("A2").Value = 66". VBE has executed the previous line "Range("A1").Value = 34" has been executed so if you go to Excel (ALT/F11) you will see 32 in cell A1.

(vii) Come back to VBE (ALT/F11) and press "F8" again. Go to Excel and see what happened in cell A2.

(viii) Come back to VBE (ALT/F11) and press "F8" again. Go to Excel and see that there is a formula in cell A3.

(ix) Come back to the VBE (ALT/F11) and press "F8" again, cell A1 is now selected in Excel.

(x) Press "F8" again. Nothing happens in Excel but "End Sub" is highlighted in yellow

(xi) Press "F8" again. Nothing happens in Excel no more lines in VBE are highlighted in yellow.

The macro hac been tested, the test is over.

In the code change the addresses A1, A2 and A3 respectively to B1, B2 and B3. Test the macro again. Do it as many times as you want.

You have tested a macro step by step. In the downloadable tutorial you will learn how to test parts of a macro, how to come back a few lines, make changes and re-execute these lines. You will also discover this most important functionality, how to share the screen between Excel and VBE.

The Code Window in the Visual Basic Editor of Excel

The Code Window is where 90% of the VBA work is done; writing VBA sentences, testing your VBA procedures (macros) and modifying them when needed.

To illustrate everything that you can do in the Code window we will start by creating a small macro in an empty workbook.

There are plenty of other operations that you can execute in the code window. For example, you can test a macro line by line (step by step), go back a few lines and make corrections, use breakpoints to test only part of a macro.