Monthly Archives: July 2011

Deselect Items in MultiSelect Listbox without looping


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/07/29/deselect-items-in-multiselect-listbox-without-looping/

Consider this Multiselect Listbox. I simply used the following code to generate it.

Private Sub UserForm_Initialize()
    For i = 1 To 100000
        ListBox1.AddItem i
    Next i
End Sub

MultiSelect Listbox

Now imagine, I have some 50 entries selected and after my work is done, I would like to deselect them. The only options that I have are either I

1) Manually unselect them OR

2) Write a code which will loop through the entries and deselect them like this?

For i = 1 To ListBox1.ListCount
    ListBox1.Selected(i - 1) = False
Next i

Right?

Wrong! There is a third way 🙂

I see two problems with the 2nd way.

1) Looping takes time if Listbox contains huge data

2) It causes a flicker if Listbox contains huge data

Here is an extraordinary way to do it. I just discovered it on my own. I quickly did a search on the web and couldn’t find a single website which suggested this.

Private Sub CommandButton1_Click()
    '~~> Reset selected items to deselected by changing it's selection behavior
    ListBox1.MultiSelect = fmMultiSelectSingle
    ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

Hope this helps 🙂

Excel Data Validation–Create Dynamic Dependent Lists (VBA)


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/07/29/excel-data-validationcreate-dynamic-dependent-lists-vba/

There are times when you need to create dependent lists from a dynamic range. The data for the list can come from, say an SQL Database or even a website.

Lets say we came across a list on a website which is similar to this.

Country City
India Mumbai
Pakistan Karachi
Bangladesh Dhaka
India Delhi
India Chennai
Pakistan Lahore
Bangladesh Munshiganj
Pakistan Islamabad
Pakistan Peshawar
India Kolkata
Bangladesh Chandpur
India Lucknow
India Srinagar
Pakistan Abottabad

Now we want to create two data validation list, where the first list shows the name of the Country and the second shows the names of the Cities. Also what we would like is that if I select “India” from the first list then the second list should automatically display the cities from India only. So how do we achieve this?

The below code will help you create dependent lists just by simply pasting data in the Source Columns. For the sake of simplicity we will copy and paste the above list in Column A and Column B of an Excel Sheet, say “Sheet1”. However before we do that, we have to paste the below code in the Sheet Code Area. The sheet code area can be accessed by pressing Alt+F11 from the main worksheet. If you want to access the Visual Basic Editor from the menu then do this.

Excel 2003 : Click on the Tools menu and then click on Macros

2003

Excel 2007 : Click on the Developer Tab and then click on the “Visual Basic” as shown in the picture below

Developer Tab 2007

Excel 2010 : Click on the Developer Tab and then click on the “Visual Basic” as shown in the picture below

Developer Tab 2010

Excel 2011 (MAC) : Click on the Developer Tab and then click on the “Visual Basic” as shown in the picture below

MAC 2011

Once you have accessed the Visual basic Editor, the screen will look like this (This screen is from 2007. The other versions will have a similar screen).

VBE

Double click on the Excel object Sheet1 (Sheet1). The moment you double click, the code area on the right hand side will open up for you. Simply paste the code there and you are ready Smile

VBE-Sheet Area

The Code:

I have commented the code so that it is easier to understand.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, LastRow As Long, n As Long
    Dim MyCol As Collection
    Dim SearchString As String, TempList As String

    Application.EnableEvents = False

    On Error GoTo Whoa

    '~~> Find LastRow in Col A
    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Set MyCol = New Collection

        '~~> Get the data from Col A into a collection
        For i = 1 To LastRow
            If Len(Trim(Range("A" & i).Value)) <> 0 Then
                On Error Resume Next
                MyCol.Add CStr(Range("A" & i).Value), CStr(Range("A" & i).Value)
                On Error GoTo 0
            End If
        Next i

        '~~> Create a list for the DV List
        For n = 1 To MyCol.Count
            TempList = TempList & "," & MyCol(n)
        Next

        TempList = Mid(TempList, 2)

        Range("D1").ClearContents: Range("D1").Validation.Delete

        '~~> Create the DV List
        If Len(Trim(TempList)) <> 0 Then
            With Range("D1").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:=TempList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    '~~> Capturing change in cell D1
    ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
        SearchString = Range("D1").Value

        TempList = FindRange(Range("A1:A" & LastRow), SearchString)

        Range("E1").ClearContents: Range("E1").Validation.Delete

        If Len(Trim(TempList)) <> 0 Then
            '~~> Create the DV List
            With Range("E1").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:=TempList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

