Change attributes of controls inside VBA userforms in easier way

Sometimes we need to use set of similar controls when developing applications with userforms. For an example we may need to use set of labels, dropdowns or textboxes etc inside a vba userform. If we have similar set of controls like that, then there are situations we may need to change the values or visibility of the controls according to the various logics and conditions.

Let's look at the following example.

Here we have 4 labels and 4 combo boxes. Sometimes we need to control the attributes of these controls or change the values of controls. If we give random names for each control then we need to control their attributes one by one or we need write one line per each control to change their values. But if we have similar set of controls and we name them simila manner, then we can change the attributes or values of the controls using a For Next loop. So for above example we can name lables like lblCycle1, lblCycle2, lblCycle3 and lblCycle4. And we can name combo boxes as cboScore1, cboScore2, cboScore3 and cboScore4. Then we can change the attributes as follows.

Dim i as Integer Dim NumberOfCycles as Integer For i = 1 To NumberOfCycles     UserForm2.Controls("lblCycle" & i).Visible = True     UserForm2.Controls("cboScore" & i).Visible = True Next i

In above example, variable "NumberOfCycles" equal to how many pairs of labels and combo boxes we need to show in userform2.

Add Button To Excel Worksheet

    We often need to add buttons to worksheets. We can use buttons for many things. Triggering a macro and navigate through worksheets are two very common use of buttons. Sometimes appearance of the button is not very important. But there are situations where we need to give higher concern for its appearance. Specially when creating dashboards. Because it is very important to give professional look to your dashboard.

    There are several types of buttons available. In this post I will show you how to add Form Control Button to your excel worksheet. Lots of people use this button to trigger macros. However I don't recommend these buttons for dashboards. Because we can't even change the color of these buttons to match with our dashboards. Also we can't change the font color of the button. But this type of buttons are very useful if you want to trigger macros. Because these buttons are compatible with all excel versions. Specially with Mac versions. So if you use this kind of button you can assure that your users will able to run macros by clicking the button from any OS and any Excel version.

    If you want to add a form control button to your worksheet, first go to the Developer tab of your excel application. If Developer tab is not visible in your Excel application,  below post will explain you how to do that.

How to show the Developer tab

After go to the developer tab, click on the insert menu. Then you will see this kind of icons.

Then click on the Button (Form Control). Then your mouse pointer will look like this.

Then click somewhere on the worksheet and drag like below to form small rectangle.

Then release the mouse. You will see this kind of pop up window.

This pop up window helps you to assign macros directly when you create the button. In this case no macros listed because there are no macros in opened files. So you can click OK to create the button without assigning any macro. Then you can assign macro later if you need. However if you have macros in your files, then this pop up will look like below.

So there are two macros in the files. One is MyFirstMacro and other is MyMacro. So if you need to assign any of these macros to your button, first click on the macro name and then click on the OK button.

When you click OK button, form control button will created in the worksheet like this.

Then you can edit text and also you can change font type, font size etc. But you can not change the color of font or color of the button.

Create Excel Timer Using VBA

    Today I'm going to show you how to create a Excel Timer. First we need to add two buttons to the excel sheet. One is to start timer. Other one is to stop the timer. And we need to reserve one cell to display the value. Here is an example interface.

We can give it nice look by removing grid-lines.

Now add new module in the VBA editor of your workbook. And add below code to it.

Public StopMacro As Boolean Sub StartTimer() StopMacro = False Dim WS As Worksheet Set WS = Worksheets("Sheet1") Dim StartTime, timeNow StartTime = Now Do Until StopMacro = True     DoEvents     timeNow = Now     WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss") Loop End Sub Sub StopTimer() StopMacro = True End Sub

Then assign StartTimer macro to start button and StopTimer macro to Stop button. I think you will able to understand this code easily. However I will explain few important points. In this program we have declared one public variable of type Boolean. We use that variable to detect when user click stop button. So when user click stop button, value of StopMacro variable become true. Then it is detected in do until loop.

In this program we used inbuilt Excel function call Now which returns date and time.

Value entering cell and format of the value is given by below line of code.

WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss")

Also it is very important to use DoEvents within the do until loop. Otherwise user will not get any chance to click on the stop button and loop will become infinite loop.

Do Until StopMacro = True     DoEvents     timeNow = Now     WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss") Loop

And this is how timer shows the time.

Pause Program Execution In VBA - Sleep and Wait Functions

Sometimes we need to pause the execution of our program for a specific time. In VBA, we can use a few methods to do this. VBA Wait method and Sleep functions are two popular methods used by VBA developers.

