Trending December 2023 # Example Of Getobject Function In Excel Vba # Suggested January 2024 # Top 14 Popular

You are reading the article Example Of Getobject Function In Excel Vba updated in December 2023 on the website Cancandonuts.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Example Of Getobject Function In Excel Vba

Excel VBA GetObject

VBA GetObject, as the name, seems like we need to create a code for getting or creating an object. But in fact, it is the process of getting the Tables from any word to excel file. In VBA GetObject, we fetch the data from a word file by putting the location where it is kept and append any number of tables from that Word into the Excel sheet.

Syntax of VBA GetObject

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax is explained as shown below:

PathName = Here we will be giving the path to the Word document where it is kept. This field is optional.

Class = Here, we need to define the Class of Object function. This one is too optional. But if we are not defining the PathName in the syntax then Class will be must to define.

Both the arguments of the syntax are optional. But anyone of them should be defined.

Example of GetObject Function in Excel VBA

We will be seeing, how to fetch the data which is in the form of Table in a word document and append that data in Excel worksheet.

You can download this VBA GetObject Excel Template here – VBA GetObject Excel Template

For this, we require such data in word file. Here, we are having a word file below, which has 2 tables of Employee name and Employee ID.

We have saved this file somewhere in our local drive which is easy to access. Follow the below steps to use GetObject function in Excel VBA.

Step 1: Go to VBA window and open a Module from the Insert menu tab as shown below.

Step 2: In the newly opened Module, write the subcategory of VBA GetObject or you can choose any other name as per need.

Step 3: First, define the 2 Object variable to access the Object prepared by VBA GetObject.

Step 4: We would need another variable for which we will store the location of File as String.

Step 5: To avoid any break if an error occurs we will keep resuming the next step.

Step 6: Now we would use GetObject function and set it as WordFile object. Keeping Path blank, we will define the Class as Word.Application where Word is the application of Microsoft.

Step 7: To avoid error number 429 which usually occurs in this case, we will clear it as it happens.

Step 8: Now make the created Object variable WordFile visible.

Step 9: As we haven’t defined the path, so we will assign file location to StrDoc along with the extension.

Step 10: If we did not find anything in the word file, then we should be getting a message prompting that “No Data Available” or “Document Not Found”. And this would be done in If-End If loop.

Step 11: Now activate the Word file.

Step 12: Do the same procedure for setting WordDoc as well. If WordDoc is nothing then we will open the file from the location where it is kept.

Step 13: Now we will need to define the variables which will help in accessing the table from Word document. By this we will be creating a Table with Rows and Columns.

Step 15: At this stage, we need to check the number of tables in Word file. And if there are no tables found then we should be getting the message for that.

Step 16: In this step, we need to access the table from Word document and map that in Excel file. For this, we will use For loop for each row and column.

Step 17: At last we will quit the document once it appends the data from word to excel file without saving the file.

We will see, the data which we have seen at the starting of the article which was separate in 2 different tables, is now appended in a single table in the excel sheet.

Below is the complete code in one sequence:

Code:

Sub

VBA_GetObject()

Dim

WordFile

As Object

Dim

WordDoc

As Object

Dim

StrDoc

As String

On Error Resume Next

Set

WordFile = GetObject(, "Word.Application")

If

Err.Number = 429

Then

Err.Clear

Set

WordFile = CreateObject("Word.Application")

End If

WordFile.Visible =

True

StrDoc = "D:InputTest.docx"

If

Dir(StrDoc) = ""

Then

MsgBox StrDoc & vbCrLf & "Not Found in mentioned Path" & vbCrLf & "C:Input Location", vbExclamation, "Document name not found"

Exit Sub

End If

WordFile.Activate

Set

WordDoc = WordFile.Documents(StrDoc)

If

WordDoc

Is Nothing Then Set

WordDoc = WordFile.Documents.Open("D:InputTest.docx") WordDoc.Activate

