Vba Read and Write to a Cell in Google Sheet

Introduction

This is a tutorial about writing lawmaking in Excel spreadsheets using Visual Basic for Applications (VBA).

Excel is 1 of Microsoft's nearly popular products. In 2016, the CEO of Microsoft said  "Retrieve almost a earth without Excel. That's only impossible for me." Well, perhaps the world can't think without Excel.

• In 1996, there were over 30 million users of Microsoft Excel (source).
• Today, there are an estimated 750 1000000 users of Microsoft Excel. That'southward a little more the population of Europe and 25x more than users than at that place were in 1996.

Nosotros're ane big happy family unit!

In this tutorial, y'all'll acquire about VBA and how to write code in an Excel spreadsheet using Visual Basic.

Prerequisites

You don't need any prior programming experience to understand this tutorial. However, you will need:

• Basic to intermediate familiarity with Microsoft Excel
• If y'all want to follow along with the VBA examples in this commodity, you volition need admission to Microsoft Excel, preferably the latest version (2019) but Excel 2022 and Excel 2013 will piece of work but fine.
• A willingness to attempt new things

Learning Objectives

Over the form of this article, you will learn:

1. What VBA is
2. Why you would utilize VBA
3. How to get set up upwards in Excel to write VBA
4. How to solve some real-world problems with VBA

Important Concepts

Hither are some important concepts that you should exist familiar with to fully understand this tutorial.

Objects: Excel is object-oriented, which ways everything is an object - the Excel window, the workbook, a sheet, a chart, a prison cell. VBA allows users to dispense and perform actions with objects in Excel.

If you don't accept any experience with object-oriented programming and this is a brand new concept, have a second to let that sink in!

Procedures: a procedure is a chunk of VBA code, written in the Visual Basic Editor, that accomplishes a job. Sometimes, this is also referred to as a macro (more on macros below). There are ii types of procedures:

• Subroutines: a group of VBA statements that performs one or more actions
• Functions: a group of VBA statements that performs one or more actions and returns ane or more values

Notation: you can have functions operating inside of subroutines. You'll meet later.

Macros: If you've spent whatsoever fourth dimension learning more advanced Excel functionality, you've probably encountered the concept of a "macro." Excel users can record macros, consisting of user commands/keystrokes/clicks, and play them back at lightning speed to attain repetitive tasks. Recorded macros generate VBA lawmaking, which yous tin and so examine. Information technology's actually quite fun to record a elementary macro and then look at the VBA code.

Delight proceed in mind that sometimes it may be easier and faster to tape a macro rather than manus-code a VBA procedure.

For example, perchance you work in project direction. Once a week, you lot have to turn a raw exported report from your project management organization into a beautifully formatted, clean study for leadership. You need to format the names of the over-budget projects in bold ruby text. Yous could tape the formatting changes equally a macro and run that whenever y'all need to brand the change.

What is VBA?

Visual Bones for Applications is a programming linguistic communication developed by Microsoft. Each software plan in the Microsoft Office suite is arranged with the VBA language at no actress price. VBA allows Microsoft Role users to create small programs that operate within Microsoft Function software programs.

Think of VBA similar a pizza oven within a eating place. Excel is the restaurant. The kitchen comes with standard commercial appliances, like big refrigerators, stoves, and regular ole' ovens - those are all of Excel's standard features.

But what if you want to make forest-fired pizza? Tin can't do that in a standard commercial baking oven. VBA is the pizza oven.

Yum.

Why use VBA in Excel?

Because forest-fired pizza is the all-time!

Merely seriously.

A lot of people spend a lot of time in Excel every bit a part of their jobs. Fourth dimension in Excel moves differently, likewise. Depending on the circumstances, 10 minutes in Excel tin feel similar eternity if you lot're not able to do what yous need, or 10 hours can go by very quickly if everything is going great. Which is when y'all should inquire yourself, why on earth am I spending 10 hours in Excel?

Sometimes, those days are inevitable. Just if you're spending eight-10 hours everyday in Excel doing repetitive tasks, repeating a lot of the same processes, trying to clean up after other users of the file, or fifty-fifty updating other files after changes are fabricated to the Excel file, a VBA procedure only might be the solution for you.

You should consider using VBA if you demand to:

• Automate repetitive tasks
• Create easy ways for users to interact with your spreadsheets
• Manipulate large amounts of information

Getting Set Upwardly to Write VBA in Excel