'~~> Function required to find the list from Col B
Function FindRange(FirstRange As Range, StrSearch As String) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Dim strTemp As String

    Set aCell = FirstRange.Find(what:=StrSearch, LookIn:=xlValues, _
    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    ExitLoop = False

    If Not aCell Is Nothing Then
        Set bCell = aCell
        strTemp = strTemp & "," & aCell.Offset(, 1).Value
        Do While ExitLoop = False
            Set aCell = FirstRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                strTemp = strTemp & "," & aCell.Offset(, 1).Value
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = Mid(strTemp, 2)
    End If
End Function

Once the code is pasted, you can directly paste the list in Column A and Column B and the Data Validation List will be created in D1. Now when you select an option in D1, the Dependent Data Validation List will automatically be created in cell E1.

Attached is a video which shows the entire process. In the video, If you notice, I changed one of the city and it reflected in the Data Validation List.

Dynamic Dependent Lists

The above code can be amended to create more than 1 Dependent Data Validation List. You can also amend it so that the Column A and Column B List can be copied to a different Worksheet and you can base your Data Validation Lists on them. This is useful, if you do not want the user to see your list.

Excel 2011 for Newbie (For Mac)


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/07/26/excel-2011-for-newbie-for-mac/

 

I was recently gifted a Mac by one of my clients for a job well done :).

The very first thing that I did on it was to install MS – Office. I already have 2 pcs and a laptop [Sony VGN-FZ27G with a triple boot between (XP + Office 2003 + VB6), (VISTA + Office 2007 + VS2008) and (Win7 + Office 2010 + VS2010)]. And now I have MacBook Pro to my collection.

As suspected, the moment I opened Excel 2011, I was confused to see an entirely new screen. Except the Tab Names and the usual look of Excel Cells, everything seemed pretty new to me. The very first challenge that I faced was to find the “Options” menu. Yes, I am referring to the

Excel 2003: Tools ~~> Options
Excel 2007: Office Button ~~> Excel options
Excel 2010: File Tab ~~> Options

The next problem that I faced was ‘Navigating‘. I hardly use the mouse but since the shortcut keys that I knew no longer worked in Excel 2011, I was back, totally dependent on the mouse. After 6 months, I am pretty comfortable with Excel 2011. However I am still no GURU and I am still learning every day 🙂

In this post, I will share lot of information regarding Excel 2011 that I learnt. If you feel this helped then do leave a comment. 🙂

Topics Covered (Will keep on adding more topics)

1) Find the ‘Options’ menu (Updated 26th July)
2) Changing Preferences (Updated 26th July)

———————- X ———————-

1) Find the ‘Options’ menu

If you refer to the image below, you will see that we have an option called “Excel” in the topmost menu next to the ‘Apple‘ icon. When you click on that, you will see the menu unfold. There you will see a menu called ‘Preferences‘. That is the Options Menu.


 2) Changing Preferences

If you click Excel ~~> Preferences as mentioned above you will be presented a dialog box which looks like below. There are 3 categories

  • Authoring:
  • Formulas and Lists:
  • Sharing and Privacy:

Let’s cover them one by one

  • Authoring: This section has 5 sub sections which are

General: This section controls settings, such as some menu, sound and appearance options.


View: This section displays your workbook and which types of objects appear on the screen


Edit: This section controls how Excel behaves while you are editing a workbook


AutoCorrect: This section controls how Excel automatically corrects text as you type


Chart: And this section is how Excel plots your graph

  • Formulas and Lists: This section has 6 sub sections which are

Calculation: This section controls how Excel performs calculation


Error Checking: This section controls how Excel checks for errors in workbooks


Custom Lists: This section lets you manage the lists that you create to automatically fill cells or sort data in unique ways


AutoComplete: This section automatically completes cell entries and function names as you type


Tables: This section sets how Excel references formulas in tables and controls table behavior


Filter: this section controls how data is displayed in the filter panel

  • Sharing and Privacy: This section has 5 sub sections

Save: This section controls how Excel saves workbooks. To set the default format for the saved files, go to the next subsection which is ‘compatibility’


Compatibility: This section lets you set the default format for saved files. It also has other compatibility options.


Security: Controls settings for privacy and security