Dim

Tble

As Integer

Dim

RowWord

As Long

Dim

ColWord

As Integer

Dim

A

As Long

Dim

B

As Long

A = 1 B = 1

With

WordDoc Tble = WordDoc.Tables.Count

If

Tble = 0

Then

MsgBox "No Tables Avaiable", vbExclamation, "Nothing To Import"

Exit Sub

End If

For

i = 1

To

Tble

With

.Tables(i)

For

RowWord = 1

To

.Rows.Count

For

ColWord = 1

To

.Columns.Count Cells(A, B) = WorksheetFunction.Clean(.cell(RowWord, ColWord).Range.Text) B = B + 1

Next

ColWord B = 1 A = A + 1

Next

RowWord

End With

Next

End With

WordDoc.Close Savechanges:=

False

WordFile.Quit

Set

WordDoc =

Nothing

Set

WordFile =

Nothing

End Sub

Pros of Excel VBA GetObject

It is quite useful in importing the big set of data from word file to excel file.

We can import any kind of data from any kind of file just by changing the extension of it.

Things to Remember

Close all the word files before running the code.

Give the proper extension to the file which is being used.

GetObject cannot be used to access the reference to class.

Recommended Articles

This is a guide to VBA GetObject. Here we discuss how to use GetObject function in VBA to fetch the data from a word file into the excel sheet along with a practical example and downloadable excel template. You can also go through our other suggested articles –

You're reading Example Of Getobject Function In Excel Vba

How To Use Vba Trim Function To Remove Spaces In Excel?

Excel VBA Trim Function

Excel VBA Trim Function is used for removing the extra spaces from any cell or text and gives us the output which has a standard in terms of required spaces. VBA Trim function works exactly as the Excel Trim function and Trim function also removes the extra spaces in 3 ways;

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Spaces from the starting of the text.

Spaces from the end of the text.

Spaces from the middle of the text if more than 1 extra space is seen.

This mostly happens when we download data from some server, data warehouse or while incorrectly inserting the extra spaces manually. Even though we can easily see the spaces at the middle of the text, but spaces at the beginning and end of text cannot be seen easily until and unless we go to edit mode of that specific cell. This can be done by VBA Marco.

How to Use Excel VBA Trim Function?

We will discuss how to use VBA Trim Function by using some examples.

You can download this VBA Trim Function Excel Template here – VBA Trim Function Excel Template

VBA Trim Function – Example #1

Here we have 3 cells in the below screenshot.

And each cell has some spaces associated with them. Cell A1 has spaces at the end.

Cell A2 has spaces at the beginning of the text.

And cell A3 has space in between the text which is highlighted in the below screenshot.

This will take us to Visual Basic coding window. Now go to the Insert menu from VBA window and select Module as shown below.

This will create a new Module where we will write a code for trimming. Below are the steps for creating and writing trimming code:

First, choose a variable and define a range. Here we have selected the variable “A”. This variable can be anything.

As our data set has already some text or object then we need to set the range for it. And for that, we will use “Selection”. It will automatically select the data in the present sheet.

Code:

Sub

Trim_Data()

Dim

A

As

Range

Set

A = Selection

End Sub

For selecting each filled cell of the sheet “For Each” is the function And Value function is used for selecting the value with Trim(Cell) selection.

Code:

Sub

Trim_Data()

Dim

A

As

Range

Set

A = Selection

For Each

cell

In

A cell.Value = WorksheetFunction.Trim(cell)

Next

End Sub

By this, we complete the coding work for creating macro through VBA Trim. Now let’s apply the created code into some tab. For this go to Insert Menu and under Illustrations select Shapes.

From that Shapes option, create any shape using shape formats and styles.

VBA Trim Function – Example #2

VBA Coding shown above can be written in one more way. In the above example, we only trimmed the selected data. In this we will insert the function in the message box as well it will show once the data is trimmed.