Why you need to use Sleep or Wait in VBA

• Sleep or wait method let other applications complete their processes.
  
  For example if you are developing a web scraping program, the Internet explorer should get sufficient time to load the webpage.
• To get input from users
  
  Sometimes you may want to let the user input data to a VBA form before completing the rest of the process.
• Let the user to use another program manually
  
  Using VBA we can automate various applications like MS Word, Powerpoint, Internet Explorer etc. But there are third party applications which we can not automate using VBA. In such cases we can use sleep function or wait method and let the user complete the middle process manually. Once that part is completed the macro can resume from that point.

Now Let’s look at how to use these two methods in VBA programs.

This is how to use the Wait method to pause a macro for 10 seconds.

Sub WaitForTenSeconds() Application.Wait (Now + TimeValue("0:00:10")) End Sub

However Sleep is a windows function. It is not a VBA function like Wait. So you have to declare the name of the API at the top of your module. So this is how you can pause the program for 10 seconds using the Sleep function.

#If VBA7 Then     Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal duration_Milliseconds As LongPtr) 'For 64 Bit Systems #Else     Public Declare Sub Sleep Lib "kernel32" (ByVal duration_Milliseconds As Long) 'For 32 Bit Systems #End If Sub WaitForTenSeconds() Sleep 10000     'You need to input the time in milliseconds End Sub

Here is a question lot of people get

Should I use a VBA sleep or Wait

First of all Sleep is not a VBA function. Sleep is a windows function. You should import it from Kernel32.dll

What is a Kernel
The kernel is the lowest level of any operating system. And it is the core or the central component of an operating system. It starts when the computer starts and keeps loaded until it is turned off. kernel manages the CPU resources, memory resources and processes of the computer. It also contains device drivers. Therefore when you do networking or use the file system, they all go through the kernel.

So functionality of both of these methods are the same. However Wait is more accurate than Sleep. But Sleep is more flexible as you can give the time in milliseconds. If you use Wait method, shortest time you can pause the program is 1 second.
Also there is another important aspect you need to consider. That is, if you use sleep or wait in VBA programs, you will notice that these functions suspend all other activities of Microsoft Excel. You can't even click in the excel sheets. However, you can work in other applications.

But sometimes you may need to pause the program for a longer time. And while the program is paused, you may need to do some work in the Excel application like typing, navigating between sheets, scrolling through the sheets etc. We don't often need this. But it may be needed specially in web scraping applications. Because sometimes users need to analyze gathered data while the web scraping program runs. However there is no VBA function or VBA method available for this. So we need to create our own custom pausing method. Read this post if you want to know how.

How to pause a macro for specific time

VBA Web Scraping - Identifying Disabled Dropdowns

In this post I will explain how to identify a disabled drop-down.

Think we need to gather data from drop downs of list of web pages. Assume list of urls are in column A of an Excel worksheet and we need to put the value of the drop-down to a column B.

First we need to find a disabled drop down and compare it's HTML code with HTML code of normal drop-down to find something unique part for disabled ones.

For an example here is a HTML code of a normal drop down.

<select start="1" min="1" step="1" ptext="Quantity: " name="tmp" class="drp qty">

And below is a HTML code of the disabled drop down.

<select start="1" min="1" step="1" ptext="Quantity: " disabled="disabled" name="tmp" class="drp qty">

So now we can see that disabled="disabled" is unique for HTML codes of disabled drop downs. Because of that we can use Instr function to distinguish disabled drop downs.

DisableCheckingString = objIE.document.getElementsByClassName("drp qty")(0).outerhtml If InStr(1, DisableCheckingString, "disabled=""disabled", vbTextCompare) > 0 Then     WS.Range("B" & i).Value = "Drop-down disabled" End If

   
So here is a full code of an example.

Dim WS As Worksheet Dim QuantityString As String Dim DisableCheckingString As String Dim url As String Set objIE = CreateObject("InternetExplorer.Application") Set WS = ActiveSheet objIE.Top = 0 objIE.Left = 0 objIE.Width = 800 objIE.Height = 600 objIE.Visible = True 'find last row Lastrow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row For i = 1 To Lastrow     url = WS.Range("A" & i).Value         If WS.Range("A" & i).Value <> "" Then         objIE.Navigate (url)                 Do         DoEvents         Loop Until objIE.readystate = 4                 Application.Wait (Now + TimeValue("0:00:03"))                 QuantityString = objIE.document.getElementsByClassName("drp qty")(0).innerText                 DisableCheckingString = objIE.document.getElementsByClassName("drp qty")(0).outerhtml                 WS.Range("B" & i).Value = QuantityString                         'Distinguish disabled ones         If InStr(1, DisableCheckingString, "disabled=""disabled", vbTextCompare) > 0 Then             WS.Range("B" & i).Value = "Out of Stock"         End If             End If Next i objIE.Quit MsgBox "Completed!", vbInformation, ""

