Excel-VBA Solutions

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.

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
Subscribe to: Posts (Atom)

Contact Form

Name

Email *

Message *