Once we do that, we will get the Visual Basic window. Now open a fresh Module and start coding on that page.

Now consider the same code which we have written in example-1. But here for printing a message box with the text we will insert some text.

Sub

Trim_Data2()

  Dim

A

As

Range

Set

A = Selection

For Each

cell

In

A cell.Value = WorksheetFunction.Trim(cell)

Dim

B

As

 

String

B = Trim("Trimming Done") MsgBox B

Next

End Sub

Once we are done with writing the code, close the Visual Basic windows.

After that, we will get the button created on the screen.

As we can see above, here our data got trimmed and we can see the message as well of “Trimming Done”.

Pros of Excel VBA Trim Function

We can trim huge sets of data in one shot without checking the number of extra spaces the cells have.

Coding of trimming function is also quite small.

There are very less or no chances that any extra space will get spared.

Things To Remember

Always save the file as Macro-Enabled Worksheet. By doing this, we can use assigned macro multiple time.

Compile the code before assigning it to any button.

There are 2 different ways are shown for creating a button and both can be used as per individuals need or wish.

Consider writing VBA Code as shown in example-2 which will give the message as well.

Recommended Articles

This has been a guide to Excel VBA Trim. Here we discussed how to use VBA Trim Function to remove spaces in Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles–

Frequency Formula In Excel (Example)

Frequency Formula in Excel

The FREQUENCY formula is a prebuilt integrated function categorized under a STATISTICAL group of formulas in Excel.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

