Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

How to show the Developer tab in Excel 2013

                     This post is for people who are new to VBA. If you are thinking of creating macros or writing VBA scripts then this should be the first step you should take.
When you open your MS Excel application, you will see that the Developer tab is not displayed by default. So you need to add Developer tab to the ribbon before write/read macros, use ActiveX/Form controls. This post will explain how to add this Developer tab step by step.

First open the excel application.


Then click on the blank workbook. This will generate a new workbook.


Once the new workbook created you can see that there are tabs like File, Home, Insert , Page Layout etc. But there is no Developer tab. So now click on the File tab. I have marked it by a red circle in the above image.


Then click on the options button. Once you click the button, Excel options dialog box will be opened.


On that dialog box, choose Customize Ribbon button.


Then you will see list of Main tabs in the right hand side of this dialog box. All the tabs are selected except Developer. Select it as well and click on OK button.


Developer tab will appear at the end.




Now you can run and record macros.

Getting started with Visual Basic Editor in Excel

        In my earlier posts, I have explained lot of things on how to automate your excel workbooks with VBA. Also I explained how to do data mining from websites and download files automatically using VBA. However if you are new to Excel VBA,  you may probably don't know how to get Visual Basic Editor in Excel 2013. So today I will show you how to open Visual Basic Editor in your Excel application and give some brief introduction. First open an excel workbook where you want to develop your VBA.













Then click on the "DEVELOPER" tab. You will see several categories like Code, Add-Ins etc under that tab. Go to the "Code" category and click on the "Visual Basic" Icon















Once you click on that icon the Visual Basic Editor will be open up.


There is an alternative way to go to the Visual Basic Editor very quickly. It is using short cut keys. Once you open the workbook just press Alt+F11. Visual basic editor will be open in the excel application.

So now I will explain few things about Visual Basic Editor. You can find the "Menu Bar" in the top.


You can see the "Tool Bar" below that.



As you can see there are two other windows open in the Visual basic editor. Top one is called "Project Explorer window"



Other one is called "Properties Window"





When you developing applications you may need an another important window.  It is "Immediate" window. Just click on the "View" menu of the Menu bar. You will see a drop down list like below.



Then click on the Immediate Window. Immediate Window will be open at the bottom of the Visual basic editor. Also you can use short cut keys Ctrl+G to open that window.


This can be use for debugging the code.

Check whether folder exist and create if it is not

                We can use VB scripts to manage our data. In this kind of applications we often need to get data from folders as well as save data to folders. This data may in any form like text files, XML files, Excel files etc. Some of our folders contains sub folders. Those sub folders can even have their sub folders. So a particular folder can have folders inside them in different levels. If we know the structure of those folders we can even communicate with folder which are located inside several folders.

Below code is a similar example used in a excel file. It check whether there is a folder call "Image Files" in the folder where the excel file is located. If there is no folder, It creates a one with that. If folder exist it tells that folder is exists.

If Dir(ThisWorkbook.Path & "/Image Files/", vbDirectory) = "" Then
    MkDir ThisWorkbook.Path & "/Image Files/"
 
Else
    MsgBox "Directory Exists", vbInformation, ""
End If


