Tuesday, July 27, 2010

Excel VBA Autofilter - Specify Multiple Criteria using Array

How to pass an Array as Criteria in Excel Autofilter - VBA

After long time let us revisit our good old Autofilter Fruits example. The following figure shows the data available

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


If Not oWS Is Nothing Then Set oWS = Nothing

If Err <> 0 Then
MsgBox Err.Description
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

You can also pass the values directly like:

oWS.UsedRange.AutoFilter Field:=2, Criteria1:=Array("Apples","Peaches","Grapes), Operator:=xlFilterValues

Sunday, July 25, 2010

Program/Macro to Highlight Editable Ranges in Protected Sheet

How to identify Editable ranges in a protected Excel sheet using VBA

My good friend Srikanth Srinivasan is a Project Manager whom Microsoft will definitely want to hire as evangelist. He uses the functionality of Excel to great extent and made it ubiquitous.

The following code was for him, which highlights the ranges that are not protected in Excel sheet

Sub HighLight_Editable_Ranges()

Dim oWS As Worksheet
Dim oRng As AllowEditRange

Set oWS = ActiveSheet


For Each oRng In oWS.Protection.AllowEditRanges
oRng.Range.Interior.ColorIndex = 35
Next oRng


End Sub

Friday, July 23, 2010

How to retrieve value from Content Controls using Word VBA

The following snippet validates the user selection using VBA. This code uses the content control created in previous example - (How to add Content Controls using VBA)

Sub Validate_ContentControl()

Dim oCC As ContentControl
Dim OCCEntry As ContentControlListEntry

Set oCC = ActiveDocument.ContentControls(1)

For i = 1 To oCC.DropdownListEntries.Count
     If oCC.DropdownListEntries.Item(i).Text = oCC.Range.Text Then
        Set OCCEntry = oCC.DropdownListEntries.Item(i)
        ' Check the text against value - can be checked directly with text
        If OCCEntry.Value = 1 Then
            MsgBox "Correct"
            MsgBox "Try Again"
            Exit Sub
        End If
     End If
Next i

Thursday, July 22, 2010

How to add Content Controls using VBA

Add Combobox to Word document using VBA

The following code would add a Combo Box control to the existing Word document:

Sub Add_A_ContentControl()

Dim oCC As ContentControl

Set oCC = ActiveDocument.ContentControls.Add(wdContentControlComboBox, Selection.Range)
oCC.SetPlaceholderText , , "Which Team Won the World Cup 2010"

oCC.Title = "World Cup Teams"
oCC.DropdownListEntries.Add "Spain", 1
oCC.DropdownListEntries.Add "Netherlands", 0
oCC.DropdownListEntries.Add "France", 2
oCC.DropdownListEntries.Add "Uruguay", 3

' Prevents the Control from being deleted
oCC.LockContentControl = True
End Sub

Lock the control by setting the LockContentControl attribute to prevent it getting accidentally deleted.

The content control gets added as shown below

Monday, July 05, 2010

GetObject Error with Internet Explorer

How to get active Internet Explorer Object using Getobject in VBA

Set IEBrowser = GetObject(, "InternetExplorer.Application")

Using GetObject for Internet Explorer in VBA throws Runtime error 429 - ActiveX can't create object. The  solution for this is to use ShellWindows

Public Function IENavigate(ByRef IEBrowser) As Boolean

Dim theSHD As SHDocVw.ShellWindows
Dim IE As SHDocVw.InternetExplorer
Dim i As Long
Dim bIEFound As Boolean

On Error GoTo Err_IE
    Set theSHD = New SHDocVw.ShellWindows
    For i = 0 To theSHD.Count - 1
        Set IE = theSHD.Item(i)
        If Not IE Is Nothing Then
            If InStr(1, IE.LocationURL, "file://", vbTextCompare) = 0 And Len(IE.LocationURL) <> 0 Then
                If IE.Visible = True Then bIEFound = True: Exit For
            End If
        End If

    If bIEFound = True Then
        Set IEBrowser = IE
        IENavigate = True
        IENavigate = False
    End If
' -------------------------------------
' Error Handling
' -------------------------------------
    If Err <> 0 Then
        Resume Next
    End If
End Function

The above code uses Microsoft Internet controls reference:

without which the following error might occur

Microsoft Visual Basic for Applications
Compile error:

User-defined type not defined
OK Help

Once you get the Internet Explorer object, you can use it as shown below:

Sub GEt_IE()

  Dim IEBrowser As InternetExplorer
  IENavigate IEBrowser
  If Not IEBrowser Is Nothing Then
    MsgBox IEBrowser.Document.Title
  End If

Friday, July 02, 2010

How to extract file name from FullPath string using VBA

Extract Name of the File from Path / Fullname using VBA

There are many methods to extract the filename from a given string. You can use FileSystemObject's function GetFileName or can use Arrays to get the last element of the array split by path separator

Here we use even simpler functions like Dir and InStrRev to achieve the same

Dir function will retrieve the name only if the file exists:

strFilePath = "C:\Users\comp\Documents\sample.xlsx"

sFileName = Dir(strFilePath)

If the file doesn't exist, Dir function will return an empty string. The following would be a better option

strFilePath = "C:\Users\comp\Documents\sample.xlsx"

sFileName = Mid(strFilePath, InStrRev(strFilePath, "\") + 1, Len(strFilePath))

Try it out and post the options you use
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.