It’s a statistical calculation that lets you know how many times a specific value falls within a specific range, e.g., Suppose a table contains multiple age groups of people. We can group them based on the age parameter, i.e., Age grouping (frequency of appearance for Different age groups i.e.

Child (0-12 years),

Adolescence (13-18)

Adult (19-59)

Senior Citizen (60 years and above)

Definition of Frequency formula in Excel

The frequency formula calculates or determines the supplied vertical array’s frequency distribution based on the Bins or intervals we supply.

The frequency formula counts the values in a range of Excel cells or arrays that fall within a range or bin.

The syntax for the Frequency formula in Microsoft Excel is as follows:

The Frequency Formula in Excel has two arguments which are as below:

Data Array: An actual range or array value is where you need to find out Dataset’s frequency distribution.

Bins Array: A range or array of intervals (BINS) for grouping values.

The frequency formula should be entered as an array formula for multiple frequencies. If we want to find out multiple Bins or return multiple frequency values, it should be entered as an array formula with the CTRL +SHIFT +ENTER option in Excel.

How to Use Frequency Formula in Excel?

Frequency Formula in Excel is very simple and easy to use. Let’s understand the working of the Frequency Formula in Excel with some examples.

You can download this Frequency Formula Excel Template here – Frequency Formula Excel Template

Example #1 – To find out a single Frequency in Excel

In the below-mentioned example, the Table contains a different number of age group entities. I need to determine the frequency of the age group under or equal to 10 years. Here Frequency formula counts how often values occur in a range or array in Excel.

Now, Let’s apply the frequency formula in cell C2. Select cell C2 where the frequency formula needs to be applied.

A dialog box appears where arguments for the frequency function need to be filled or entered. i.e. =FREQUENCY (data_array, bins_array)

Data Array: It is an actual range or array value where you need to find out Dataset’s frequency distribution; here, the range or array is from A2 to A18, So select the column range. i.e. A2:A18.

Bins Array: A reference or range or array of intervals (“BINS”) for grouping values.

Example #2 – To Find Multiple Frequency in a date range

In the below-mentioned example, the Table contains a member with different age entities. I need to determine or calculate the frequency distribution of different age groups of 17 members aged between 9 and 80.

Now, Let’s apply the frequency formula in cell E2. Select cell E2 where the frequency formula needs to be applied.

A dialog box appears where arguments for the frequency function need to be filled or entered. i.e. =FREQUENCY (data_array, bins_array)

Data_Array: It is an actual range or array value where you need to find out Dataset’s frequency distribution; here, the range or array is from A2 to A18, So select the column range. i.e. A2:A18.

Bins_Array: A reference or range or array of intervals (BINS) for grouping values.

Note: Different age groups are split into 4 age groups to be four bins_array values.

The result will be as given below.

Now, you can observe that only the first cell of the Frequency column has a value; the rest are blank.

Now, you can see the frequency result column; it displays all the missing values.

In the formula toolbar, you can observe the curly brackets for an array formula.

Things to Remember

Frequency Formula ignores blank cells or text values in Excel.

If the Data_Array argument in the frequency formula does not contain any values in an array or range of cells, then Frequency returns an array of zeros.

If the Bin_Array argument in the frequency formula does not contain any values in an array or range of cells, then Frequency returns the number of elements in the data_array.

Recommended Articles

This has been a guide to Frequency Formula in Excel. Here we discuss how to use Frequency Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

Working Of Numpy Flatten() Function With Example

Introduction of NumPy flatten

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Working of NumPy flatten() Function

In this article, we will see how flatten() function will work in Python with numpy module and an array object defined by this module which is used for representing a multidimensional array having an immovable number of elements in it. This NumPy module and the object of an array as ndarray are used for demonstrating a flatten() function. Now in the below section let us see the syntax and examples of the flatten function.

Syntax:

ndarray.flatten(order)

In the above syntax, we have an array object as ndarray which is an input array to which the flatten() function needs to be applied. The parameter “order” is passed to the function having different values such as C, F, A, and K where C is a default value. This function returns the input array with multi-dimensional array flattens or collapsed to one dimension.

“C” is used as the value to flatten the given multidimensional array to a single dimension in row-wise order.

“F” is used to flatten or collapse to single dimensional array in the order of column-wise.

“A” is optional value and it is used only when the F value is having an array as contiguous in memory with column-wise flattening, otherwise, it will flatten in the row-wise.

“K” is also used as a value to the order parameter to flatten the array in the order the items or elements appear in the memory.

Example of NumPy flatten

Code:

import numpy as nf print("Program to demonstrate flatten function of numpy module") print("n") in_arr = nf.array([[2,3,8], [4,5,9], [3,0,6]]) print("The given input array is as follows:") print(in_arr) print("n") print("The flattened array is as follows:") print(in_arr.flatten()) print("n") print("The flattened array in C ordering is as follows:") print(in_arr.flatten(order = 'C')) print("n") print("The flattened array in F ordering is as follows:") print(in_arr.flatten(order = 'F')) print("n") print("The flattened array in A ordering is as follows:") print(in_arr.flatten(order = 'A')) print("n") print("The flattened array in K ordering is as follows:") print(in_arr.flatten(order = 'K')) print("n")

Output:

In the above program, we can see we have to first import NumPy and set its alias name as “nf” so that it becomes easy to refer it in the entire program. Then we are creating an array of 3*3 dimensional array using an array function of the NumPy module. Then we can flatten this multidimensional 3*3 array into a single dimension array using the flatten() function on the given input array “in_arr”. As C is default value so when we don’t pass any value to the flatten function then it will arrange the 3*3 dimensional array row-wise that means the first row is appended with the second row and then the second is appended wit 3rd row to form a single array. Similarly, when we have specified the value of order as “C” to arrange the multidimensional array to one dimension. Then in the next line, we have specified the value as “F” which is Fortran style to arrange the given input array column-wise as shown in the above screenshot such as the first column is appended with the second column and then it is appended with 3rd column to form 3*3 dimensional array to single dimensional array. So, therefore, as we can see in the above program, we have also used “A” value which is also arranging the elements of 3*3 dimensional array into a one-dimensional array in row-wise as F does not contain contiguous values in memory. Then lastly, we have specified value as “K” which will flatten the elements of 3*3 dimensional array into one dimension as they appear in the given input array memory and the output of all the possibilities of the parameter values are displayed in the above screenshot.

This flatten() function of NumPy which is used in dealing with huge data related to neural networks like convent then this function is very useful. In Python, there is another function provided by the NumPy module is reshape() function which is also similar to flatten() function and also have the same parameter values such as C, F, A and K with C as default for reshaping the elements in row-wise but flatten() function will reduce the multidimensional array to one dimension whereas reshape() function can be used to reduce or collapse or reshape the given array into any dimensional array which is mainly used to arrange data from wide to long.

Conclusion

In this article, we conclude that we are using a flatten() function of the NumPy module in Python to reduce or collapse the given multidimensional array into one dimension array. In this article, we have discussed the syntax along with parameters and its values such as C, F, A, and K which is used for arranging the array elements in either row-wise or column-wise. In this article, we also an example of how to use flatten() function, and their different parameter values are demonstrated with output and screenshot.

Recommend ed Articles

This is a guide to NumPy Flatten. Here we also discuss the introduction and working of numpy flatten() function along with an example. You may also have a look at the following articles to learn more –

Stdev Function In Excel (Formula, Examples)

STDEV Function in Excel (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

STDEV Function in Excel

Stdev in Excel is used for calculating the standard deviation of any data set. Standard Deviation shows how one value deviates to another with how many digits. Stdev is used only for sample data, whereas we have other Standard Deviation functions such as STDEV.P for the entire population. To calculate the standard deviation of sample data, we have a new function which is STDEV.S. To calculate standard deviation, select the range of numbers.

STDEV Formula in Excel

Below is the STDEV Formula in Excel :

Examples of STDEV function in Excel

1) Let us take a look at some examples of STDEV. STDEV is widely used to measure how much the individual data elements are dispersed from the average value.

You can download this STDEV Function Excel Template here – STDEV Function Excel Template

For example, let’s say we have a dataset of parts for a store which is being checked for quality. We need to find the standard deviation between the number of parts we have, the number of parts to be reworked, parts that failed, the parts yet to be checked, and the total number of parts.

The function STDEV will use like this:

This would give us the standard deviation for values in the cells A2 till F2. Similarly, we can find out the standard deviation for others.

The above data analysis means that the difference between the number of parts for each category is high. If the formula returned a lower number, it would mean that the number of parts of each category is closer. This is an important factor in many applications, such as analyzing the effectiveness of quality control measures.

2) Let’s look at another example where the STDEV function is useful.