Excel 2013 Pivot tables

                               Today I'm going to teach you how to create pivot tables in Excel 2013. Pivot tables are powerful features of Microsoft Excel. They were continually developed in recent versions. First I will explain you what are the situations you need pivot tables. We normally use pivot tables for data related to transactions. There is no necessarily to have some kind of dependency among these items. But they are in similar measurements and have certain properties in common.

                                The case study I'm going to looking at is some what straight forward. It is a record of a company which has few branches Island wide. This data is a record of individual data of sales for nearly ten months of time. So my workbook contain thousands of data. Below you can see the part of my data set.


                           
                            Let's take row 3 as an example. In that row first value (In cell A3) is the date of the transaction. Next one is the branch where transaction took place. Third one is the value. And fourth one is the category of the sale. So here it is Food. It is not compulsory to data to be always sales to create pivot tables, Only it needs to be something measurable.

                            If you have very large amount of data, it is very difficult to analyze and present them. Consider above data set. You can analyze them in several ways. You can plot the total sales against the time. Also you can use different time frames like days, weeks and months. Also you can plot data of each and every branch against the time to see the growth of the sales in individual branches. So you can see that we can plot these data in various ways to suit to our requirement. We can  compare branches each other or we can compare categories each other. So you can see that we can analyze these data in many ways by changing the axes of charts time to time. To do that we need pivot tables and charts. I will explain about pivot charts in another post.

                           You need to know few things about Excel pivot tables before proceed further. One important thing is if you have created pivot tables with Excel 2003, please not that they are not compatible with higher versions of Excel. So it is advisable to find raw data you use to create those pivot tables in Excel 2003 and use them to create new pivot tables in Excel 2013. However newer versions of Excel pivot tables have higher level of compatibility. If you have created your pivot tables earlier with Excel 2007 or Excel 2010 you can use them in Excel 2013 without any problem. Also you can use external data to create pivot tables. So it is not compulsory to have data in the same work book where the pivot tables are. An another important thing. You need to have all the transactions in rows. You can't have the transactions in columns. If your transactions are in columns you need to somehow transpose them to rows as shown in above data set. Also it is advisable to have one transaction in one row. There should not be any blank columns or blank rows. And each column should have a unique title. And if your data set contain numeric values don't leave empty cells. Put zero instead. Check whether your data set fulfills all those requirements before start creating a pivot tables and pivot charts.

                           My data set fulfills all the requirements. So now let's start creating our pivot table. In Excel 2013 there are two method to create a pivot tables. I will explain both these methods. First we will look at quick method. Click in a cell inside the data range you use to create the pivot table. Then press Ctrl+asterisk(*). So you will see that all data is selected automatically.



                       Once all the data is selected you will see an icon call Quick Analysis icon. (Please see the bottom right hand corner of the above image.) You can use this icon to quickly analyse your data. Just click on that icon. You will get a menu bar like below.


                         It contains several menus like Formatting, Charts, Totals, Tables and Spark lines. So let's click on the Tables menu.


                          Under that menu you can find Table, two pivot table options and More option. If you take cursor on top of each icon it will show you a live preview. So I will click on first pivot table option. With that click Excel 2013 will create a new sheet like below.



So now you can see that the pivot table is created in the left hand side of the new sheet. This is a quick way of a creating a pivot table. In that method we created the pivot table for a data range. But when you create a pivot table it is better to create it on a table rather than a range of data. Because when time goes you may need to add new data to the data set. So if you have used a table it is much easier to update your pivot table later on. In this second method I will show you how you can create a pivot table after converting your data set to a table.
First click in a cell within the data range. Then go to "Insert" menu and click on "Table".


Once click on the "Table" you will get the "Create Table" dialog box. Excel also suggest a boundary for your data as well.


You can change that boundary to suit to your requirement. Define boundary of your table and click OK. With that click the table will be created and it will be selected. In this point simply give your table a name. In below image I have marked the place with a red circle where you can specify the name of your table.


So I just name it as SalesData. Next click inside your table and go to "Insert" menu and then click on "Pivot table"


Once you click on the pivot table you will get the "Create pivot table" dialog box


Here you can see that my table has automatically selected. It is because I have click inside my table before click pivot table. Also here you can decide where you need your pivot table to be created. Either in the sheet where your raw data are or in a new sheet. After making your selections click on the OK button.To give much space to data analyzing area, I choose new worksheet option.


Above is the result I got. As you can see this method is little bit different to the quick pivot table creation method. Because in that method we got pivot table right away. In this method we have got a some kind of place holder for our pivot table. Actual pivot table will be created once we select the fields from the right side panel.  The bottom part of the right panel has separated to four areas. Filters, Columns, Rows and Values. Here you will notice that when you select text field or date field they will automatically listed under Rows area. And if you select a field which has numeric values it will automatically listed in the Value area. So now I will select Branch field and value field.


So now let's look at the Pivot table we have created. There are branches in the column A and Sum of values in column B. We can control the pivot table by the panel shown in the left hand side. You can see in that panel only check boxes of Branch and Value are checked. As a result only those two fields are shown in the pivot table. So let's click on the check box of the category field and see what will happen.



