Skip to content

5000+ Great Articles

Best Articles & Essays: Interesting Articles to Read Online

  • How to Update NVIDIA Drivers for Best Performance How-To
  • BSD vs Linux: The Basic Differences Linux Tips
  • Create a Windows 10 System Image Backup Featured Posts
  • 9 Best Free Calling Apps For Unlimited Calls & Texts

    Keeping in touch with your loved ones doesn't have to be costly, and if you look in the right places it definitely won't. We're of course talking ab

  • How to Change Background Name on Zoom Computer Tips
  • Top 10 Google Calendar Tips 2021 Google Software/Tips
  • 7 Ways To Free Up Hard Disk Space On Windows How-To
  • What To Do If The Windows Store Won’t Open Help Desk

The Best VBA Guide (For Beginners) You’ll Ever Need

Posted on October 7, 2020 By blog_786 No Comments on The Best VBA Guide (For Beginners) You’ll Ever Need

The VBA programming platform, which works in almost all Microsoft Office products, is one of the most powerful tools anyone can use to improve the use of these products.

This VBA Beginner’s Guide will show you how to add a developer menu to an Office application, how to enter the VBA editor window, and how basic VBA statements and loops work so you can get started using VBA in Excel, Word, Powerpoint, Outlook, and OneNote.

The Best VBA Guide (For Beginners) You’ll Ever Need

This VBA tutorial uses the latest version of Microsoft Office products. If you have an older version, you may see slight differences from the screenshots.

How to enable and use the VBA Editor

You may notice that in any of the Office products used in this tutorial, you do not have the specified developer menu. The developer menu is only available in Excel, Word, Outlook and Powerpoint. OneNote does not offer a tool for editing VBA code from within an application, but you can still reference the OneNote API to interact with OneNote from other Office programs.

You will learn how to do this in our upcoming advanced VBA tutorial.

  • To enable the developer menu in any office product, select the File menu and select Options from the left navigation menu.
  • You will see the Options pop-up menu. From the left navigation menu select Customize Ribbon.

The Best VBA Guide (For Beginners) You’ll Ever Need

The list on the left shows all the available menus and menu commands available in that Office application. The list on the right is the ones that are currently available or activated.

– /

  • You should see the Developer listed on the right, but will not be activated. Just check the box to activate the developer menu.

  • If you don’t see the developer available on the right, change the left option Select commands from the dropdown to All commands. Find Developer in the list and select Add >> Center to add this menu to the ribbon.
  • Click OK when done.
  • When the Developer menu is active, you can return to the main application window and select Developer from the top menu.
  • Then select View Code from the Controls group on the Ribbon to open the VBA Editor window.

The Best VBA Guide (For Beginners) You’ll Ever Need

  • This will open a VBA editor window, where you can enter the code that you will explore in the next few sections.

The Best VBA Guide (For Beginners) You’ll Ever Need

  • Try adding a developer menu to a few Office apps you use every day. When you’re comfortable with opening the VBA editor window, skip to the next section of this tutorial.

The Best VBA Guide (For Beginners) You’ll Ever Need

General VBA Programming Tips for Beginners

You will notice that when you open the VBA editor, the navigation options in the left pane differ from one Office application to the next.

The Best VBA Guide (For Beginners) You’ll Ever Need

This is because the available objects that you can put VBA code in depend on what objects are in the application. For example, in Excel, you can add VBA code to workbook or worksheet objects. In Word, you can add VBA code to documents. In Powerpoint for modules only.

So don’t be surprised at the different menus. The structure and syntax of VBA code is the same for all applications. The only difference is the objects you can reference and the actions you can take on those objects using VBA code.

Before we dive into the various objects and the actions you can perform on them with VBA code, let’s first take a look at the most common VBA structure and syntax that you can use when writing VBA code.

Where to put the VBA code.

When you are in the VBA editor, you need to use the two dropdowns at the top of the edit window to select which object you want to attach the code to and when you want the code to run.