Suppose we have weight data for a number of shipments. We want to find out how much they differ from each other.

The data is as below:

Shipment 1:  0.5kg

Shipment 2: 2 kg

Shipment 3: 0.8 kg

Shipment 4: 2.8 kg

Shipment 5: 1.1 kg

We put this in an Excel sheet.

Now Use the STDEV function to get the standard deviation:

This would give us the standard deviation in cell B7.

Now we find that the standard deviation is about 0.9, which means that the weights do not vary much between the shipments. Such problems contain many more data points in real life and use more measures such as average and variance to create solutions to specific problems.

Explanation of the STDEV function

In this formula, one of the values (say 1,  etc.) is the mean or average of the numbers, while n is the total sample size. Standard deviation measures how widely each value in a sample varies from the average value. It is the square root of the variance. Variance is the squared differences’ average from a dataset’s mean or average.

How to Use the STDEV Function in Excel

Now that we have an overview of the statistics underlying the STDEV function in Excel, and a few examples of STDEV functions’ uses, let us look at how to use the function and the correct syntax.

First, we need one or more data points to choose from. Once we have done that, we need to pick a cell where we want the result displayed.

Now type the function and arguments correctly and press F2 and Enter.

And the result will be :

The syntax of the STDEV function is as follows:

In STDEV, the number 1 is called a mandatory argument and corresponds to a sample of the data. The number 2 and other numbers are optional arguments but should correspond to a dataset sample to get correct results.

