Category: General

Creating VBA in Microsoft Excel 2010

Adding an active x control in Microsoft Excel 2010 is not as obvious as in the earlier versions of MS Excel. The default worksheet in Microsoft Excel 2010 does not show the controls.

In order to bring up the controls, you need to make the developer tab visible. To display the developer tab, click File and then click Options. In the Options dialog, click Customize Ribbon and under Main Tabs check the Developer box, as shown in the Figure below:

showdeveloper

 

Now your Microsoft Excel workbooks shows the Developer tab and the ActiveX controls are available there, as shown in the Figure below:

showdeveloper_xcel

 

Formatting Font and Background Colors in Excel Spreadsheet

We can write Excel VBA code to format the color of a MS Excel spreadsheet. Using  Excel VBA code, we can change the font color as well as the the background color of each cell effortlessly.

Alright, let’s create a program that can format random font and background colors using a randomize process. Colors can be assigned using a number of methods in Excel VBA, but it is easier to use the RGB function. The RGB function has three numbers corresponding to the red, green and blue components. The range of values of the three numbers is from 0 to 255. A mixture of the three primary colors will produce different colors.

More details here:

http://excelvbatutor.com/index.php/excel-vba-lesson-12/

Creating Animation in Excel VBA

Though generally we create Excel VBA code for mathematical and financial calculations, it is also possible to creating some fun applications in Excel VBA, including games and animation. Although professionals programmers might not be interested to write such applications, it is worth while trying them out as a hobby and for personal satisfaction.

Visit this link to find out more:

http://excelvbatutor.com/index.php/excel-vba-lesson-22/

Formatting Functions in Excel VBA

Formatting output is a very important part of Excel VBA programming so that the data can be presented systematically and clearly to the users. Data in the previous lesson were presented fairly systematically through the use offunctions like Int, Fix and Round. However, to have better control of the output format, we can use a number of formatting functions in Excel VBA.

Read more about formatting functions at:

http://excelvbatutor.com/index.php/excel-vba-lesson-9/

Excel VBA Functions

In Excel VBA, a function is similar to a procedure but the main purpose of the function is to accept a certain input from the user and return a value which is passed on to the main program to finish the execution. There are two types of functions, the built-in functions (or internal functions) and the functions created by the programmers, or simply called user-defined functions. We shall deal with built-in functions in this lesson and the user-defined functions in the next lesson.

The first built-in function that we have already learned and familiar with its usage is the Message Box. We are not going to repeat here but we shall take a look at its syntax once more, i.e.

message=MsgBox(Prompt, Style Value,Title)

Now we shall examine the next commonly used function in Excel VBA, it is none other than the InputBox function.

Read more about functions at: http://excelvbatutor.com/index.php/excel-vba-lesson-8/

Excel VBA Lesson 1: Introduction to Excel VBA

[Contents] <<Lesson 2>>

1.1 What is Excel VBA?

Excel VBA is the acronym for Excel Visual Basic for Applications. It is an integration of the Microsoft’s event-driven programming language Visual Basic for Microsoft Office Applications such as Microsoft Excel. Using the Visual Basic programming language in Excel VBA, you can write customized solutions for the Microsoft Excel spreadsheet and complement its built-in functions. In addition, you can actually learn the fundamentals of Visual Basic programming without having to buy a copy of Visual Basic professional. Why is it so? It is because there is a built-in Visual Basic Editor in Microsoft Excel.


1.2  How to write Excel VBA Code?

There are two ways which you can write EXCEL VBA code. The first one is to place a command button on the spreadsheet and start writing codes by clicking the command button while the second one is to write Visual Basic codes within the Visual Basic Editor. Let’s start with the command button first. In order to place a command button on the spreadsheet, you need to click View on the MS Excel menu bar and then click on the toolbar and finally select the Control Toolbox to launch the control toolbox bar, as shown in Figure 1.1.

Excel VBA
Figure 1.1

The control toolbox comprises various controls. In this lesson, you only use the command button. Now click on the command button and draw it on the spreadsheet, as shown in Figure 1.2


Figure 1.2




