Application Object

Properties in Application Object

Application objects allow the user to access and use many properties of excel like status bar, vlookup, reading data from the text file, writing data in the text file, and much more. Here we will learn about some of those properties.

StatusBar

This is one of the many properties of excel. It can be used to show the status of the lengthy macro, and in this way, it can help the user know the proper time for its macro execution.

Situation

We will write code to fill the range ("A1:E20") with random numbers.

Follow the below procedure to write the code for the above situation.

• We will declare three variables of type integer named j, i, and pctCompl.

Dim i As Integer, j As Integer, pctCompl As Integer

• Add two nested loops.

For i = 1 To 20
    For j = 1 To 5

 
    Next j
Next i

• We will use the rand function to fill the random values in the cell. So add the below line in between the loops.

Cells(i, j).Value = WorksheetFunction.RandBetween(20, 100)

• Initialize pctCompl and in the second line, we will write the value in the status bar.

pctCompl = (i - 1) * 5 + (j * 1)
Application.StatusBar = "Importing Data.. " & pctCompl & "% Completed"

• We will use the wait method as shown below:

Application.Wait Now + TimeValue("00:00:01")

• And to restore the default status bar, add the following line of code:

Application.StatusBar = False

When you click the Import button, the result will be as follows:

You can see in the bottom left corner the status has been shown.

Read Data From Text File

In this blog section, we will write the code to read data from a text file on your computer into excel. The file below contains some geographical coordinates now. We will import this data into our excel file.

Situation

Follow the steps given below to import the data:

• First, downloads the file and save it accordingly. Now add a command button on your worksheet and add the following code.
• Next, we will declare four variables text of type string, myFile of type String, posLong of type integer, and posLat of type integer.

Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer

• We need to initialize the variable with the full file name and its full path.

myFile = "C:\test\geographical_loaction.txt"

• Add the following lines of code :

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

• Close the file by the following instruction.

Close #1

• Now we will search for latitudes and longitudes and store their values and use those stored values to show in our excel sheet by using mid function.

posLat = InStr(text, "latitude")
posLong = InStr(text, "longitude")
Range("A1").Value = Mid(text, posLat + 10, 5)
Range("A2").Value = Mid(text, posLong + 11, 5)

Now, click on the command button the result will be as follows:

Write Data to Text File

Here we will learn how to write data from an excel file into a text file.

Situation

Here is the excel file with its content.

Follow the steps given below to make the program do the above function:

• Make a command button and add these code lines to it. Declare the following variables.

Dim myFile As String, rng As a range, cellValue As Variant, i As Integer, j As Integer

• Now specify the full path with the file name.

myFile = Application.DefaultFilePath & "\sales_file.csv"

• Initialize the range object and add the following code line:

Set rng = Selection
Open myFile For Output As #1

• Now start a double loop and add the following lines in it so that we can read the value from cells:

For i = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then
    Write #1, cellValue
Else
    Write #1, cellValue,
End If
  Next j
Next i

• Close the file using the following line of code:

Close #1

Now check the result:

Vlookup

To access the Vlookup property in excel, we must use the WorksheetFunction property:

Situation

Follow the steps given below to execute the Vlookup function.

• Insert a command button on your worksheet and add the following code to it.

Range("H3").Value = WorksheetFunction.VLookup(Range("H2"), Range("B3:E9"), 4, False)

When you click on the command button, the salary of 56 will be shown in cell H3.

• Now suppose you are looking for a value that doesn't exist in your excel file, like 26:

The result will be like this:

• To replace this error, update the code with the following code.

On Error GoTo InvalidValue:

Range("H3").Value = WorksheetFunction.VLookup(Range("H2"), Range("B3:E9"), 4, False)

Exit Sub

InvalidValue: Range("H3").Value = "Not Found"

Now the result will be this:

FAQs

1. Can you lock the cell so that only some specific allowed user can perform the operations?
   You can lock cells for a specific user by using the Allow users to edit ranges option".
    
2. How can a user clear the format in range?
   The user can clear the format in range by using the ClearFormates method.
    
3. What is the difference between the used and current range?
   The current range is the range surrounded by the used range, whereas the entire range in the worksheet used by the user is known as the used range.
    
4. Can we write macro in the code window?
   Yes, we can write the macro in the code window.

Key Takeaways

In this article, we have extensively discussed application objects in excel with its different options like worksheetFunction, screenupdating, displayalerts, and calculation. Along with options, we have also discussed other properties associated with application objects like vlookup, reading data from text files, writing data in a text file, and status bar, all with suitable examples.

We hope that this blog has helped you enhance your knowledge of excel. If you are interested in learning about ActiveX controls, you must refer to this blog. Here, we have discussed  Different ActiveX Controls like a list box, combo box, scroll bar, option buttons, command buttons, and many more from scratch and also learned how to control their working with writing code in VBA. If you would like to learn more, check out our articles on Code studio. Do upvote our blog to help other ninjas grow.

 “Happy Coding!”