Before Excel 2003, you could get a complete list of Visual Basic Functions by looking in Visual Basic Help > Contents > Functions. In Excel 2010, entering “Functions” into the Help Search box produces nothing useful. Using a specific category, e.g. “Math Functions”, may be more productive. Please be aware of what references to “Excel…
It is difficult to make your program foolproof, because fools are so ingenious! Even so, one should make an effort to trap and handle errors. You should not let the program simply crash if there is a problem, whether due to bad data, faulty calculation, or whatever. This is what we call Error-Handling. Excel Visual…
1. MsgBox MsgBox (Message Box) brings up a dialog box with a title bar, a message of our choice, and a choice of icons and buttons. The function can return a value that tells us which button the user clicked. We have seen MsgBox in passing in earlier posts. Here is the syntax of the…
In Alice in Wonderland by Lewis Carroll, at the trial of the Knave of Hearts, the White Rabbit asks the King how to read the evidence. “Begin at the beginning, the King said, very gravely, and go on till you come to the end: then stop.” It would be easy if all computer programs did…
In this post, we will give you some more of the basic (forgive the pun) building blocks of the Visual Basic programming language. We will cover formulas, assigning values, logical comparison, constants, comments, and continuation lines. We conclude with two small examples. One of them demonstrates our first Error Trap. 1. Formulas We have already…
In the Excel Visual Basic Editor (Alt+F11), display the Project Explorer (Ctrl+R). Usually (and in this picture), it appears at the top left, under the toolbars: In Project Explorer, you will see a VBAProject for every Excel file that is open. The list will include the hidden Personal Macro workbook, Personal.xlsb, if you have personal macros….
At this point, you might be wondering, “What if I get stuck?” Fear not, help is at hand! Microsoft offers extensive online documentation of the VB language. To be fair and warn you, it’s often not that useful. You might find more assistance if you Google for your topic. There is a lot of programming…
In an earlier post, we recorded two Excel macros in Visual Basic, and ran them. Then, in the next post, we viewed the code in the Excel Visual Basic Editor. Code you have recorded will usually run. However, you may want to change it for all sorts of reasons. To tidy it up, make it…
The Excel Visual Basic Editor can be set to appear in a variety of ways. In the appearance shown below (the default) the majority of windows appear docked at one edge of the screen. Code windows can be tiled together on the screen, or one can be maximized. The main features of the screen are…
The macros you recorded in the previous post are stored in an Excel file. To edit it you use what looks like a separate program, the Visual Basic Editor. Open it with the Developer ribbon (Code group) > Visual Basic Editor (Alt+F11). You will find newly-recorded macros under Modules in the explorer on the left…