Excel VBA - Step by Step Guide To Learning Excel Programming Language For Beginners (Jason Jay)（Z-Library）
Other
Shows how to create macros and then write vba (visual basic for applications) code. At the end of each chapter gives a short test review to make sure you remember what you read. All examples are shown in code and evoked with the output.
300
Views
Registered users can read the full content for free
Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.
Page
1
(This page has no text content)
Page
2
EXCEL VBA Step-By-Step Guide To Learning Excel Programming Language For Beginners Jason Jay
Page
3
© Copyright 2017 by Jason Jay - All rights reserved. If you would like to share this book with another person, please purchase an additional copy for each recipient. Thank you for respecting the hard work of this author. Otherwise, the transmission, duplication or reproduction of any of the following work including specific information will be considered an illegal act irrespective of if it is done electronically or in print. This extends to creating a secondary or tertiary copy of the work or a recorded copy and is only allowed with express written consent from the Publisher. All additional right reserved.
Page
4
TABLE OF CONTENT EXCEL VBA 1 Introduction 5 CHAPTER 1 7 VBA Developer TAB 7 Accesing to the Developer TAB 7 Quiz 1 8 CHAPTER 2 9 Macros 9 Creating a Macro 9 Relative References 11 Running the Macro 12 Saving a Macro-Enabled Workbook 12 Quiz 2 14 CHAPTER 3 16 Starting with VBA 16 What if I need to fill the cells up to 100? 19 Insert Form Button 21 Simple things a Macro can’t do. 22 Insert ActiveX Button 22 Variables, Do and Loop. 23 What is the advantage of declaring variables as byte, integer or any other? 31 APPs Performance 35 MSGBOX 39 If and Select Case 40 Quiz 3 43 CHAPTER 4 45 Project: Creating a Simple Calculator using ActiveX 45 What is a Module? 45 How to create a Module 46 Adding Letters? 58 Quiz 4 59 CHAPTER 5 60 Project: Calculator using Forms 60 Review 60 FORMS 61
Page
5
Commmand Buttons code 65 Excel Formulas on VBA 74 Combining VBA and a Spreadsheet 75 Starting with Declarations: 78 Open and Close declarations: Displaying a form without looking any spreadsheet 81 Macro Security 87 Comments 89 The whole code for Calculator Project 94 Command Buttons Order 101 Adding Password to VBA Code. 102 Quiz 5 104 Interacting with other Applications. 105 Opening other apps from Excel 105 Sending an Outlook e-mail from Excel: 108 Exercises Solutions: 111 Answers Chapter 1 111 Answers Chapter 2 112 Answers Chapter 3 113 Answers Chapter 4 114 Answers Chapter 5 115
Page
6
Introduction If you already know how to use Microsoft Excel but there’re a few things you can’t do, it is time to learn the strongest functionality it has, Visual Basic for Applications (VBA). Visual Basic for Applications is a programming language incorporated in Microsoft Excel, Access, PowerPoint and even Word, which let you do all things you already know about them and much more. For example, you want that every time you open a specific Microsoft Word file it writes automatically the current date two lines below where you left last time. Or maybe you want a whole spreadsheet of Excel without formulas on it and still applying them as if they were there. How would you do that? All these things and much more are done with Visual Basic for Applications for Microsoft Office. Look at the example below: It looks like a program made for analysis, and it does. Guess what program it is? Probably you are thinking it is not any Microsoft Office
Page
7
program, but let me tell you that it was made with Microsoft Excel, how would you do something like that without programming? There’s no way! You’ll learn much more than that and will be able to create your own programs using Visual Basic for Applications (VBA). If you need a very specific program for your business analysis, something for personal use, or even just for having fun, you need Visual Basic for Applications now!
Page
8
CHAPTER 1 VBA Developer TAB Every programming language has similarities between their fundamentals. The functions IF, Then, Loop, Close, Open, are just some of them. We’ll learn the basic ones first; it will be necessary to understand how VBA works. We’ll use Microsoft Excel 365 for this instructions and examples, however, from Microsoft Excel 2007 onwards it will work the same. Accesing to the Developer TAB Microsoft Excel doesn’t show the Developer TAB by default. It only has File, Home, Insert, etc. But there’s no one called Developer. To access to this TAB there are different options, but we’ll show the easiest one. 1. Right click to the Ribbon (any part inside the red box, except the buttons). 2. Select Customize the Ribbon 3. Enable the Developer checkbox and click the OK button.
Page
9
4. You should see the Developer TAB available now. Quiz 1 1. How you Access the Developer TAB? a) It is available by default in Excel. b) Right click on the Ribbon, Customize the Ribbon, enable the Checkbox for Developer and Accept. c) Go to file, Options, Advanced and Enable the Developer TAB.
Page
10
CHAPTER 2 Macros Creating a Macro You’ll see a few options available in the Developer TAB, by now we’ll start to use the Record Macro button. A Macro is an automated sequence which will apply every time you play it. Let’s see a practical example of it: Imagine that in your job you do the same process every morning. It takes some valuable time and even you’re getting bored of that. The process is the following: a) You receive a Microsoft Excel file from your boss with some data and you need to write the date using Year, Month and Day in different columns. You do this because it is the format your job needs and you’ve been adding the same values every day for a few years. In this case an semi-automated process would be helpful. Excel gives that option to all of us with Macros. A Macro is a semi-automated process which let you run a specific task using a shortcut. To create a Macro, follow the sequence below: a) Click the Record a Macro Button.
Page
11
b) Write a name for your Macro. (Needed) c) A shortkey which every time you press will Run the Macro. Be careful, don’t add Ctrl + C or Ctrl + v, otherwise it won’t copy or paste anymore, but run the Macro. In case you want a more specific shortcut, hold the shift key as you press a letter. For example, ctrl + shift + c. To make it work, don’t press ctrl as you add a short cut. (Optional) d) Store Macro in: Personal workbook: Will be available for all the files you open with Excel on that computer; New Workbook will be available for a new file only. This workbook, will apply only to the current open file. (Needed to choose one) e) Write a description about what that Macro does. (Optional) f) Click Ok. g) Start doing everything you always do, which would be adding the current date in this case.
Page
12
h) Once you finish, go back to the Record Macro Button, which now is called Stop Recording. Press it and now should be saved. This would be a very simple Macro, it only adds the current date, but what would you think if you also need to import data from a web page which is updated every hour, and need to classify it using a few charts, and you do the same process several times a day. No doubt, a good Macro would be useful. The process to get it any Macro, is the same we’ve followed. There’s only an important thing to consider when creating one, it is to choose between using Relative References or not. Relative References The Relative References button is just below the Record a Macro Button. Once you click on it, it remains active until you click on it again. It is used to record macros in which the process should be applied to different ranges instead of one already set. Its functionality is very useful. A macro recorded without relative references will always repeat the process on the same cells used when recorded. But if you use relative references, the macro will run from the active cell. Using the example above, what If you need the dates written on cells F4:H4 instead of B2:D2? The only thing you should do is to select F4 and run the Macro. Or select any cell you need and run it. But you need to record the Macro using Relative References, and then select the cell and run it, otherwise it wouldn’t work. Running the Macro There are a bunch of ways to run a Macro. Let’s see the first: Run this one by clicking on the Macros Button, then click on it to run it.
Page
13
Maybe it is not as practical as we expected, however, we’ll add more functionality and make it easier to run in the next step. Saving a Macro-Enabled Workbook Once you have added some Macros to your worksheet and try to save it you’ll get a notification like the following: This might be a little tricky, because most people would attempt saving the file without reading this notification: The following features cannot be saved in macro-free workbooks: °VBA project To save a file with these features, click No, and then choose a macro-enabled file in the File Type list. To continue saving as a macro-free workbook, click Yes. Most people would just click Yes, and according to this message they wouldn’t save the file with their Macros, but as Macro free. It would make you lose all your Macro work. To save the Macro, just click No to the message above, then select save as Excel Macro-Enabled Workbook.
Page
14
Click on Save, and it is done! Once you open it again, you should see a message saying Macros have been disabled, and a Button saying Enable Macros. Click on it and won’t have further problems. If you don’t click on it, you won’t be able to work with VBA, at least you enable them on Macro Security in the Developer TAB, or follow the steps on Chapter 5: Macro Security. Quiz 2
Page
15
1. What is a Macro? a) It is an Excel Formula b) It is a shortcut which runs a recorded process. c) It is a built-in process included in Excel. 2. How to create a Macro? a) Clicking on the Visual Basic Button b) Clicking on the Macros Button c) Clicking on the Record Macro Button 3. What is Relative References for? a) It is to Record a Macro without set specific cells. b) It is to Record a Macro with specific cells. c) Without it a Macro isn’t editable. 4. How to Run a Macro? a) Clicking on the Macro Button b) Clicking on the Record Macro Button c) Clicking on the Relative References Button 5. How to save a workbook with Macros? a) Just save the file normally, the Macros will be saved. b) You’ll get a notification, in which we should be denied and then
Page
16
select save as Macro-Enabled Workbook. c) You’ll get a notification, in which we will be notified that we are saving a Macro-enabled workbook, then just accept to save.
Page
17
CHAPTER 3 Starting with VBA Macros are fundamental to be introduced to VBA. Let’s see why by following the process: 1.-Create a new Macro without relative references. 2.-In the process select the cell A1, write a number 1, and press enter. 3.-Stop Recording. Next to the Record Macro button, there’s another one called Visual Basic. Click on it and you’ll see a code like this: Range("A1").Select ActiveCell.FormulaR1C1 = "1" Range("A2").Select Congrats, you have some Visual Basic Code now. It means a few orders: Select Cell A1 Write the number 1 Select Cell A2 It is exactly what we did. But now, we’ll edit the code so that the Macro does
Page
18
something else. If we see, there’s a pattern, which is: select, write, select. So, we could continue the pattern by adding a few more things directly, like this: Range("A1").Select ActiveCell.FormulaR1C1 = "1" Range("A2").Select ActiveCell.FormulaR1C1 = "2" Range("A3").Select ActiveCell.FormulaR1C1 = "3" Range("A4").Select Now that you added this, run the macro, but now you’ll see a second way to run it. Press the green button above the code. Once you click on it go to the Excel Spreadsheet by clicking on the Excel symbol. You’ll see that the cells A1:A3 are filled with the numbers we wrote and the Cell A4 is selected.
Page
19
It may look very complex, and it is like that because humans use to do things that machines wouldn’t. I needed to select cell A1, A2, A3 and son on. But does Excel really need to select it to write a simple number? Let’s try it by adding the following code: Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 That’s it! Excel can skip steps humans can’t. So, it makes it much more faster and easy to accomplish its work. What if I need to fill the cells up to 100?
Page
20
Now, I think that if I need to follow this pattern until A100 is going to be hard. What should I do? Let’s add this: Range("A1:A100") = 1 Oops! It adds a number 1 to all cells from A1 to A100. Let’s try another thing: Range("A1:A100") = 1 + 1 It adds a number two instead. So, How do I tell Excel that I want it to fill cells in sequence? There are several ways. One is to record a Macro and during the process add a number 1 to the cell A1, then hold right click on the small square and scroll down until you select A100. Select fill series. Go to Visual Basic and you’ll see a code like this: Range("A1").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A1:A100"), Type:=xlFillSeries Range("A1:A100").Select
The above is a preview of the first 20 pages. Register to read the complete e-book.
AI Reading Assistant
Whole-book reading guide from stratified index samples; jump to passages in the text
AI guide
【One-Line Pitch】
A hands-on beginner's path into Excel automation: record a macro, read the VBA it generates, then edit that code into something Excel can't record on its own. Best for Excel users who are comfortable with spreadsheets but have never written a line of code.
【Book Arc】
- **Opening (~0%–10%)**: Sets up the environment and the mental model — enabling the Developer tab, recording a first macro, and understanding when relative references matter versus fixed ranges.
- **Early (~10%–30%)**: Bridges recording and programming: opening the Visual Basic editor to inspect recorded code, trimming redundant `.Select` steps, and attaching macros to Form and ActiveX buttons.
- **Middle (~30%–55%)**: Introduces real language fundamentals — variable types and their size limits, the overflow bug that teaches why declarations matter, MsgBox return values, and If/ElseIf versus Select Case.
- **Late (~55%–85%)**: Moves into structure and reuse: Modules, Public vs. Private procedures, calling routines from button click handlers, and building a multi-button calculator project step by step.
- **Ending (~85%–100%)**: Extends outward — macro security settings, comments, password-protecting VBA code, and interacting with other applications such as launching apps and sending Outlook email from Excel.
【Key Takeaways】
- **Macros are the on-ramp, not the destination** (Opening): Recording captures your keystrokes — including your mistakes — so the real skill is reading the generated code and rewriting it. This reframes the whole book: record to learn syntax, then edit to gain power.
- **Relative references decide where a macro runs** (Opening): Without them a macro always replays on the exact cells recorded; with them it runs from the active cell, which is what makes a macro reusable across rows and sheets.
- **Excel doesn't need to select a cell to write to it** (Early): Recorded code is full of `.Select` and `ActiveCell` steps that humans need but the machine doesn't; collapsing them into direct assignments like `Range("A1") = 1` is the first real optimization a beginner makes.
- **Variable types are a correctness issue, not a formality** (Early/Middle): The byte-to-integer overflow example shows how a wrong declaration produces a runtime bug, while an undeclared or Variant variable trades safety for range. Choosing types affects both bugs and speed.
- **Assignment order is a classic beginner trap** (Middle): Writing `Range("A2") + Range("B2") = Range("C2")` fails because the target cell must come first — the book treats this as a rule worth memorizing.
- **MsgBox returns a number, which makes it a decision tool** (Middle): Button choices map to return values (Yes = 6, No = 7, Cancel = 2), so a dialog can drive If/ElseIf or Select Case branching — the foundation of user-facing prompts.
- **Public vs. Private controls reach** (Middle): Public procedures can be called from anywhere and appear in the macro list; Private ones cannot be called from other parts of the code. This is the book's introduction to scope and code organization.
- **Modules plus calling keep projects maintainable** (Middle/Late): Splitting addition, subtraction, division, and multiplication into separate modules and invoking them from button handlers is the book's first lesson in structuring a program rather than piling code into one sheet.
【Reading Tips】
- **Do the quizzes and exercises rather than reading past them.** Each chapter ends with a short review and the answers are collected at the back; the book is built as a workbook, and skimming the questions removes most of its value.
- **Type every code example into Excel yourself.** The book deliberately shows code alongside its output, and several lessons (the overflow bug, the reversed assignment) only land when you reproduce the error.
- **Deep-read the variable-type table and the bug explanations; skim the step-by-step UI click paths** (ribbon customization, button insertion) once you've done them a single time.
- **Treat the calculator project as the checkpoint.** If you can build the buttons, modules, and calling structure without following along line by line, you've absorbed the core of the book.
- **Expect the later material on security, passwords, and other-application integration to be lighter** — read it for awareness rather than mastery.
【Coverage Limits】
This guide is based on stratified excerpts covering the table of contents, the opening chapters, and the middle sections on variables, MsgBox, conditionals, and modules; the excerpts do not cover the full calculator code, the macro security and password chapters, or the Outlook/other-application material in detail, so those sections are described only at the level their headings indicate.
Passage locations
Page 6
is time to learn the strongest functionality it has, Visual Basic for Applications (VBA). Visual Basic for Applications is a programming language incorporate...
View in text
Page 15
References Button 5. How to save a workbook with Macros? a) Just save the file normally, the Macros will be saved. b) You’ll get a notification, in which we...
View in text
Excerpt 3
and press the button. Congrats! You’ve found your first bug! It is an overflow. Bugs are errors when one writes a wrong code. In our example the code works f...
View in text
Excerpt 4
X Case Else MsgBox "There's another value" End Select End Sub This last one occupies much less code. It was easier and better. You can do practically the sam...
View in text
Recommended for You
{{#thumbnailUrl}}
{{/thumbnailUrl}}
{{^thumbnailUrl}}
{{/thumbnailUrl}}
Loading recommended books...
Failed to load, please try again later
Tip the Site
Scan the WeChat Pay or Alipay code to tip. No login required.
WeChat Pay
Alipay