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:
Now your Microsoft Excel workbooks shows the Developer tab and the ActiveX controls are available there, as shown in the Figure below:
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:
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:
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:
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/
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.
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
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.
Run the macro and you can see the output as shown in Figure 1.3 below:
Can you imagine you can create animation in Excel VBA? The answer is YES! Please click on the following link to learn about Animation in Excel VBA.
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!
Visual Basic is a third-generation event-driven programming language first released by Microsoft in 1991. Visual Basic 6 is a user-friendly programming language designed for beginners, and it enables anyone to develop GUI window applications easily. The prerequisite to learn Excel VBA is Visual Basic 6, therefore, if you are new to VB6 programming, we suggest you follow our Visual Basic 6 Tutorial before start learning Excel VBA.
Our tutor Dr.Liew has authored an e-book, Excel VBA Made Easy that can be used as a reference book for your excel VBA projects. The book is also available on your mobile devices and Kindle.
Featured Excel VBA Book
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.