2025-01-21 About Visual Basic for Applications in MSExcel - Excel-VBA

We are sure our esteemed readers are quite aware of what Visual Basic For Applications (VBA) is and how it can be accessed from the Microsoft Excel interface, still, to put the basics into the right perspective, we shall revise the same. So, first you have to learn how to enable the Developer's Menu in Microsoft Excel. So first you have to go to the main menu of excel and then scroll down to the option menu, as shown in the below Figure 1. On the bottom left hand corner you will find Options, with the quick access short-cut key as "T".

Figure 1 - Main Menu of Excel

Okay, now that you have discovered the Options menu, you just click it and VOILA, the options Dialog Box appears, as shown in Figure 2 below.

Figure 2 - Options Dialog Box

In the options Dialog Box, you can see that the "Customize Ribbon" option has already been selected in the left hand menu (appearing under 'Excel Options'). If the right hand 'Customize the Ribbon' does not show "Developer", as it does in the above figure, it could be that your graphic resolution may be a little lower than what is needed to display, in which case you just have to scroll down under the "Main Tabs", till you come to "Developer", nestled between "Automate" and "Add-Ins" options. By default, it would be unchecked, so you just have to click on it with your mouse, or navigate with your keyboard arrows and press space-bar. VOILA, the Developer menu will appear in your main Excel Interface, as shown in Figure 3 below.

Figure 3 - Excel Main Interface

That is all that you need, to enable "Developer" menu. In order to start coding in the Visual Basic Environment (VBE), all you have to do is to click on the "Visual Basic" icon that appears on the extreme left corner of the "Developer" menu.

Figure 4 - Developer Menu Selected

Okay, so now, when you click Visual Basic, what happens is that a new windows opens up, as shown below.

Figure 5 - Visual Basic Environment Window

In this window, you can finally start coding. At this point of time, all we can say is that using the "Insert" Menu, you can insert a new Module, Class Module or Userform, which will have to be coded, for it to work, and you have to plan your code in advance before you can start designing the Userform. The Class Module is something that you will learn in the "Advanced VBA Training" sessions, while in the "Basic VBA Training" you will receive training on how to use the "Macro Recorder" in MS Excel to create a new module and later how to code your own module(s) to create "User Defined Functions (UDF)" and to create various Sub-Procedures for carrying out an instruction on the given data.

That's all, folks, for this post. Apply now for the "Visual Basic for Applications (Excel-VBA)" course today itself by logging onto "Excel Trainer Ahmedabad Blog", where the link to join the classes will be avaliable after January 30th, 2025. This facility is also likely to be made available at my Excel-VBA-AHMFCA Website too, so be dure to check there as well. Cheers !!

CA Vikram Shankar Mathur
cavs.mathur@gmail.com

+91-9998090111 / +91-8460890111

=============================================================

20191118-Running-Totals-Formulas-Excel-Worksheet

20191118-Running Total Formula in Microsoft Excel Worksheet for Computation of Total Income of an Assessee

I have already made a similar post in my Wordpress Blog as VBA Beginners Guide - (November 2019), but here is a more refined version of the same with detailed explanations about the need to create such a running formula.

What is a "Running Total" and when is it significant in an Excel Worksheet?

The above image is the last part of the image that was added to the final image right at the top of this page, replicated here to ease the explanation of why a "running total" can be required in an excel worksheet. Suppose you had a worksheet, as in my case it is a worksheet that computes the Total Income of an Income-Tax Assessee for the Financial Year ended 31-March-2019, wherein the various allowed heads under Section 80C, namely, "Public Provident Fund Contribution", "Life Insurance Premium", "Children's Education Tuition Fees" etc. are entered from Row Number 40 through to 47, in Column E of the worksheet, the running total being calculated in the Column F (Qualifying Deduction), that is, the column immediately following the entry column E (Gross Deduction). As you can see from the figure above, the starting row has been frozen, as in E$40 (semi-absolute reference restricted to the row only). Don't worry about the formula itself, it is replicated in a copy-able format at the end of this post, so just follow along with the narative at the moment.

Therefore, freezing the row in the formula achieves the desired result of a "Running Total" in the sense that it can be copied as it is from Cell E40 down to Cell E47, giving us the desired result. Now as to why it is required, I wanted to have an idea how much of each amount entered in the Gross Deduction Amount was going to be allowed as a Qualifying Deduction Amount, so that a greater analysis of the same could be done while I was entering the details of the dedudction(s) per assessee.

Discussion about the Break-up of the formula itself

