Excel VBA - Step by Step Guide To Learning Excel Programming Language For Beginners (Jason Jay) (Z-Library)

📄 File Format: PDF

💾 File Size: 1.2 MB

📖 Read Online ⬇️ Download

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