I wish to extend the wise words of Steve Jobs and say everyone in the world should learn how to program a computer. You may not necessary end up working as a programmer or writing programs at all but it will teach you how to think. In this tutorial, we are going to cover the following topics. What is VBA? Why VBA? Before we go into further details, let's look at what computer programming is in a layman's language.
Assume you have a maid. If you want the maid to clean the house and do the laundry. You tell her what to do using let's say English and she does the work for you.
As you work with a computer, you will want to perform certain tasks. Just like you told the maid to do the house chores, you can also tell the computer to do the tasks for you. The process of telling the computer what you want it to do for you is what is known as computer programming. Just as you used English to tell the maid what to do, you can also use English like statements to tell the computer what to do.
The English like statements fall in the category of high level languages. VBA is a high level language that you can use to bend excel to your all powerful will.
VBA is actually a sub set of Visual Basic 6. It uses English like statements to write instructions Creating the user interface is like using a paint program. You just have to drag, drop and align the graphical user interface controls.
Application.UserName property (Excel)
Short learning curve. From day one that you start learning, you can immediately start writing simple programs. Read the article on Macros for more information on how you can achieve this.
For business use, you can create complete powerful programs powered by excel and VBA. The advantage of this approach is you can leverage the powerful features of excel in your own custom programs. The following basics will help you get started. Variable — in high school we learnt about algebra. In this expression, x and y are variables. They can be assigned any numbers i. They can also be changed to say 4 and 2 respectively. Variables in short are memory locations. As you work with VBA, you will be required to declare variables too just like in algebra classes Rules for creating variables Don't use reserved words — if you work as a student, you cannot use the title lecturer or principal.
These titles are reserved for the lecturers and the school authority. Reserved words are those words that have special meaning in Vba and as such, you cannot use them as variable names. Variable names cannot contain spaces — you cannot define a variable named first number. Use descriptive names — it's very tempting to name a variable after yourself but avoid this. Use descriptive names i. Logical operators - The concept of logical operators covered in the earlier tutorials also apply when working with VBA.
Here the name is the name you want to assign to your program.In this VBA Tutorial, you learn how to s et a cell's or cell range's value and g et a cell's or cell range's value. You can get immediate access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.
The following VBA and Macro Tutorials may help you better understand and implement the contents below:. Value property. Value2 property. The following GIF illustrates the results of executing the first macro example, which works with the Range.
The following GIF illustrates the results of executing the second macro example, which works with the Range. To set a cell range's value with VBAuse a statement with the following structure:. The message box displays the value of cell A7. Notice that cell A7 is formatted as currency. The Range. Value property converts the cell's value to the Currency data type, which results in a fixed-point number with 4 decimal digits.
Therefore, the message box displays a value with only 4 decimal placesinstead of the 10 decimal places that the original value in cell A7 has. Value2 property doesn't work with the Currency data type. Therefore, the message box displays all the 10 decimal places that the original value in cell A7 has.
To get a cell range's value with VBAuse a statement with the following structure:. The message boxes display the values of cell A11 to C Notice that the cell range is formatted as currency. Value property converts the cell range's values to the Currency data type, which results in fixed-point numbers with 4 decimal digits. Therefore, the message boxes display values with a maximum of 4 decimal placesinstead of the 10 decimal places that the original values in the cell range have.
Therefore, the message boxes display all the 10 decimal places that the original values in the cell range have. Here are my top 4 picks:. Use the following Table of Contents to navigate to the section that interests you.Keep in touch and stay productive with Teams and Officeeven when you're working remotely.
Connecting to and transforming data often follows a few common steps:. While some data analysis efforts focus on a few of these steps, each step is important in the data analysis and transformation process.
The content and premises are similar but some screens and steps are slightly different, and we want you to clearly see how these features work in your version of Excel. To see these features in action in previous versions of Excel, take a look at Getting Started with Power Query. The following links and information can get you started, and help you understand these new data import and transformation features in Excel. We update these links when additional resources are available, so check back often.
A query enables you to connect to, preview, and transform data from a wide variety of available data sources. You can then load that transformed data into a table, or into the built-in Data Model in Excel, and even refresh that data later on. You can also edit the query whenever you need to, and share your query, too.
From there, choose your data source. Want to see how a query can transform your data? These features are often referred to as Power Query. Here are a few tutorials that can get you started, as well as links that guide you through more challenging and interesting scenarios:.
Power Query Combine data from multiple data sources Power Query. Modify a formula Power Query. Learn about Power Query formulas. Create an advanced query Power Query. Microsoft Power Query for Excel Help. Take a look at the following resources to get involved with others in the community. Power Query forum. Power BI blog. Excel blog. Learn more. Expand your Office skills.Instead of referencing a single cell, you can reference a range of cells and change all of the cell values at once:.
In the above examples, we set the cell value equal to a number 1. Instead, you can set the cell value equal to a string of text. In VBA, all text must be surrounded by quotations:.
Here we used a variable of type Variant. Variant variables can accept any type of values. Instead, you could use a String variable type:. If you know your cell value will be numerical, you could use a Double variable type Double variables can store decimal values :. However, if you attempt to store a cell value containing text in a double variable, you will receive an error:. You can compare cell values using the standard comparison operators.
Associated Files Download Links. Range "A2". Cells 21.
Range "A2:A5". Dim strText as String. MsgBox ActiveCell.
VBA Active Cell
Dim var as Variant. Dim var as String. Dim var as Double. Range "A1". Range "A1:A5". MsgBox Range "A1". If Range "A1". Value Then. Range "C1". Elseif Range "A1". End If. You may also like some of this related content Contact Us. Automate Excel.Querying data over the web becomes more common every day. Almost every cloud based program has the ability to serve up its data through some sort of API. Sometimes, the data can be extracted to Excel through some sort of reporting interface within the program.
This is where the ability to make an Excel http get request is so valuable. It is a free service that provides the ability to search for tickers and company names and then fetch stock data on that entity.
They consist of these components:. The host is just that, the domain address a host has chosen for you to reach their server. The port is usually not needed to be explicitly typed. The default port is 80 and the URL implicitly sends that if you exclude it. The resource path and the query are the import parts I want to point out.
The resource path is instructions sent to the host that tell the server where to navigate to so that your information can be found. Lastly, the query section is always kicked off with a question mark. This section provides any parameters you want to include as well the option to explicitly list the precise data you want returned.
This article on the Mozilla Developer Network is a great place to start. The first thing we need to do is add some library references so we can access the controls we will need to make HTTP calls. We will then need to pass that data into our code. Now, we need to write the code that will accept this input, and then make an http get request to return potential matches.
That would return dozens of companies so we need somewhere to store those values temporarily. It looks like this:. This is why I like to use that approach in my Excel http get request programs.
If you want to read more about what this entails, MSDN has a great write up on it. Suffice it to say this service allows us to send HTTP requests and parse the responses. Now, with that variable we will open a connection and pass it three parameters. This prevents anything further from happening until we get a response from the host. If we left this blank, the default is true. The program could then move on while we are waiting the call and break.
There are two more optional parameters in this Open method for username and password, in that order. They are not required in this example however. In the next section of code, we create a response variable and store the response from the request as a string. Note that I wrap my response in some additional text. In the form that pops up, our user will select a company for which they want to return a stock quote.
To do this, we need to have this process kick off when the event for the button click occurs.Active cell is the currently selected cell in a worksheet, active cell in VBA can be used like a reference to move to another cell or change the properties of the same active cell or the cells reference provided from the active cell, active cell in VBA can be accessed by using the application.
To work efficiently with VBA coding it is important to understand the concept of range object and cells properties in VBA. In excel there are millions of cells and your doubt for sure is which one is Active Cell.
For an example look at the below image. If your active cell not visible in your window then look at the name box it will show you the active cell address, in the above image active cell address is B3. Even when the many cells are selected as a range of cells whatever the first cell is in the selection becomes the active cell. For example, look at the below image. In our earlier articles, we have seen how to reference the cells in VBA. By Active Cell property we can refer the cell.
But here I have used ActiveCell. The reason why I have used Active Cell property because the moment I select the cell A1 it becomes an active cell. Now, look at the below image. Run this code using F5 key or manually then, it will show the address of the active cell in a message box.
Active Cell property has parameters as well. After entering the property ActiveCell open parenthesis to see the parameters. For example, ActiveCell 1,1 means whichever the cell active. Here we learn the Concept of Active cell to find the address of a cell and also we learned Parameters of Active Cell in Excel VBA along with practical examples and a downloadable template.
Below you can find some useful excel VBA articles —. Your email address will not be published. Save my name, email, and website in this browser for the next time I comment. Login details for this Free course will be emailed to you. Free Excel Course. Active Cell in Excel VBA Active cell is the currently selected cell in a worksheet, active cell in VBA can be used like a reference to move to another cell or change the properties of the same active cell or the cells reference provided from the active cell, active cell in VBA can be accessed by using the application.
Popular Course in this category. View Course.Are you facing a repetitive clean up of fifty tables in Word? Do you want a particular document to prompt the user for input when it opens? Are you having difficulty figuring out how to get your contacts from Microsoft Outlook into a Microsoft Excel spreadsheet efficiently?
You can perform these tasks and accomplish a great deal more by using Visual Basic for Applications VBA for Office—a simple, but powerful programming language that you can use to extend Office applications. This article is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.
The Office suite of applications has a rich set of features.
There are many different ways to author, format, and manipulate documents, email, databases, forms, spreadsheets, and presentations. The great power of VBA programming in Office is that nearly every operation that you can perform with a mouse, keyboard, or a dialog box can also be done by using VBA. Further, if it can be done once with VBA, it can be done just as easily a hundred times. In fact, the automation of repetitive tasks is one of the most common uses of VBA in Office. Beyond the power of scripting VBA to accelerate every-day tasks, you can use VBA to add new functionality to Office applications or to prompt and interact with the user of your documents in ways that are specific to your business needs.
For example, you could write some VBA code that displays a pop up message that reminds users to save a document to a particular network drive the first time they try to save it. This article explores some of the primary reasons to leverage the power of VBA programming. It explores the VBA language and the out-of-the-box tools that you can use to work with your solutions.
Finally, it includes some tips and ways to avoid some common programming frustrations and missteps. Interested in developing solutions that extend the Office experience across multiple platforms?Excel Visual Basic (VBA) for Beginners - Part 1 of 4: Buttons and Macros
Check out the new Office Add-ins model. VBA is effective and efficient when it comes to repetitive solutions to formatting or correction problems. For example, have you ever changed the style of the paragraph at the top of each page in Word?
Have you ever had to reformat multiple tables that were pasted from Excel into a Word document or an Outlook email? Have you ever had to make the same change in multiple Outlook contacts?
If you have a change that you have to make more than ten or twenty times, it may be worth automating it with VBA.
If it is a change that you have to do hundreds of times, it certainly is worth considering. Almost any formatting or editing change that you can do by hand, can be done in VBA. There are times when you want to encourage or compel users to interact with the Office application or document in a particular way that is not part of the standard application. For example, you might want to prompt users to take some particular action when they open, save, or print a document.
Do you need to copy all of your contacts from Outlook to Word and then format them in some particular way? Or, do you need to move data from Excel to a set of PowerPoint slides? Sometimes simple copy and paste does not do what you want it to do, or it is too slow. You can use VBA programming to interact with the details of two or more Office applications at the same time and then modify the content in one application based on the content in another.