Programmer Tab

To write VBA, you'll need to add the Developer tab to the ribbon, and then you'll meet the ribbon like this.

To add together the Programmer tab to the ribbon:

1. On the File tab, become to Options > Customize Ribbon.
2. Under Customize the Ribbon and under Primary Tabs, select the Developer check box.

After you lot prove the tab, the Developer tab stays visible, unless you clear the check box or take to reinstall Excel. For more information, run into Microsoft assist documentation.

VBA Editor

Navigate to the Programmer Tab, and click the Visual Basic button. A new window will pop up - this is the Visual Basic Editor. For the purposes of this tutorial, you just demand to be familiar with the Project Explorer pane and the Property Properties pane.

Excel VBA Examples

Offset, let's create a file for us to play around in.

1. Open a new Excel file
2. Save it as a macro-enabled workbook (. xlsm)
3. Select the Developer tab
4. Open the VBA Editor

Let's rock and coil with some like shooting fish in a barrel examples to go you writing code in a spreadsheet using Visual Bones.

Example #1: Display a Message when Users Open the Excel Workbook

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don't paste!):

Sub Auto_Open()
MsgBox ("Welcome to the XYZ Workbook.")
End Sub

Salvage, shut the workbook, and reopen the workbook. This dialog should display.

Ta da!

How is it doing that?

Depending on your familiarity with programming, you may have some guesses. Information technology's not peculiarly complex, only in that location'southward quite a lot going on:

• Sub (short for "Subroutine): think from the first, "a group of VBA statements that performs one or more actions."
• Auto_Open: this is the specific subroutine. It automatically runs your code when the Excel file opens - this is the event that triggers the procedure. Auto_Open volition only run when the workbook is opened manually; it will not run if the workbook is opened via code from another workbook (Workbook_Open will do that, learn more about the divergence betwixt the two).
• By default, a subroutine's admission is public. This means any other module tin can use this subroutine. All examples in this tutorial will exist public subroutines. If needed, yous tin can declare subroutines as private. This may exist needed in some situations. Learn more than nigh subroutine access modifiers.
• msgBox: this is a function - a group of VBA statements that performs one or more deportment and returns a value. The returned value is the message "Welcome to the XYZ Workbook."

In short, this is a unproblematic subroutine that contains a function.

When could I use this?

Peradventure you lot have a very important file that is accessed infrequently (say, in one case a quarter), but automatically updated daily by another VBA procedure. When it is accessed, it's by many people in multiple departments, all across the company.

• Problem: Most of the time when users access the file, they are confused about the purpose of this file (why it exists), how it is updated so often, who maintains it, and how they should interact with it. New hires always have tons of questions, and y'all have to field these questions over and over and over again.
• Solution: create a user message that contains a concise answer to each of these frequently answered questions.

Real World Examples

• Use the MsgBox role to display a message when there is any event: user closes an Excel workbook, user prints, a new sheet is added to the workbook, etc.
• Apply the MsgBox part to display a message when a user needs to fulfill a condition before closing an Excel workbook
• Use the InputBox function to go information from the user

Example #2: Allow User to Execute another Procedure

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don't paste!):

Sub UserReportQuery()
Dim UserInput Every bit Long
Dim Answer As Integer
UserInput = vbYesNo
Answer = MsgBox("Process the XYZ Report?", UserInput)
If Answer = vbYes Then ProcessReport
End Sub

Sub ProcessReport()
MsgBox ("Thanks for processing the XYZ Study.")
End Sub

Save and navigate back to the Developer tab of Excel and select the "Button" option. Click on a cell and assign the UserReportQuery macro to the push.

Now click the button. This message should brandish:

Click "yeah" or hit Enter.

Once again, tada!

Please note that the secondary subroutine, ProcessReport, could exist anything. I'll demonstrate more possibilities in example #3. But first...

How is it doing that?

This instance builds on the previous example and has quite a few new elements. Allow'due south get over the new stuff:

• Dim UserInput As Long: Dim is curt for "dimension" and allows you lot to declare variable names. In this example, UserInput is the variable name and Long is the data type. In plain English, this line means "Hither's a variable called "UserInput", and it'due south a Long variable type."
• Dim Reply As Integer: declares another variable called "Answer," with a information type of Integer. Learn more than about information types here.
• UserInput = vbYesNo: assigns a value to the variable. In this example, vbYesNo, which displays Yes and No buttons. There are many button types, learn more than here.
• Answer = MsgBox("Process the XYZ Written report?", UserInput): assigns the value of the variable Answer to exist a MsgBox office and the UserInput variable. Yes, a variable within a variable.
• If Answer = vbYes And then ProcessReport: this is an "If argument," a provisional statement, which allows us to say if x is truthful, then do y. In this case, if the user has selected "Yes," so execute the ProcessReport subroutine.

When could I use this?

This could be used in many, many ways. The value and versatility of this functionality is more then defined by what the secondary subroutine does.

For example, maybe you lot take a file that is used to generate three different weekly reports. These reports are formatted in dramatically different ways.

• Problem: Each fourth dimension one of these reports needs to be generated, a user opens the file and changes formatting and charts; so on and and so forth. This file is beingness edited extensively at least 3 times per week, and it takes at least 30 minutes each time it'due south edited.
• Solution: create 1 push per report type, which automatically reformats the necessary components of the reports and generates the necessary charts.

Real World Examples

• Create a dialog box for user to automatically populate sure information across multiple sheets
• Use the InputBox part to get information from the user, which is and then populated across multiple sheets

Case #3: Add Numbers to a Range with a For-Next Loop

For loops are very useful if you need to perform repetitive tasks on a specific range of values - arrays or cell ranges. In plain English, a loop says "for each x, practise y."

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don't paste!):

Sub LoopExample()
Dim X Every bit Integer
For X = 1 To 100
Range("A" & Ten).Value = X
Next Ten
Terminate Sub

Save and navigate back to the Developer tab of Excel and select the Macros button. Run the LoopExample macro.

This should happen:

Etc, until the 100th row.

How is it doing that?

• Dim X As Integer: declares the variable Ten every bit a data type of Integer.
• For X = ane To 100: this is the start of the For loop. Simply put, it tells the loop to go on repeating until X = 100. 10 is the counter. The loop will keep executing until Ten = 100, execute 1 concluding fourth dimension, and then stop.
• Range("A" & X).Value = X: this declares the range of the loop and what to put in that range. Since X = i initially, the first cell will be A1, at which point the loop volition put X into that cell.
• Next X: this tells the loop to run once more

When could I utilize this?

The For-Adjacent loop is one of the about powerful functionalities of VBA; there are numerous potential apply cases. This is a more complex case that would require multiple layers of logic, only it communicates the world of possibilities in For-Next loops.

Maybe you accept a list of all products sold at your bakery in Column A, the type of production in Column B (cakes, donuts, or muffins), the cost of ingredients in Cavalcade C, and the market place average price of each production type in another sail.

You lot demand to figure out what should be the retail price of each product. You're thinking it should be the cost of ingredients plus 20%, but also 1.2% under market boilerplate if possible. A For-Side by side loop would let you to practise this type of adding.

Real Earth Examples

• Use a loop with a nested if statement to add specific values to a separate array simply if they meet certain conditions
• Perform mathematical calculations on each value in a range, east.g. calculate additional charges and add them to the value
• Loop through each character in a string and extract all numbers
• Randomly select a number of values from an assortment

Conclusion

Now that we've talked about pizza and muffins and oh-yeah, how to write VBA code in Excel spreadsheets, permit's do a learning check. See if y'all can answer these questions.

• What is VBA?
• How do I become set upward to offset using VBA in Excel?
• Why and when would you use VBA?
• What are some problems I could solve with VBA?

If you accept a fair idea of how to you could respond these questions, and so this was successful.

Whether you're an occasional user or a power user, I promise this tutorial provided useful information nigh what can be accomplished with merely a fleck of code in your Excel spreadsheets.

Happy coding!

Learning Resources

• Excel VBA Programming for Dummies, John Walkenbach
• Get Started with VBA, Microsoft Documentation
• Learning VBA in Excel, Lynda

A flake about me

I'1000 Chloe Tucker, an artist and developer in Portland, Oregon. As a former educator, I'm continuously searching for the intersection of learning and instruction, or engineering science and art. Attain out to me on Twitter @_chloetucker and bank check out my website at chloe.dev.

---

---

Learn to code for complimentary. freeCodeCamp'southward open source curriculum has helped more 40,000 people get jobs as developers. Become started

geislerkinger.blogspot.com

Source: https://www.freecodecamp.org/news/excel-vba-tutorial/

Share this post