As you can see, now my pivot table has expanded. Now it shows both the Branch and Category. So in Rows area the category label is placed under Branch label. According to that, in the pivot table categories are listed under each branch. Think you need to list branches under each category. Then you can see how sales are varied for different branches for different categories. You can simply do this. what you need to do is drag Category label at the Rows area to top of the Branch label of same area. Here is the result you will get.








Now all the branches are listed under each category.So now let's move the category label to columns area and see what will happen.







Now it has also break down by categories as well. So each column represent a unique category. In addition to that you can filter column labels as you like. Not only that you can even pivot two fields. So let's interchange Branch and Category in the bottom part of the right panel and see what will happen.



So you can see how the Branches and Categories swapped with that action. This flexibility of the Excel pivot tables is a key reason to it's popularity.

Download a file from url using VBA

Sometimes our VB Applications needs to interact with websites. Downloading a file through a url is a typical example.

Here below is a code which you can use to download a file through a url. You should replace "Put your download link here" with your url. Also remember to put it inside double quotes.

Dim myURL As String
myURL = "Put your download link here"

Dim HttpReq As Object
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
HttpReq.Open "GET", myURL, False, "username", "password"
HttpReq.send

myURL = HttpReq.responseBody
If HttpReq.Status = 200 Then
    Set oStrm = CreateObject("ADODB.Stream")
    oStrm.Open
    oStrm.Type = 1
    oStrm.Write HttpReq.responseBody
    oStrm.SaveToFile ThisWorkbook.Path & "\" & "file.csv", 2 ' 1 = no overwrite, 2 = overwrite
    oStrm.Close
End If

Also you should be careful with below line.

 oStrm.SaveToFile ThisWorkbook.Path & "\" & "file.csv", 2 ' 1 = no overwrite, 2 = overwrite

In this case I have chosen the option to overwrite existing file. If you don't need to overwrite existing file please put the number accordingly.

How to add @ or = sign

            Hope you already know that you can enter any formula to excel cell by entering formula with equal (=) sign. This may have helped you to automate your spread sheets very easily.

But have you ever needed to put = sign or @ sign to a excel cell as a first character. (Not to enter a formula) .
If you enter equal sign first and then if you enter some thing which is not a formula then you will get an error message like below.

 #NAME?

And if you enter @ sign first then you will get following error message.