For example, in Excel, if you select Worksheet and Activate, the code will run whenever the worksheet is opened.

The Best VBA Guide (For Beginners) You’ll Ever Need

Other worksheet actions you can use to run VBA code include: when the worksheet is modified, when it is closed (deactivated), when the worksheet is calculated, etc.

When you add VBA code to the editor, always place the VBA code on the object and use the correct action that you want to use to run that code.

IF VBA statements

The IF statement works in VBA just like it does in any other programming language.

The first part of the IF statement checks if a condition or set of conditions is true. These conditions can be combined with the AND or OR operator to link them together.

One example would be to check if a grade in a spreadsheet is above or below a “passing” grade and assign a pass or fail status to another cell.

If Cells (2, 2)> 75, then Cells (2, 3) = “Pass”, Else Cells (2, 3) = “Fail”

If you don’t want the entire statement to be on one line, you can split it over multiple lines by adding the “_” character at the end of the lines.

If Cells (2, 2)> 75, then _
Cells (2, 3) = “Pass”, otherwise _
Cells (2, 3) = “Fail”

Using this technique can often make your code much easier to read and debug.

VBA For Next Loops

IF statements are great for single comparisons, as in the single cell view example above. But what if you want to loop over the entire range of cells and execute the same IF statement for each one?

In this case, you need a FOR loop.

To do this, you will need to use the length of the range and iterate over that length by the number of lines containing the data.

To do this, you need to define the range variables and cells and loop through them. You also need to define a counter so that you can output the results to the appropriate line. So your VBA code will have this line first.

Dim rng As Range, cell As Range
Dim rowCounter as Integer

Determine the size of the range as follows.

Set rng = Range (“B2: B7”)
rowCounter = 2

Finally, you can create a FOR loop to loop through each cell in that range and perform a comparison.

For each cell in rng If cell.Value> 75 Then _ Cells (rowCounter, 3) = “Pass” Else _ Cells (rowCounter, 3) = “Fail” rowCounter = rowCounter + 1 Next cell

After running this VBA script, you will see the results in a real spreadsheet.

Loops while in VBA

The Best VBA Guide (For Beginners) You’ll Ever Need

The While Loop also loops through a series of statements just like a FOR loop, but the condition to continue the loop is a condition that remains true.

For example, you can write the same FOR loop above as the WHILE loop by simply using the rowCounter variable as follows.

While rowCounter 75 Then _ Cells (rowCounter, 3) = “Pass” Else _ Cells (rowCounter, 3) = “Fail” rowCounter = rowCounter + 1 Wend

Note. The completion constraint rng.Count + 2 is required because the row count starts at 2 and must end on line 7 where the data ends. However, the range counter (B2: B7) is only 6, and the While loop will only end when the counter becomes MORE than the counter, so the last rowCounter value must be 8 (or rng.Count + 2).

You can also customize the While Loop as follows:

While rowCounter <= rng.Count + 1

You can only increment the counter of range (6) by 1, because when the rowCounter variable reaches the end of the data (line 7), the loop might end.

VBA Do While and Do While Loops

Bye and Bye loops are almost identical to Bye loops, but they work a little differently.

  • The Bye Loop checks if a condition is met at the beginning of the loop.
  • The Do-While Loop checks if a condition is true after executing statements in the loop.
  • The Do-until loop checks to see if the condition remains false after the loop has completed.
  • In this case, you could rewrite the above While Loop as follows as a Do-While Loop.

    Do If Cells (rowCounter, 2)> 75 Then _ Cells (rowCounter, 3) = “Pass” Else _ Cells (rowCounter, 3) = “Fail” rowCounter = rowCounter + 1 Loop while rowCounter

    In this case, the logic doesn’t change much, but if you want to make sure that the boolean comparison happens after all statements have been run (allowing them all to run no matter what at least once), then Do-While or Do-until loop is correct option.

    VBA Select Case Statements

    The last type of logical operator you need to understand in order to start structuring your VBA code is the Select Case statements.

    In the example above, let’s say you want to have an assessment method that does not only allow passing an exam. Instead, you want to assign a letter grade from A to F.

    You can do this with the following Select Case statement:

    For each cell In rng Select Case cell Case 95 to 100 Cells (rowCounter, 3) = “A” Case 85 to 94 Cells (rowCounter, 3) = “B “Case 75 to 84 cells (rowCounter, 3) =” C “Case 65 to 74 cells (rowCounter, 3) =” D “Case 0 to 64 cells (rowCounter, 3) =” F “End Select rowCounter = rowCounter + 1 Next cell

    The resulting table after running this VBA script looks like the following.

    The Best VBA Guide (For Beginners) You’ll Ever Need

    Now you know everything you need to know to start using VBA in your Microsoft Office applications.

    –

    Share this:

    • Twitter
    • Facebook