STDEV has some related functions for use with different types of datasets; the most commonly used are the following:

STDEV.S for a numeric data sample does not calculate standard deviation based on text and logical values.

STDEVA for calculations based on logical values and text

We can also use the STDEV function from the formula bar, as shown below:

Pick a dataset and select any cell in which we would like to display the result.

Then select the data that needs analysis.

And the result will be :

Things to Remember About the STDEV Function in Excel

We have seen a few examples of the STDEV function and have also seen how to use it in problems of data analysis in the examples; here are a few things to remember:

When using STDEV, the function assumes that the sample is the entire size of the population. So when we want to determine the standard deviation from a subset of the whole data, we should use STDEV.S

The standard deviation formula used in STDEV is called the (n-1) method.

The arguments in STDEV can be numbers or text, arrays of different types, and references containing numbers.

The calculation in STDEV uses logical values and text typed directly in the arguments. If the logical values or text are referenced from an array, empty cells, or error values in the reference, STDEV ignores them.

To use logical and text values referenced from elsewhere and not typed directly in the argument, we should use the STDEVA function.

Population in statistics means the entire dataset we are using. A sample is a part of the dataset which we pick to analyze.

Conclusion – STDEV Function in Excel

Data science is one of the most important areas of study currently. Excel is a tool we can use in many data science applications without resorting to high-level programming or having an in-depth knowledge of the mathematics involved. STDEV and many other such functions make it much easier for us to be data analysts. However, we need to know the basics and think about creative solutions to apply them.

Recommended Articles

This has been a guide to STDEV Function in Excel. Here we discuss the STDEV Formula in Excel and How to use the STDEV function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

Understanding Excel Vba Data Types (Variables And Constants)

In Excel VBA, you would often be required to use variables and constants.

When working with VBA, a variable is a location in your computer’s memory where you can store data. The type of data you can store in a variable would depend on the data type of the variable.

For example, if you want to store integers in a variable, your data type would be ‘Integer’ and if you want to store text then your data type would be ‘String’.

More on data types later in this tutorial.

While a variable’s value changes when the code is in progress, a constant holds a value that never changes. As a good coding practice, you should define the data type of both – variable and constant.

When you code in VBA, you would need variables that you can use to hold a value.

The benefit of using a variable is that you can change the value of the variable within the code and continue to use it in the code.

For example, below is a code that adds the first 10 positive numbers and then displays the result in a message box:

Sub AddFirstTenNumbers() Dim Var As Integer Dim i As Integer Dim k as Integer For i = 1 To 10 k = k + i Next i MsgBox k End Sub

There are three variables in the above code – Var, i, and k.

The above code uses a For Next loop where all these three variables are changed as the loops are completed.

The usefulness of a variable lies in the fact that it can be changed while your code is in progress.

Below are some rules to keep in mind when naming the variables in VBA:

You can use alphabets, numbers, and punctuations, but the first number must be an alphabet.

You can not use space or period in the variable name. However, you can use an underscore character to make the variable names more readable (such as Interest_Rate)