The first part of the formula [=IF(E47=0,0,IF(SUM(E$40:E47)=0,0] is easy to understand. If the entered Gross Deduction amount in cell E47 is zero, then obviously the Qualifying Deduction amount in cell F47 has to be zero. Since the construction of an "=IF(" formula is of the type "=IF(<Condition>, <True Part>, <False Part>)" the <Condition> being "E47=0",  the <True Part> being "0" and the <False Part> beginning as "IF(SUM(E$40:E47)=0,0".

Before I progress further, an explanation of "Sec80CLimit" is required, for it comes up in the next part of the formula. Elsewhere in my worksheet, I have defined a Range Name as Sec80CLimit, which bears a value of Rs. 1,50,000/-, the current maximum allowable limit under Section 80C of the Income-Tax Act, 1962. A similar logic follows the second part and third part of the formula. Essentially, you need to view the formula in basically three parts (<Condition>'s).

The first part (<Condition>) being if the entered Gross Deduction amount is Zero (E47=0), the second part (<Condition>) being if the entered Gross Deduction amount is less than or equal to the Sec80CLimit defined above (SUM(E$40:E47)<=Sec80CLimit) and the last part (<Condition>) being if the entered Gross Deduction Amount is greater than the Sec80CLimit (IF(SUM(E$40:E47)>Sec80CLimit).

And finally, the entire formula
[=IF(E47=0,0,IF(SUM(E$40:E47)=0,0,IF(SUM(E$40:E47)<=Sec80CLimit,E47,IF(SUM(E$40:E47)>Sec80CLimit,Sec80CLimit-SUM(F$39:F46),0))))] 
is replicated in a copy-able format, as promised above.

CA Vikram Shankar Mathur
18-Nov-2019 | 00:10 Hours IST  - Updated on 21-Jan-2025 | 02:07 Hours IST

HelloWorld-Macro

Hello World Macro

This would easily be the easiest first macro for anybody to create, especially if one is just beginning to learn how to program macro's in the Visual Basic Editor in Microsoft Excel (Any version). The first step is to create a workbook and call it "Hello_World_Macro.xlsm" and save it to your computer in your favoured location, usually "My Documents".

STEP 1: Create a Shape or WordArt:

Refer to Figure 1 above, in this case I have used a WordArt Text on the worksheet from where I want to execute the macro.

STEP 2: Add the following code:

Sub Hello_World()
'---------------------------------------------------------------------------------------
' Procedure : Hello_World
' Author    : vsmathur-onms
' Date      : 17/10/2019
' Purpose   :
'---------------------------------------------------------------------------------------
'

   On Error GoTo Hello_World_Error

    MsgBox "Hello World !!", vbOKOnly

Hello_World_Exit:

    On Error GoTo 0
    Exit Sub

Hello_World_Error:

    MsgBox "Error " & Err.Number & " on Line # " & Erl & " (" & Err.Description & ") in procedure Hello_World of Module m_HelloWorld"
    Resume Hello_World_Exit


End Sub

STEP 3: Assign Macro and Click to Execute Macro

You can right-click the WordArt created in Step 1 above and select "Assign Macro" - in the dialog box that opens, select the Sub called "Hello_World". Finally, click the WordArt and you should see a Message Box appear with "Hello World !!" written in it. There, that is all that there is to creating your very first Macro!! Cheers !!

CA Vikram S. Mathur17-Oct-2019 | 10:15 Hours IST

Beginning to learn VBA

Beginning to learn VBA

CA Vikram Shankar Mathur

This document should get you started with how to program in the Visual Basic for Applications (VBA) in Microsoft Excel and the other MSOffice365 products straight off. Cheers !

So,  what is VBA?

Visual Basic for Applications (VBA) Introduced:

When you are in any of the three applications mentioned above, and specifically in Microsoft Excel, pressing [Alt]+[F11]  would bring you into what is called the VBE (Visual Basic Editor) window, where you would see what would look somewhat like the figure below.

Visual Basic Editor (VBE)

Want to insert a picture from your files or add a shape, text box, or table? You got it! On the Insert tab of the ribbon, just tap the option you need.

The ‘elusive’ Developer Tab

Oh, before I forget, you will need to make the ‘Developer’ tab visible in your Microsoft Office (Excel/Word) application, if you wish to quickly access the VBA Window from the main application. To do this, you need to access [File->Options->Customize Ribbon] to reach the screen depicted below:

Why Learn VBA?

Basically, VBA is not meant for the weak-hearted or the person who wants thing-done-0in-a-jiffy!! To elucidate the point, it took me a good 15+ years to evolve to the point that I could consider myself worthy of making this first post on any of my blogs. Admittedly, I got a hefty kick-start in the start of my career in 1989, when my boss insisted that I prepare all my documentation to be submitted to him on the computer. At that time, we only had WordStar as the word processor, Lotus 1-2-3 as the spreadsheet and dBase/FoxBase as the database programs.

Well, let me not digress here, to conclude this post, I evolved into the Microsoft Excel’95 world without much effort, thanks to my experience of Macro-Programming in Lotus 1-2-3. The real question here, however, is where do you (the layman reading this blog, not the experts who read it for whatever reason 😊 😊 ). I will answer that question in my next post, where I will go one step ahead in getting you stated on actually writing your first “Macro” in either Microsoft Excel or Microsoft Word.

Thanks for reading so patiently.

CA VIKRAM S. MATHUR

03-Sep-2019 | 13:50 Hours IST