Feedback: This section offers an opportunity to learn about and control your participation in the Microsoft Customer Experience Improvement Program


Ribbon: Controls the behavior and appearance of the ribbon. You can display the ‘Developer’ Tab from here.

You can also access the Ribbon dialog box directly from the worksheet by clicking the “Ribbon Preferences“. See picture below

.Find and .FindNext In Excel VBA


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/

 

The most under utilized feature of Excel when it comes to finding data is .Find and .FindNext.

Most of us use loops in case we want to find something in Excel. For Example, Let’s say Our data is in sheet1 from Cell A1 to A65000 and the data is like this

A1 ~~> 1
A2 ~~> 2
A3 ~~> 3
A4 ~~> 4
A5 ~~> 5



A65000 ~~> 65000

Now suppose we want to find which cell has say 10000. The primitive way was to loop through each cell and find which cell had that value. For Example

Sub Sample()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim t As Long
    t = GetTickCount
    On Error GoTo Err
    Set oSht = Sheets("Sheet1")
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    strSearch = "10000"
    For i = 1 To lastRow
        If oSht.Range("A" & i).Value = strSearch Then
            MsgBox "Value Found in Cell " & oSht.Range("A" & i).Address & vbCrLf & _
            "and it took " & GetTickCount - t & " milliseconds"
            Exit Sub
        End If
    Next i
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

This method of looping is not ‘wrong‘ but yes it is very slow as compared to Excel’s inbuilt “.Find” Tool. The above Sub executed in 109 milliseconds on my laptop.

In this tutorial, I will stress on how to use .Find to make your search faster.

The syntax of .Find is

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Where

Expression (Required): is any valid range Object. So if we take the above example then the range would be Range(“A1:A” & lastRow)

What (Optional Variant): is the “Search value”

After (Optional Variant): The cell after which you want the search to begin.

LookIn (Optional Variant): The type of information. (xlValues or xlFormulas)

LookAt (Optional Variant): Can be one of the following XlLookAt (constants): xlWhole or xlPart.

SearchOrder (Optional Variant): Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.

SearchDirection: Can be one of these XlSearchDirection constants. xlNext default xlPrevious

MatchCase (Optional Variant): True to make the search case sensitive. The default value is False.

MatchByte (Optional Variant): Used only if you’ve selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.

SearchFormat (Optional Variant): The search format.

Let’s now take different scenarios on how to work with “.Find

The other scenarios that we will work on are as follows…

1) Find Values in Cell Value (See Section 1 below)

2) Find Values in Cell Formula (See Section 2 Below)

3) .FindNext (See Section 3 Below)

4) Making .Find work as Vlookup() formula (See Section 4 Below)

5) Using .Find and .FindNext in a Used-Defined Function (See Section 5 Below)

Section 1

Now let’s try and incorporate .Find to find the data that we want.