Next,  click on the command button to bring up the Visual Basic Editor. In the Visual Basic Editor,  enter the statements as shown in Figure 1.2. The first statement fills up cell A1 to cell A10 with the phrase “Visual Basic” while the second statement adds the value in cell A11 and cell B11  and then shows the sum in cell C11. It is that simple.

Figure 1.2

Run the macro and you can see the output as shown in Figure 1.3  below:

Figure 1.3

[Contents] <<Lesson 2>>

Welcoming Message from the Webmaster

We have transferred the entire Excel VBA tutorial website to this new domain. The main objective is to provide a more user-friendly learning environment to learn Excel VBA macro programming. It is still in the building process, we hope that you can provide your useful feedbacks for us to build a better website. Thank you!

Excel VBA Tutorial

Excel VBA Tutorial provides step by step guide in learning VBA for the older versions of MS Excel such MS Excel 2003. We provide a separate VBA tutorial for the newer MS Excel 2010.  Start learning how to create VBA in Microsoft Excel by following the lessons below.

Table of Contents

Lesson 1: Introduction to Excel VBA
Lesson 2: Working with Variables
Lesson 3: Working with Arrays
Lesson 4:  Operators
Lesson 5: Sub Procedures and Functions
Lesson 6: Mathematical Functions
Lesson 7: Trigonometric Functions
Lesson 8: String Manipulating Functions
Lesson 9: Formatting Functions
Lesson 10: Financial Functions
Lesson 11: Date and Time Functions
Lesson 12:Using If Then Else
Lesson 13: Select Case
Lesson 14: Looping
Lesson 15: Do Loop
Lesson 16: Formatting Font and Background Color
Lesson 17: Introducing Excel VBA Object2 Part 1
Lesson 18: Introducing Excel VBA Objects Part 2
Lesson 19: The Range Object
Lesson 20: The worksheet Object
Lesson 21: The workbook Object
Lesson 22: Working with Check Box and Option Button
Lesson 23: Working with List Box, Combo Box and Toggle Button
Lesson 24: Creating Charts and Graphs in Excel VBA
Lesson 25: Creating Animation in Excel VBA



Excel VBA Tutorial provides plenty of useful lessons written in a straightforward manner to assist you in mastering Excel VBA macro programming. Our lessons cover Excel VBA programming for the older versions of Excel as well as the latest Microsoft Excel 365. However, the syntaxes remain the same because they are based on Visual Basic 6, even for the latest versions of Excel. In addition, we also run tutorials on Visual Basic, JavaScript, JQuery, Blockchain, CSS, and HTML.

Our website is hosted on Linux VPS. However, if you prefer a more robust hosting solution, you can opt for the dedicated server.

Our tutor, Dr.Liew has authored a new e-book, the latest Excel VBA 365 Made Easy. This book can be used as a reference book for your excel VBA projects. Please check it out.

Excel VBA 365 Made Easy

ISBN-13: 979-8642584767

Preview the book

Excel VBA 365 Made Easy is a complete guide to mastering Excel VBA 365, for beginner to intermediate programmers. Authored by Dr. Liew, creator of the popular online Excel VBA Tutorial at excelvbatutor.com, this book is an excellent reference text for high school or college-level computer science courses.

By the end of this book, you will gain a comprehensive understanding of basic Excel VBA 365 concepts and be able to create your own code from scratch. You will learn how to:

  • Write code for objects like Worksheet, Range, Cells and more using their methods and properties
  • Write macros to automate tasks
  • Program code for all the ActiveX controls available in the Developer environment
  • Create applications using the UserForm
  • Create objects and classes using the Class module

Best of all, you will gain inspiration from a variety of interesting examples like a calculator, stock trading program, slot machine, Star Wars, and more. You may modify the examples easily to suit your needs.

Also check out our classic Excel VBA book.

Excel VBA Made Easy

Preview the book

What is VBA ?

VBA is the acronym for Visual Basic for Applications.  It is similar to the  Microsoft programming language Visual Basic except that it is embedded within the MS Office applications such as MS Excel, MS Words, MS Access, MS Powerpoint and more.  VBA allows you to create customized programs using the built-in Visual Basic Editor in MS applications.  These programs are also known as macros.  The macros can greatly enhance the functionality of the Microsoft applications.