Upper Case, Lower Case and Proper Case

In this post I will show you how to use VBA functions "UCase", "LCase" and worksheet function Proper to format the text in excel sheet. I will explain this using below example. Assume we have list of names like this.

So we have 10 names in sheet 1. As you can see that names are not in a proper format. So now let's look at how to convert this 10 names to upper case first. You can use below code to do that.

Sub ConvertToUpperCase() Dim WS_Input As Worksheet Dim WS_Result As Worksheet Dim i As Integer Set WS_Input = Worksheets("Sheet1") Set WS_Result = Worksheets.Add For i = 1 To 10     WS_Result.Range("A" & i).Value = UCase(WS_Input.Range("A" & i).Value) Next i End Sub

So this will convert the names to upper case and list them in a new sheet. So this is the result you will get after running the code.

You can see that all the letters have converted to upper case. So now let's look at how to convert that names to lower case.  Below code will do that.

Sub ConvertToLowerCase() Dim WS_Input As Worksheet Dim WS_Result As Worksheet Dim i As Integer Set WS_Input = Worksheets("Sheet1") Set WS_Result = Worksheets.Add For i = 1 To 10     WS_Result.Range("A" & i).Value = LCase(WS_Input.Range("A" & i).Value) Next i End Sub

You can change the limits of for next loop to do this for any amount of rows. Also you can modify the code to replace existing text in sheet 1 with result text. After running this macro result will look like this.

So now let's look at how to convert these names to proper case or title case. This is little different than other two. Because in earlier two cases, we used VBA functions. But this time we need to use a worksheet function. Because of that Proper(WS_Input.Range("A" & i).Value) will not work this time. Instead we have to use Application.WorksheetFunction.Proper(WS_Input.Range("A" & i).Value). Here is the full code.

Sub ConvertToProperCase() Dim WS_Input As Worksheet Dim WS_Result As Worksheet Dim i As Integer Set WS_Input = Worksheets("Sheet1") Set WS_Result = Worksheets.Add For i = 1 To 10     WS_Result.Range("A" & i).Value = Application.WorksheetFunction.Proper(WS_Input.Range("A" & i).Value) Next i End Sub

You will get below result after running this macro.

So modify above codes to suit with your requirements and improve your workbooks.

Web Scraping - Collect Options Inside Drop-down Lists

When we develop web scraping scripts we may need to collect items (options)  inside drop-down lists. So let's look at how to develop a code to collect options inside a select tag.

If you are new to Web Scraping, please read this post first.

Web Scraping - Basics

This is how drop-downs look like.

Below is the source code of above drop-down list.

<select d-start="1"  name="qty" class="name1"><option value="1" selected="selected">Quantity: 1</option><option value="2">Quantity: 2</option><option value="3">Quantity: 3</option></select>

So if you need to collect options inside this drop down list, you can use below code.

Dim QuantityString As String Set objIE = CreateObject("InternetExplorer.Application") objIE.Top = 0 objIE.Left = 0 objIE.Width = 800 objIE.Height = 600 objIE.Visible = True objIE.Navigate ("Your url here")   Do DoEvents Loop Until objIE.readystate = 4 QuantityString = objIE.document.getElementsByClassName("name1")(0).innerText

So what it does is, it will store the options inside the drop-down to variable call "QuantityString"

If you want to see the result in the message box use below line.

MsgBox QuantityString

So it will look like this.

However sometimes these drop-down lists may in disabled status. So now let's look at how to detect if a particular drop down list is in a disable status. Here below is a sample HTML code of a disabled drop-down list.

<select disabled="disabled" dstart="1" class="name2"><option value="1" selected="selected">Quantity: 1</option><option value="2">Quantity: 2</option><option value="3">Quantity: 3</option></select>

So here what we can do is, first assign whole code inside Select tag to a new variable. Then check that string for disabled="disabled". You can use inbuilt VBA function like InStr to check that.

This is how you can do it.

Dim DisableCheckingString As String Set objIE = CreateObject("InternetExplorer.Application") objIE.Top = 0 objIE.Left = 0 objIE.Width = 800 objIE.Height = 600 objIE.Visible = True objIE.Navigate ("Your url here")   Do DoEvents Loop Until objIE.readystate = 4 DisableCheckingString = objIE.document.getElementsByClassName("name2")(0).outerhtml If InStr(1, DisableCheckingString, "disabled=""disabled", vbTextCompare) > 0 Then     msgbox "This combo box is disabled" End If