Sub Sample1()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim t As Long
    Dim aCell As Range
    t = GetTickCount
    On Error GoTo Err
    Set oSht = Sheets("Sheet1")
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    strSearch = "10000"
    Set aCell = oSht.Range("A1:A" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        MsgBox "Value Found in Cell " & aCell.Address & vbCrLf & _
        "and it took " & GetTickCount - t & "milliseconds"
    End If
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

The above sub took 6 milliseconds as compared to 109 milliseconds on the ‘looping’ sub !!!

Section 2

Find Values in Cell Formula

Let’s say our data is as follows

Quote:

B1 ~~> 1

B2 ~~> 2

B3 ~~> 3

B4 ~~> 4

B5 ~~> 5

B100 ~~> 100

And This

C1 ~~> 1

C2 ~~> 2

C3 ~~> 3

C4 ~~> 4

C5 ~~> 5

C100 ~~> 100

And Finally This

A1 ~~> =SUM(B1:C1)

A2 ~~> =SUM(B2:C2)

A3 ~~> =SUM(B3:C3)

A4 ~~> =SUM(B4:C4)

A5 ~~> =SUM(B5:C5)

A27~~> =MAX(B5:C5)

A100 ~~> =SUM(B2:C2)

Now I want to Find the word “MAX” in the formula and replace it with say “SUM” so using .Find we can achieve it in the following manner.

Note that since we are searching for values in the formula then LookIn takes the value of xlFormulas

Sub Sample2()
    Dim oSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String
    Dim aCell As Range
    On Error GoTo Err
    Set oSht = Sheets("Sheet2")
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    strSearch = "MAX"
    Set aCell = oSht.Range("A1:A" & lastRow).Find(What:=strSearch, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        MsgBox "A"
        aCell.Formula = Replace(aCell.Formula, strSearch, "SUM")
    End If
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Section 3

.FindNext

Let’s say our data is as follows

Quote:

A1 ~~> 1

A2 ~~> 2

A3 ~~> 3

A4 ~~> 4

A5 ~~> 5

A27~~> 2

A45~~> 2

A100 ~~> 100

If you have noticed that Cell A2, A27, A45 has the same value which is 2

So if I want to find all “2” ‘s one after the other then how do I do that using .Find. It is very simple. We use .FindNext in a loop.  See example below.

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean
    Dim SearchString As String, FoundAt As String
    On Error GoTo Err
    Set ws = Worksheets("Sheet3")
    Set oRange = ws.Columns(1) 

    SearchString = "2"
    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        Set bCell = aCell
        FoundAt = aCell.Address
        Do While ExitLoop = False
            Set aCell = oRange.FindNext(After:=aCell) 

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                FoundAt = FoundAt & ", " & aCell.Address
            Else
                ExitLoop = True
            End If
        Loop
    Else
        MsgBox SearchString & " not Found"
    End If
    MsgBox "The Search String has been found these locations: " & FoundAt
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Section 4

Making .Find work as Vlookup() formula

Lets Say we have a database (see picture) and a range where data needs to be updated (see picture)

Find Example

Now suppose we have to find the capitals for the relevant companies then we can use .Find to get the relevant companies. For Example

Sub Sample()
    Dim ws As Worksheet
    Dim DataRange As Range, UpdateRange As Range, aCell As Range, bCell As Range
    On Error GoTo Err
    Set ws = Worksheets("Sheet4")
    Set UpdateRange = ws.Range("B5:B16")
    Set DataRange = ws.Range("J5:J16")
    For Each aCell In UpdateRange
        Set bCell = DataRange.Find(What:=aCell, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
            aCell.Offset(, 1) = bCell.Offset(, 1)
        End If
    Next
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Section 5

Using .Find and .FindNext in a Used-Defined Function

Here comes the tricky part.

.FindNext doesn’t work in a User-Defined Function as expected. You can use it in a normal function.

Lets Say We have this data in Sheet1

A1~~> Colt

A2~~> Holt

A3~~> Dolt

A4~~> Hello

and in

B1~~> olt

Now if we paste the below code in a module and run it then we will get the expected result as $A$1:$A$3

Sub Test()
    Sample Sheets("Sheet1").Range("B1"), Sheets("Sheet1").Range("A1:A4")
End Sub 

Sub Sample(FirstRange As Range, ListRange As Range)
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange 

        Do While ExitLoop = False
            Set oRange = ListRange.FindNext(After:=oRange) 

            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        MsgBox aCell.Address
    Else
        MsgBox "Not Found"
    End If
End Sub

However it would not work as expected if you paste this function in a module and call it from a worksheet as (Say in Cell C1) =FindRange(A1,A1:A5)

The code will only give you the 1st instance of the value found and ignore the rest

And hence the result that you will get is $A$2!!!

Function FindRange(FirstRange As Range, ListRange As Range) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange 

        Do While ExitLoop = False
            Set oRange = ListRange.FindNext(After:=oRange) 

            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = aCell.Address
    Else
        FindRange = "Not Found"
    End If
End Function

We need to approach this from a different angle.

Instead of using .FindNext we use .Find again till we get the desired result ($A$1:$A$3). See the code below which works

Function FindRange(FirstRange As Range, ListRange As Range) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange 

        Do While ExitLoop = False
            Set oRange = ListRange.Find(what:=FirstRange.Value, After:=oRange, LookIn:=xlValues, _
            lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = aCell.Address
    Else
        FindRange = "Not Found"
    End If
End Function

Hope this small article helps 🙂

My First Blog!


I have been contemplating to create a Blog for quite sometime now. In fact, I have been planning for almost 3 years now. Finally my wish comes true. I have been awarded the Microsoft’s Most Valuable Professional (MVP) for the Visual Basic program for the year 2011, This is an annual award for those who display technical competence and a willingness to help others. This award is actually responsible for helping me decided on finally starting this blog.

You may learn more about the MVP program HERE.

If you are interested in becoming an MVP yourself then do see this LINK