So I will show you how to enter something which is not a formula to a cell with = sign first. Or how to enter @ sign first. Solution is easy. You just need to put apostrophe ( ' ) before the = or @.
Below image will show how it works. You can see @Head at cell. And in formula bar you can see how it has entered using apostrophe.





Web Scraping - Basics

Web sites contains mass amount of data. Some times people need to extract those information for their needs. Web scraping is a popular technique use to collect data automatically from websites. Web scraping is also called as Web harvesting.

There are lot of uses of web scraping. It is used

  • To compare prices
  • To monitor weather data
  • To detect changes made to websites
  • For research purposes
  • To collect contact details

Complexity of the web scraping program depends on the web site and the data we going to collect. If we develop a web scraping software from VBA we need to use several in-built functions to make our program easier. Here below are very important functions we will need.

Functions helpful in web scraping

Mid - This function can be used to extract a string segment from it's parent string.
InStr - This function can be used to check whether certain string is included in another string and get the position of that sub string if it exists.
Replace - This function replace a character or string segments with another character or string.

Create an Internet Explorer object and navigate to a webpage

First, let's see how to create an internet explorer object. You can use below line of code to create that object.

Set objIE = CreateObject("InternetExplorer.Application")

Then set following attributes to the created object. You can change them according to your requirement. First to attributions decide the position of internet explorer window. Next to attributes will decide the size of the window.

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

Following attribute enable the viewing of window navigation. However you can set it to false if you do not want to see it.

objIE.Visible = True 

So now we have created the internet explorer object and set the attributes. Next let's see how to navigate to a website. You can use below line to navigate to the website you want.

objIE.Navigate ("http://www.example.com/")

There is a very important thing you need to know when developing a web scraping software. Your program will have to run through several line of codes after navigating to a specific website. However website can take some time to be fully loaded due to various reasons. So when you run the program there is a chance of running the rest of the code before web site is fully loaded. This can harmfully affected to the results you get from the software. However we can take preventive action to overcome this kind of situations. For that you need to always put below three lines where ever you have navigation command.

Do
DoEvents
Loop Until objIE.readystate = 4

Following is the full code you can use to Create an Internet Explorer object and navigate to a webpage.

Set objIE = CreateObject("InternetExplorer.Application")

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

objIE.Visible = True

objIE.Navigate ("http://www.example.com/")

Do
DoEvents
Loop Until objIE.readystate = 4

There is an another reason which can cause our program to behave differently to the way we have intended. There are times web sites not available due to different reasons. If our program try web harvest with a web site which is not online it will halt our program. So we need to use error handling techniques wisely to overcome this kind of scenarios.

Here below is a example of using error handling in VBA web scraping.


the_start:

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600
objIE.Visible = True 'We will see the window navigation

On Error Resume Next
objIE.navigate ("http://www.example.com/")

Do
DoEvents
    If Err.Number <> 0 Then
        objIE.Quit
        Set objIE = Nothing
        GoTo the_start:
    End If
Loop Until objIE.readystate = 4

MsgBox "webpage has loaded"

Often we need to get the html code to a text box or to a variable, in first steps of the web scraping program. So here below is a code to paste html code of a specific web page to a text box. Here txtHtml refers to a text box name.

txtHtml.Text = objIE.document.body.innerhtml

Some times it is very easier to get required data from the inner-text without dealing with html codes. So we can use below line of code to copy paste inner-text to a text box in that kind of situation.

txtHtml.Text = objIE.document.body.innertext

In web scraping, frequently we need to get elements by their tag names. Here below is an example which can get elements have the "a" tags (anchor)

Set Alllinks = objIE.document.getelementsbytagname("A")
    For Each Hyperlink In Alllinks
        MsgBox Hyperlink.innertext & "-" & Hyperlink.href
    Next

Often web scraping software need to click links inside it's procedures. Below example will click the link if it find a link with the text "New Arrivals".

Set Alllinks = objIE.document.getelementsbytagname("A")
    For Each Hyperlink In Alllinks
        If InStr(Hyperlink.innertext, "New Arrivals") > 0 Then
            Hyperlink.Click
            Exit For
        End If
    Next

Above are few basic things you need to know if you like to develop a software for Web scraping using VBA. Read these posts if you want to learn more.

Useful References for VBA Web Scraping
Web Scraping Techniques - Part 2
Web Scraping - Collecting Data From a Webpage
Web Scraping - Collect Options Inside Drop-down Lists
VBA Web Scraping - Identifying Disabled Dropdowns
How to scroll a web page number of pixels

Open and read text file

                         We need to handle text files in various situations. Sometimes we need to create, open, write  or delete text files while the program is running. So today I will explain you how to Open and read text file through VBA.
First of all we need to define the variables.

Dim myFile, text, textline as String

Variable "myFile" is used to store the file path of the text file. Each and every text lines will be stored temporarily to the variable call "textline" while program run throgh the Do loop until it reaches to the end of the file. All the text lines of the text file will be appended to variable "text".

There are two ways to assign the text file to "myFile" variable depending on your requirement.

Either you can use below line of code to assign the file path directly. This method can be used if you always use same folder path and file.

myFile = "D:\Fiverr\amjedamjed\Project 2\all - Copy (11).txt"

Or you can use below line of code to assign the file name to variable. Here user get opportunity to select the file through browsing window.

myFile = Application.GetOpenFilename()

So you should use one of the above two lines in your program according to end user requirement. However rest of the program will not affected by what you chooses from above two ways.  
Below line will open the file for reading

Open myFile For Input As #1

Below do loop will run until it reaches to the end of the text file. Inside the do loop, program read the text file line by line and assign that single line to variable "textline". Whole text of text file is appended to variable call "text" by the next line.
Do Until EOF(1)

    Line Input #1, textline
    text = text & textline
Loop

Below line of code will close the text file which was opened for reading.
Close #1


Now you have whole content of text file in variable "text". Now you can do any string manipulation to that text from here.
However I will just show the content in a message box.

MsgBox text





Here below is the full code for you to easily copy paste for your project

Dim myFile, text, textline as String

myFile = "D:\Fiverr\amjedamjed\Project 2\all - Copy (11).txt"
or
myFile = Application.GetOpenFilename()

Open myFile For Input As #1
Do Until EOF(1)
    Line Input #1, textline
    text = text & textline
Loop
Close #1
MsgBox text

VBA - Error 400

Have you ever got a error similar to shown below. If you are a VBA developer you might have got this error when you run or debug your program.
This error message contains only 400 and OK button.



I saw lot of people have asked about this error in lot forums. This error made developers very frustrated because it doesn't show any error message or anything. It only pop up a message box with "400" and OK button.

Soon after you press the OK button, it will kill the process.

However in microsoft website I found information about similar error. I saw following line in that website.

400 Form already displayed; can't show modally

So here what has happened is program tried to display the form again while it is already displayed. May be it is hidden by a sheet or another form.





But in our case error message is different. Only similarity is both the error messages have "400" in common. However after doing some experiments I was able to find one reason for "error 400". Think your program access files in your hard drives while it runs. So this error can occur if you have given non existent path to the program. So program try to access a file. But it is not existing. This will cause error 400. Then even missing of a one letter in the file path can throw this error. Lot of people says it takes much more time to identify a small misspelling than finding a complicated one. 

Open files in a specific directory (folder)

Some times VBA programs needs to access files from a specific folder. Assume you have a folder call "Test" within your drive "D".







Following code will open all the files inside that folder. You can easily modify this code for your requirement.



Dim file As Variant
Dim pathAndFileName  as String
file = Dir("D:\Test\")
While (file <> "")
    pathAndFileName = "D:\Test\" & file
    Application.Workbooks.Open (pathAndFileName)
    file = Dir
Wend


Here is the explanation of how this code works.

First we need to define our variables. Variable "file" is used to get individual file names from the folder. Variable "pathAndFileName" is used to hold the file path and file name.

Dim file As Variant
Dim pathAndFileName  as String

Next we assign a our first value to the variable we have defined.

file = Dir("D:\Test\")

So now the name of firsts file inside the folder "Test" is assigned to the variable "file". In this example we use while wend loop to open all the files inside that folder. We use variable "pathAndFileName" to hold the file path and file name. 

 pathAndFileName = "D:\Test\" & file

Next we use  Application.Workbooks.Open method to open the file.



Application.Workbooks.Open (pathAndFileName)

So now we have opened the first file in that directory. We need to repeat the process for all the other files. But before going to next cycle we need to clear the attributes of our variable "file". We use following line of code to clear them

file = Dir

That is the full explanation of the code.

Finding a specific file from a folder

Some times we need to check whether specific file is contained in a folder. Below example checks whether file "book1.xlsx" is contained in a folder call "Test" which is located in "D" drive. Then open the file if it is exists.


Dim file As Variant
Dim FName, pathAndFileName As String
FName = "book1.xlsx"

file = Dir("D:\Test\")
While (file <> "")
    If StrComp(file, FName) = 0 Then
        pathAndFileName = "D:\Test\" & file
        Application.Workbooks.Open (pathAndFileName)
        MsgBox "found " & file
    End If
file = Dir
Wend

Here is the explanation of how this code works.

First we need to define our variables. Variable "file" is used to get individual file names from the folder. Variable "pathAndFileName" is used to hold the file path and file name. Variable FName holds the file name we want to check.

Dim file As Variant
Dim FName, pathAndFileName  As String
FName = "book1.xlsx"

Next we assign the first file of the folder to the variable we have defined.

file = Dir("D:\Test\")

So now the name of firsts file inside the folder "Test" is assigned to the variable "file". In this example we use while wend loop to compare all the files inside that folder with file name we want. For the comparison we use below line of code

 If StrComp(file, FName) = 0 Then

If the file names matched then the codes inside the if statement will be executed.
We use variable "pathAndFileName" to hold the file path and file name. 

 pathAndFileName = "D:\Test\" & file

Next we use  Application.Workbooks.Open method to open the file.

Application.Workbooks.Open (pathAndFileName)

Then program will give the message that it has found the file we want.



 MsgBox "found " & file

There is important thing we need to do when loop through the program. It is that we need to clear the attributes of our variable "file"  before going to next cycle. We use following line of code to clear them

file = Dir

Sort Function

                   Today we will discuss about using Sort function through VBA. So assume you have below kind of excel sheet. You can notice that values in column A under the title "NO" are not in and order. So what we going to do is sort the data according to the values in column  A.




Just go to the Data tab of excel application. You will see below icon in that tab.




                   If you click that icon, it will pop-up a table similar to below. From that table you can choose based on what column you going to sort the data. Based on what, you going to sort the data. And also you can select the order as well.




                      After selecting the parameters accordingly, you can click the OK. Button. Then worksheet will end up with result similar to shown in below image.




So now we have come to the important part. What we are going to do is complete the above process through VBA. You can use below code for that. This sample code will sort data from cells A2 to E13 based on values in the 1st column.

Dim WS As Worksheet
Set WS = ActiveSheet

WS.Range("A3:E13").Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    ("A3:A13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range("A3:E13")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Also you can use this subroutine to sort data in ascending order. This subroutine take data range, sort fields as parameters. So you can readily use this inside your projects as separate sub routine.

Sub SortAscending(WS As Worksheet, FirstColumn As String, LastColumn As String, KeyColumn As String, _
FirstRow As Long, LastRow As Long)

WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

And this is the subroutine for sort descending

Sub SortDescending(WS As Worksheet, FirstColumn As String, LastColumn As String, KeyColumn As String, _
FirstRow As Long, LastRow As Long)

WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Sometimes we need to add two or more levels when we sorting the data. This is how we do it manually.



Following is a subroutine which can use for two levels. This will sort both levels in ascending order.

Sub SortAscendingTwoLevels(WS As Worksheet, FirstColumn As String, LastColumn As String, _
KeyColumn As String, SecondKeyColumn As String, FirstRow As Long, LastRow As Long)

WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
WS.Sort.SortFields.Add Key:=WS.Range _
    (SecondKeyColumn & FirstRow & ":" & SecondKeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub




If you want to sort both levels in descending order, then you can use below subroutine.

Sub SortDescendingTwoLevels(WS As Worksheet, FirstColumn As String, LastColumn As String, _
KeyColumn As String, SecondKeyColumn As String, FirstRow As Long, LastRow As Long)

WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
WS.Sort.SortFields.Add Key:=WS.Range _
    (SecondKeyColumn & FirstRow & ":" & SecondKeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Date format issue (How to solve)

     
                  In this lesson I'm going to teach you how to overcome formatting errors when you input data to excel sheet from a text box, combo box etc.

                  Here is a my own experience. Once I had a date value 01/08/2014 in one of text box of a user form.I got that value to the text box from a date picker. So it was in the format dd/mm/yyyy.
So the above date refers to 1st of August 2014.

                  However when data entered to the excel sheet it took the form of 08/01/2014
So now it refers to 8th of January 2014
    
                  If you face this kind of situation here below is the solution.Just format the value using Format function before input data to the sheet.

Here txtDueDate is the name of the text box. Below example enters date value to cell E3 of worksheet("Data")



    Dim WS as worksheet
    set WS=worksheets("Data")

    Dim DDate As Date

    DDate = Format(txtDueDate.Text, "dd/mm/yyyy")
    WS.Range("E3").Value = DDate

Find function

Think you have a spread sheet similar to this. It contains value "100" in several cells.

So today I will teach you how to get the address of those cells from VBA code

First let's define the value we need to search.

Dim s1 As String

Then assign the value we search to that variable.

s1 = "100"

And it is a good practice to use objects where you can. It prevent occurrence of errors. Think you have worksheet call "Report". If you use conventional way you need to refer that sheet by worksheets("Report") at every where you needed to refer it. But if you use worksheet object you can refer it from very short name. So this will give neat look to your coding as well. So I prefer using objects. So use worksheet objects as follows.

Dim WS As Worksheet
Set WS = Worksheets("sheet1")

Then Set the range we need to search.

With WS.Range("A1:T23")

Following line of code will give you the address of each cell having 100 in it.

MsgBox c.Address

Loop will continue until it find a value for c except first value.

Loop While Not c Is Nothing And c.Address <> firstAddress

So here is the full code. You can use this wherever you need by changing the sheet names, range and search values accordingly. Also you can pass the search value as a variable.

Dim s1 As String
s1 = "100"
Dim WS As Worksheet
Set WS = Worksheets("sheet1")
With WS.Range("A1:T23")
    Set c = .Find(s1)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            MsgBox c.Address
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

With the above code you will get the cell address of cells having "100" to msgboxes.

Populate combo box with sheet name of opened work book

Here cboFromSheeet is the name of the combo box.

Dim wbFrom As Workbook
Set wbFrom = Application.Workbooks.Open(lblFromFilePath.Caption)
Dim ws As Worksheet

'Clear the combo box
cboFromSheeet.Clear

With cboFromSheeet
    For Each ws In Worksheets
            .AddItem ws.Name
    Next ws
End With

cboFromSheeet.Text = "Select a Sheet"

Add work sheet with a name

Think you want to add a worksheet with a name "Report"

This code will do it.

Sheets.Add.Name = "Report"

However you can do this even with objects

See following code

Dim WS as Worksheet
Set WS = Sheets.Add

You can refer this new sheet by WS wherever at the code. You don't need to know anything about like it's name, location etc. Even you can give it a name later.

For an example think you want to name it as "Calculation". So below code will do it.

WS.Name = "Calculation"

Find last row having data in a certain column

    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

reference workbooks and sheets Explicitly

This example shows how to reference workbooks and sheets explicitly.

Dim wbFrom As Workbook
Dim wbTo As Workbook
Dim wsFrom As Worksheet
Dim wsTo As Worksheet
Dim Str1, Str2, s1, s2, s3, s4 As String
'paths of 2 workbooks
Str1 = lblFromFilePath.Caption
Str2 = lblToFilePath.Caption
Dim WrdArray() As String
WrdArray() = Split(Str1, "\")
's1 and s2 are Workbook names
s1 = WrdArray(UBound(WrdArray()))
WrdArray() = Split(Str2, "\")
s2 = WrdArray(UBound(WrdArray()))
'Worksheet names
s3 = cboFromSheeet.Value
s4 = cboToSheet.Value

Set wbFrom = Workbooks(s1)
Set wbTo = Workbooks(s2)
Set wsFrom = wbFrom.Worksheets(s3)
Set wsTo = wbTo.Worksheets(s4)

If cboFromSheeet.Text <> "Select a Sheet" And cboToSheet.Text <> "Select a Sheet" Then
    MsgBox wsFrom.Name, , "from"
    MsgBox wsTo.Name, , "To"
Else
    MsgBox "Please select relevant sheets from the drop down menu.", vbOKOnly, "WARNING!"
End If

Browse file and put path+name and name in two labels

Dim strFile As String
strFile = Application.GetOpenFilename("Excel workbooks,*.xls*")
If strFile = "False" Then
    ' if the user clicked Cancel
    'Here nothing will happen
Else
    'If the user selected a file; both its path and name is in strFile
    'So we store it in lable caption. you can set visibility of lable to false
    lblFromFilePath.Caption = strFile
    Dim WrdArray() As String
    WrdArray() = Split(strFile, "\")
    'here it will show the file name
    lblFromFile.Caption = WrdArray(UBound(WrdArray()))
End If

You can use below line to browse any type of file.

strFile = Application.GetOpenFilename()

Call each and every sheet of a workbook

Dim ws As Worksheet
For Each ws In Worksheets
    ws.Protect Password:="data123"
Next ws

Open a closed work book

Application.Workbooks.Open ("E:\Blogger Folder\My Files\Folder1\Calculations.xls")

Save excel file as CSV file

ActiveWorkbook.SaveAs Filename:= _
"c:\MyFile.csv", FileFormat:=xlCSV _
, CreateBackup:=False

Replace string inside a string by another string

Dim s1,s2,s3 As String
s1 = "abcabcabc"
s2 = Replace(s1, "a", "b")
s3 = Replace(s1, "bc", "a")

You will get below answers

s2= bbcbbcbbc
s3= aaaaaa

Dealing with word documents

Open excel work book.

press Alt+F11

tools > References >

put tick on "Microsoft word 15.0 object library"

Write below code

    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Set wApp = CreateObject("Word.Application")
    wApp.Visible = True

'Here only the word application will be opened.

if you put following line next a new word document will be created.

Set wDoc = wApp.Documents.Add

If you want to open a document in your hard drive ( (Location -D:\VBA LAB\Result.docx) ) you need to put following code

  Set wDoc = wApp.Documents.Open("D:\VBA LAB\Result.docx")

Get path of a workbook

Following code will show the path of the active work book in a message box

MsgBox ActiveWorkbook.Path

Clear the value of a text box when click on it

Some times we need to clear the text box when click on top of it.

Ex - Enter date to a text box which displays "Date" on it.

So you have to put the code to MouseUp event of the text box. Below is the code for a date text box

Private Sub txtBDate_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If txtBDate.Text = "Date" Then
    txtBDate.Text = ""
End If

End Sub

Add data set to next empty row of a excel database

Private Sub cmdEnter_Click()

Dim WS As Worksheet
Set WS = Worksheets("Data")

'find the end of column A
Dim x As Integer
x = 1
Do Until WS.Range("A" & x).Value = ""
     x = x + 1
Loop
'last row having data = x-1

'Add data to database
        WS.Range("A" & x).Value = txtJN.Value
        WS.Range("B" & x).Value = txtCus.Value
        WS.Range("C" & x).Value = txtSup.Value
        WS.Range("D" & x).Value = txtPO.Value
        WS.Range("E" & x).Value = txtDD.Value

        ActiveWorkbook.Save

        MsgBox "Data Saved Successfully"


'Clear text and combo boxes

txtJN.Value = ""
txtCus.Value = ""
txtSup.Value = ""
txtPO.Value = ""
txtDD.Value = ""

End Sub

Add names of all open workbooks to a combo box list

Here cboCsvFileName is the name of the combo box

Dim wb As Workbook

For Each wb In Application.Workbooks
    With cboCsvFileName
        .AddItem wb.Name
    End With
Next wb

Function to returns column name from col number

This function will return column name if you input column number.

for example if you want to get column name of 100 th column

you can use ReturnColumnName(100)

Answer will be CV

Function ReturnColumnName(ByVal number As Integer) As String
    ReturnColumnName = Split(Cells(, number).Address, "$")(1)
End Function

How to get a discription of an error

On Error Resume Next
-------------------------------
Your code goes here.( ex: Mail.Send)
-------------------------------
If Err.Number <> 0 Then
    MsgBox Err.Description, vbCritical, "There was an error"
    Exit Sub
End If

Delete a row, rows, column or columns

Below code will delete the 6 th row from the activesheet

Rows(6).Delete

This will delete the 3 rd column (Column "C")

Columns(3).Delete

Cell address of a cell having a specific data

MsgBox Cells.Find(What:="Deposit", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Address

Open save as dialog box with a file name

Some times we need to show save as dialog box for the user. Some times we need to show it with a  file name. Below is a code for that.

Dim IntialName As String
Dim fileSaveName As Variant
InitialName = "Result"
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
     fileFilter:="CSV (Comma delimited) (*.csv), *.csv")

Populating 3 combo boxes

Populating 3 combo boxes  cboYBrand, cboYName and cboColor from values from rows 2 to 10 of columns A,B and C

'Populating cboYBrand, cboYName and cboColor
If Worksheets("Database").Range("A2").Value <> "" Then 'if there is atleast one data row
        For i = 2 To 10
            With cboYBrand
                .AddItem Worksheets("Database").Range("A" & i).Value
            End With
            With cboYName
                .AddItem Worksheets("Database").Range("B" & i).Value
            End With
            With cboColor
                .AddItem Worksheets("Database").Range("C" & i).Value
            End With
        Next i
End If

Contact Form

Name

Email *

Message *

Popular Posts