You can not use special characters (#, $, %, &, or !) in variable names

VBA doesn’t distinguish between the case in the variable name. So ‘InterestRate’ and ‘interestrate’ are the same for VBA. You can use mixed case to make the variables more readable.

VBA has some reserved names that you can use for a variable name. For example, you can not use the word ‘Next’ as a variable name, as it’s a reserved name for For Next loop.

Your variable name can be up to 254 characters long.

To make the best use of variables, it’s a good practice to specify the data type of the variable.

The data type you assign to a variable will be dependent on the type of data you want that variable to hold.

Below is a table that shows all the available data types you can use in Excel VBA:

Data Type Bytes Used Range of Values

Byte 1 byte 0 to 255

Boolean 2 bytes True or False

Integer 2 bytes -32,768 to 32,767

Long (long integer) 4 bytes -2,147,483,648 to 2,147,483,647

Single 4 bytes -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values

Double 8 bytes -1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

Currency 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal 14 bytes +/-79,228,162,514,264,337,593,543,950,335 with no decimal point;+/-7.9228162514264337593543950335 with 28 places to the right of the decimal

Date 8 bytes January 1, 100 to December 31, 9999

Object 4 bytes Any Object reference

String (variable-length) 10 bytes + string length 0 to approximately 2 billion

String (fixed-length) Length of string 1 to approximately 65,400

Variant (with numbers) 16 bytes Any numeric value up to the range of a Double

Variant (with characters) 22 bytes + string length Same range as for variable-length String

User-defined Varies The range of each element is the same as the range of its data type.

When you specify a data type for a variable in your code, it tells VBA to how to store this variable and how much space to allocate for it.

For example, if you need to use a variable that is meant to hold the month number, you can use the BYTE data type (which can accommodate values from 0 to 255). Since the month number is not going to be above 12, this will work fine and also reserve less memory for this variable.

On the contrary, if you need a variable to store the row numbers in Excel, you need to use a data type that can accommodate a number up to 1048756. So it’s best to use the Long data type.

As a good coding practice, you should declare the data type of variables (or constants) when writing the code. Doing this makes sure that VBA allocates only the specified memory to the variable and this can make your code run faster.

Below is an example where I have declared different data types to different variables:

Sub DeclaringVariables() Dim X As Integer Dim Email As String Dim FirstName As String Dim RowCount As Long Dim TodayDate As Date End Sub

To declare a variable data type, you need to use the DIM statement (which is short for Dimension).

In ‘Dim X as Integer‘, I have declared the variable X as Integer data type.

Now when I use it in my code, VBA would know that X can hold only integer data type.

If I try to assign a value to it which is not an integer, I will get an error (as shown below):

Note: You can also choose to not declare the data type, in which case, VBA automatically considers the variable of the variant data type. A variant data type can accommodate any data type. While this may seem convenient, it’s not a best practice to use variant data type. It tends to take up more memory and can make your VBA code run slower.

While you can code without ever declaring variables, it’s a good practice to do this.

Apart from saving memory and making your code more efficient, declaring variables has another major benefit – it helps trap errors caused by misspelled variable names.

To make sure you’re forced to declare variables, add the following line to the top of your module.

Option Explicit

When you add ‘Option Explicit’, you will be required to declare all the variables before running the code. If there is any variable that has not been declared, VBA would show an error.

There is a huge benefit in using Option Explicit.

Sometimes, you may end up making a typing error and enter a variable name which is incorrect.

Normally, there is no way for VBA to know whether it’s a mistake or is intentional. However, when you use ‘Option Explicit’, VBA would see the misspelled variable name as a new variable that has not been declared and will show you an error. This will help you identify these misspelled variable names, which can be quite hard to spot in a long code.

Below is an example where using ‘Option Explicit’ identifies the error (which couldn’t have been trapped had I not used ‘Option Explicit’)

Sub CommissionCalc() Dim CommissionRate As Double CommissionRate = 0.1 Else CommissionRtae = 0.05 End If MsgBox "Total Commission: " & Range("A1").Value * CommissionRate End Sub

Note that I have misspelled the word ‘CommissionRate’ once in this code.

If I don’t use Option Explicit, this code would run and give me the wrong total commission value (in case the value in cell A1 is less than 10000).

But if I use Option Explicit at the top of the module, it will not let me run this code before I either correct the misspelled word or declare it as another variable. It will show an error as shown below:

While you can insert the line ‘Option Explicit’ every time you code, here are the steps to make it appear by default:

Check the option – “Require Variable Declaration”.

Once you have enabled this option, whenever you open a new module, VBA would automatically add the line ‘Option Explicit’ to it.

Note: This option will only impact any module you create after this option is enabled. All existing modules are not affected.

So far, we have seen how to declare a variable and assign data types to it.

In this section, I will cover the scope of variables and how you can declare a variable to be used in a subroutine only, in an entire module or in all the modules.

The scope of a variable determines where can the variable be used in VBA,

There are three ways to scope a variable in Excel VBA:

Within a single subroutine (Local variables)

Within a module (Module-level variables)

In all modules (Public variables)

Let’s look at each of these in detail.

When you declare a variable within a subroutine/procedure, then that variable is available only for that subroutine.

You can not use it in other subroutines in the module.

As soon as the subroutine ends, the variable gets deleted and the memory used by it is freed.

In the below example, the variables are declared within the subroutine and would be deleted when this subroutine ends.

When you want a variable to be available for all the procedures in a module, you need to declare it at the top of the module (and not in any subroutine).

Once you declare it at the top of the module, you can use that variable in all the procedures in that module.

In the above example, the variable ‘i’ is declared at the top of the module and is available to be used by all the modules.

Note that when the subroutine ends, the module level variables are not deleted (it retains its value).

Below is an example, where I have two codes. When I run the first procedure and then run the second one, the value of ‘i’ becomes 30 (as it carries the value of 10 from the first procedure)

If you want a variable to be available in all the procedure in the workbook, you need to declare it with the Public keyword (instead of DIM).

The below line of code at the top of the module would make the variable ‘CommissionRate’ available in all the modules in the workbook.

 Public CommissionRate As Double

You can insert the variable declaration (using the Public keyword), in any of the modules (at the top before any procedure).

When you work with local variables, as soon as the procedure ends, the variable would lose its value and would be deleted from VBA’s memory.

In case you want the variable to retain the value, you need to use the Static keyword.

Let me first show you what happens in a normal case.

In the below code, when I run the procedure multiple times, it will show the value 10 everytime.

Sub Procedure1() Dim i As Integer i = i + 10 MsgBox i End Sub

Now if I use the Static keyword instead of DIM, and run the procedure multiple times, it will keep on showing values in increments of 10. This happens as the variable ‘i’ retains its value and uses it in the calculation.

Sub Procedure1() Static i As Integer i = i + 10 MsgBox i End Sub

While variables can change during the code execution, if you want to have fixed values, you can use constants.

A constant allows you to assign a value to a named string that you can use in your code.

The benefit of using a constant is that it makes it easy to write and comprehend code, and also allows you to control all the fixed values from one place.

For example, if you are calculating commissions and the commission rate is 10%, you can create a constant (CommissionRate) and assign the value 0.1 to it.

In future, if the commission rate changes, you just need to make the change at one place instead of manually changing it in the code everywhere.

Below is a code example where I have assigned a value to the constant:

Sub CalculateCommission() Dim CommissionValue As Double Const CommissionRate As Double = 0.1 CommissionValue = Range("A1") * CommissionRate MsgBox CommissionValue End Sub

The following line is used to declare the constant:

Const CommissionRate As Double = 0.1

When declaring constants, you need to start with the keyword ‘Const‘, followed by the name of the constant.

Note that I have specified the data type of the constant as Double in this example. Again, it’s a good practice to specify the data type to make your code run faster and be more efficient.

If you don’t declare the data type, it would be considered as a variant data type.

Just like variables, constants can also have scope based on where and how these are declared:

Within a single subroutine (Local constants): These are available in the subroutine/procedure in which these are declared. As the procedure ends, these constants are deleted from the system’s memory.

Within a module (Module-level constants): These are declared at the top of the module (before any procedure). These are available for all the procedures in the module.

In all modules (Public constants): These are declared using the ‘Public’ keyword, at the top of any module (before any procedure). These are available to all the procedures in all the modules.

You May Also Like the Following VBA Tutorials:

Update the detailed information about Example Of Getobject Function In Excel Vba on the Cancandonuts.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!