Office Tips

Post navigation

Previous Post: How To Install & Run Linux Apps On a Chromebook
Next Post: How to Factory Reset Amazon Echo Devices

Related Posts

  • How To Fix Outlook Keeps Asking For Password Issue Office Tips
  • What Is a VBA Array in Excel and How to Program One Office Tips
  • How to Delete Blank Lines in Excel Office Tips
  • Use the Keyboard to Change Row Height and Column Width in Excel Office Tips
  • Change the Layout of a PowerPoint Slide with Just a Few Clicks Office Tips
  • How to open MSG file on Windows and Mac Office Tips

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Archives

  • October 2023
  • September 2023
  • August 2023
  • July 2023
  • June 2023
  • May 2023
  • April 2023
  • November 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • March 2021
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • December 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018

Categories

  • AI Tools & Guides
  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Featured Posts
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • islamic Books
  • Linux
  • Linux Tips
  • Mac OS X
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Windows XP Tips
  • Wordpress
  • cat5 vs cat5e vs cat6 vs cat6a vs cat7 – Which Ethernet Cable to Use?
  • 15 Best Legal Torrenting Sites to Download Content Safely
  • Review of the ReMarkable 2.0 tablet with Type Folio keyboard
  • Flixable Helps You Find the Best Movies and TV shows on Netflix
  • How to Watch Winter Olympics 2018 from Anywhere
DMCA.com Protection Status

Recent Posts

  • How to Watch Netflix in 1080p on Chrome and FireFox
  • cat5 vs cat5e vs cat6 vs cat6a vs cat7 Which Ethernet Cable to Use?
  • 15 Best Legal Torrenting Sites to Download Content Safely
  • Review of the ReMarkable 2.0 tablet with Type Folio keyboard
  • Flixable Helps You Find the Best Movies and TV shows on Netflix

Recent Comments

  1. Flixable Helps You Find the Best Movies and TV shows on Netflix on SmartDNS vs VPN “What’s the Difference?”
  2. 5 Sites That Are Like Audible, But Free on Top 4 Amazon Price Tracker Tools
  3. 5 Sites That Are Like Audible, But Free on 14 Best Free Audiobooks on Audible – 2022
  4. How to Reduce PNG File Size of a Photo on How to Convert a Screenshot to a Jpeg on a Mac
  5. 3 Sites like YouTube to Earn Money With Your Videos on Here are the Top 10 highest paid YouTubers of 2013
  • How To Hard Reset a Frozen iPhone or Android Device How-To
  • How To Play MKV Files On Windows Media Player Software Reviews
  • How to Convert Picture to Excel Table in a Blink of an Eye How-To
  • How long does Oculus Quest 2 battery last? Gaming
  • What Are The Options For Gamers With Disabilities? Gaming
  • Best ti89 titanium graphing calculator 2020

    Best ti89 titanium graphing calculator 2020. 1 - EZ Graphing Strong Pink Hard Slide Cover (See Description for Details) for TI 84 Plus, TI 84 Plus C

  • Best 13 Funny Prank Sites to Fool Your Friend 2022 Cool Websites
  • How to pair an Apple Watch with a new phone Apple Watch

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version