You can alter this code to suit with your requirements. Because you may not need to show message box when the drop down list is disabled in real life examples. Instead you may need to skip those drop downs or may be you will need to mention about them in the result pages you create.

Web Scraping - Collecting Data From a Webpage

Web pages have various HTML tags. So the data we want to collect can be contained in any of these tags. Because of this you will need to carefully examine the HTML structure of the webpages before develop codes.

Web browsers have special tool which helps us to look at the structure of the web pages very easily. For an example, think you need to collect names of the people which is shown in a webpages. In this example each name is shown in separate page. But structure of each page is identical. So what you need to do first is, take your cursor near the name. Then write click. You will see this kind of options.

Then click on the Inspect.

You will able to see the structure of the web-page. Assume the code relate to name is look like this.

<span itemprop="full name" class="full name block">James Smith</span>

So I will show you how to collect name from this kind of structure. Here we can extract name using class name as follows.

objIE.document.getElementsByClassName("name block")(0).innerText

objIE should be defined before like

Set objIE = CreateObject("InternetExplorer.Application")

If you are not familiar with those basic web scraping, you can learn them from my earlier post.

Web Scraping - Basics

So let's now look at how to extract data from hyperlinks. Assume telephone numbers are stored in a hyperlink like below.

<a class="click-link" data-gaaction="ourbusiness" data-gaevent="pho_number" href="tel:9128602884" itemprop="phone">912-860-2884</a>

We can use below type of VBA code to get the telephone number.

Dim telephone_number As String Set Alllinks = objIE.document.getElementsByTagName("A") For Each Hyperlink In Alllinks     If InStr(1, Hyperlink.href, "tel:", vbTextCompare) > 0 Then         telephone_number = Hyperlink.innerText         Exit For     End If Next

Also we can use following technique to get text inside div tags.

Dim AllString As String         Set AllDiv = objIE.document.getElementsByTagName("div") For Each DivTag In AllDiv     AllString = DivTag.innerText Next

Then we can use functions like InStr, Split etc.. to extract relavent information from that "AllString" variable.

Web Scraping Techniques

In this post we are going to discuss about few more techniques used in web scraping. If you are new to web scraping, please read my earlier posts from below.

Web Scraping - Basics

Useful References for VBA Web Scraping

In this post I'm going to explain about more advance techniques.

When we do the data mining, sometimes we need to go to certain websites, put some value in text box and click on "Search" button. Then website gives list of results. These results sometimes may in several pages. If you want to automate this kind of process, you can develop a code to go to that url, then put value to text box, and then write a code to click button and so and so. You can learn how to write this kind of code from my earlier post.

But sometimes this can be easier than you think. Because it may be possible to get result of each page by changing the url. For an example if the website gives results in several pages, url's of each page sometimes have part like "?page=1", "?page=2" etc. So you can develop a program to get data from each page directly. And if you are searching for some value in textbox, then that value also can contained in the url. So it is always better to carefully look at the url and try to find some patterns.

Then sometimes we need to open the each result in separate pages to get all the information of each and every result. Think website have hyperlink like "View" for each result. So we can collect url of each of this "View" link from following code.

Set objIE = CreateObject("InternetExplorer.Application") url="url of particular result page" objIE.Navigate (url) Dim WS as worksheet set WS=activesheet 'wait to load page... Do DoEvents Loop Until objIE.readystate = 4 Set Alllinks = objIE.document.getElementsByTagName("A")     For Each Hyperlink In Alllinks         If InStr(Hyperlink.innerText, "View") > 0 Then             WS.Cells(row, 1).Value = Hyperlink.href             row = row + 1         End If     Next

So what it does is, it lists url of each result in a worksheet. Also you should have some idea about how many result pages the website will give. Then you can set suitable upper limit to the number of pages and should use some technique to exit the loop when there are no more result pages. Here below is example of such program.

Res = InputBox("Please enter number You want to search?", "") Set objIE = CreateObject("InternetExplorer.Application") objIE.Top = 0 objIE.Left = 0 objIE.Width = 1100 objIE.Height = 700 objIE.Visible = True Dim i, row As Integer Dim url, isdata As String Dim WS As Worksheet Set WS = Worksheets("DataBase") i = 1 row = 1 For i = 1 To 100     'genarate url...     url = "your website url+ ?page=" + CStr(i) + "There may be some additional parts of url here" + CStr(Res)     objIE.Navigate (url)         'wait to load page...     Do     DoEvents     Loop Until objIE.readystate = 4         'collect links...     isdata = "n"         Set Alllinks = objIE.document.getElementsByTagName("A")     For Each Hyperlink In Alllinks         If InStr(Hyperlink.innerText, "View") > 0 Then             WS.Cells(row, 1).Value = Hyperlink.href             isdata = "y"             row = row + 1         End If     Next     If (isdata = "n") Then         i = 101     End If Next i objIE.Quit

