Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Options in Application Object
2.1.
WorksheetFunction
2.2.
ScreenUpdating
2.3.
DisplayAlerts
2.4.
Calculation
3.
Properties in Application Object
3.1.
StatusBar
3.1.1.
Situation
3.2.
Read Data From Text File
3.2.1.
Situation
3.3.
Write Data to Text File
3.3.1.
Situation
3.4.
Vlookup
3.4.1.
Situation
4.
FAQs
5.
Key Takeaways
Last Updated: Mar 27, 2024

Application Object

Author Naman Kukreja
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Nowadays, excel is the essential requirement asked by almost all companies, whether they are dealing with data or have subsidiaries to deal with data. They expect each employee to know how to work with Excel in the brute force way, and the optimized way that saves time.

We can perform different operations in excel, and there are multiple ways to achieve them. Application object in excel provides or enable users to use other properties, functions, and procedures in excel that makes their work a lot easier and time-efficient.

We will learn all about application objects while proceeding with this blog, so let's get on with our topic without wasting more time.

Options in Application Object

Excel is often referred to as the mother of all objects, and we call it an application object. It provides us access to various operations in excel. We will learn about some of them in this part of the blog.

WorksheetFunction

Users can access the Excel functions by using WorksheetFunction. We will learn more about it by the following example.

  • Insert a command button on your worksheet and add the following code to it.
Range("A5").Value = Application.WorksheetFunction.Average(Range("A1:A4"))

When the user clicks on the command button, then Excel VBA calculates the average of all the values from cell A1 to cell A4 and stores the result in the cell A% like, shown in the image below:

ScreenUpdating

Sometimes you become irritated with screen updating due to flickering, so in times like these ScreenUpdating function is great to use. We will learn how to use these functions by the example given below:

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

 
For i = 1 To 10000
    Range("A1").Value = i
Next i

Now, when the user clicks on the command button then, it will display all the values for a fraction of a second, as shown in the image below:

  • You can speed up the process by updating the previous code with this code:
Dim I As Integer

 
Application.ScreenUpdating = False

 
For i = 1 To 10000
    Range("A1").Value = i
Next i

 
Application.ScreenUpdating = True

This will increase the execution speed of your code, and as a result, you will directly see 10000.

DisplayAlerts

Sometimes displaying alerts becomes very necessary to save major errors in the future. You can achieve this by using the DisplayAerts function. You will learn how to use this function by the example given below:

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

Now when the user clicks on the command button, Excel asks you to save the changes you made or not, as given below:

  • This alert becomes irritating when you are executing the code, and to solve this, update the code with the following code:
Application.DisplayAlerts = False

 
ActiveWorkbook.Close

 
Application.DisplayAlerts = True

Calculation

The calculation is set to automatic by default. As a consequence, each time a value influencing a formula changes, Excel recalculates the file automatically. You may speed up your macro by setting calculation to manual if your worksheet has a lot of complex formulae.

  • Insert a command button on your worksheet and add the following code to it.
Application.Calculation = xlCalculationManual
  • You can verify this by yourself by clicking on File, Options, Formulas shown below:

  • Now when you calculate the value of one cell, then the value of the other cell corresponding to it will not be changed, as shown in the image below:

  • You may have to set the calculation to automatic at the end of your code, so to do so, add the following line at the end of your code:
Application.Calculation = xlCalculationAutomatic
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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!”

Previous article
Functions and Sub in Excel
Next article
ActiveX Controls
Live masterclass