Determine whether characters inside a cell are bold or regular

Sometimes we need to determine whether characters inside a cell are bold or regular. And some cells can contain both bold and regular characters. Here is an example.

So today we are going to develop a macro to determine whether each and every character inside a cell is regular or bold. What it does is, it outputs each letter in immediate window and tells whether that each letter is bold or regular. Here below is the full code.

Dim i As Integer Dim Sentence_Length As Integer Sentence_Length = Len(Range("B2").Value) For i = 1 To Sentence_Length     Debug.Print Range("B2").Characters(i, 1).Text         If Range("B2").Characters(i, 1).Font.Bold = True Then         Debug.Print "Bold"     Else         Debug.Print "Regular"     End If Next i

Below is the explanation of above code.

First we need to define our variables. Then below line of code measure the number of characters inside B2 cell.

Sentence_Length = Len(Range("B2").Value)

And this for next loop, loop through all the letters in B2 cell.

For i = 1 To Sentence_Length     Next i

Below line prints the each character in immediate window. Please note that this program will consider spaces also as characters.

Debug.Print Range("B2").Characters(i, 1).Text

This part of the code checks whether the character is bold or regular and print the result in immediate window.

If Range("B2").Characters(i, 1).Font.Bold = True Then         Debug.Print "Bold" Else         Debug.Print "Regular" End If

Here is an image of immediate window after running the macro.

So this is just a very simple example. You can alter this code to suit with your requirements.

Useful References for VBA Web Scraping

        This post is to give small tip related to web scraping. If you are  new to web scraping please see my earlier post which explain everything from beginning. Below is the link to that post.

Web Scraping - Basics

In this post I will explain you what are the best VBA references you should add when you develop a Visual Basic Application to scrape data from websites. Adding these references are not mandatory. But it will make your life easier. So here are the list of useful references.

• Visula Basic for Applications
• Microsoft Excel 15.0 Object Library
• OLE Automation
• Microsoft Office 15.0 Object Library
• Microsoft Forms 2.0 Object Library
• ietag 1.0 Type Library
• iextag 1.0 Type Library
• Microsoft HTML Object Library
• Microsoft Internet Controls

As you can see that there are numbers like 15.0 etc. These numbers can vary according the versions you have installed in your computer.

Also when you open your VBA editor you will notice that first four items of the list are automatically ticked by default. So you don't need to add them manually.

And when you insert form for the first time to your project, VBA editor will automatically add reference to Microsoft Forms 2.0 Object Library. So you also don't need to worry about adding the reference to that library.

Ultimately you have shorter list to add references manually. Here is that list

• ietag 1.0 Type Library
• iextag 1.0 Type Library
• Microsoft HTML Object Library
• Microsoft Internet Controls

Once you add references, your references window should look like this.

Shapes

We often need to use shapes in Excel worksheets. You can insert various kinds of shapes to Excel worksheets from insert menu. Also you can use your own pictures as shapes. Here are few VBA examples related to shapes.

If you want to assign macro programmatically to your shape you can use below method.

Sub AssignMacro() ActiveSheet.Shapes("Picture 20").OnAction = "'" & ActiveWorkbook.Name & "'!Macro1" End Sub

You may want to know the row number of cell of top left of your shape. You can use below code for that.

Sub TopLeftCellRowNumber() MsgBox ActiveSheet.Shapes("Picture 20").TopLeftCell.Row End Sub

Below code will select all your shapes which have "picture" in its name.

It will select one shape at a time. Then will wait for 1 second and will select next shape.

Sub SelectPictureShapes() Dim sh As Shape     For Each sh In ActiveSheet.Shapes         If InStr(1, sh.Name, "picture", vbTextCompare) > 0 Then             sh.Select             Application.Wait Now + TimeValue("00:00:01")         End If Next End Sub

Sometimes you may need to know the name of the clicked shape in your program. Because sometimes your program will need to do different things depend on what shape is clicked. So below code will show the name of clicked shape in a message box. You can develop this to suit with your needs.

msgbox ActiveSheet.Shapes(Application.Caller).Name