Deadly-VBA.Programmer

A dash of Forensic Audit and Information Security Audit when combined with an accounting expertise and computerisation expertise gives that extra edge in the international market to me. Specific focus was always on developing a chart of accounts that caters to company affairs as well as income-tax audit for both companies and non-companies. Mix all of the above with that knife-cutting Sharp expertise in Visual Basic for Applications and the immense experience that I pack under my belt! A conglomeration of all that results into what? DEADLY-VBA.PROGRAMMER!! HAPPY INDEPENDENCE DAY!!

https://deadlyvbp2016.business.blog

15-Aug-2017 | 11:05 Hours IST

The SaralNew Spreadsheet (Applicable to AY 2003-2004) (Part-I)

Well, today I am going to share for the first time the application that I had built using a mixture of Excel functionality and Visual Basic For Applications (VBA) way back in AY 2003-2004 (FY 2002-2003) for a friend who used it quite extensively to file Income-Tax Returns of more than 100 clients spread over the period of little less than five years.

What I am going to display here in the following paragraphs is the finalized product, which, to my knowledge, still lacked in some ways, but was quite satisfactory to the clients and the Income-Tax Department of that time. The first is the opening screen that you would get as soon as you opened the “Saral New Excel Spreadsheet“.

SplashScreen_Form

Figure 1: The Splash Screen

When you clicked the “Close” button visible above, the following screen would appear.

Main_Application_Screen

Fig 2: The Main Application Screen (Main Menu)

Form here began the actual journey into the software, which was essentially broken down into the steps as defined in the main menu above. The actual data entry was done by clicking the first button “Enter General Information”, clicking which there would be a series of forms that would appear, which I will show one by one henceforth.

The forms themselves and the controls (named in the same way as the labels that appear) are in plain english, so they need no explanations. I am NOT covering the coding that went behind them, because this is a conceptual posting.

Personal_Details_Assessee

Personal_Details2_Assessee

Form16_Details

Form16_Details_Other

Form16A_Details1

Form16A_Details

HouseProperty_Income

OtherIncome

CapGains_BusiIncome

Rebates_Deductions

Rebates_Deductions_Dropdown1

Rebates_Deductions_Dropdown

AdvanceTax_SelfAssessment

Details_Total_Income

Total_Taxes

Miscellaneous_Details

This completed the data entry portion of the Spreadsheet. In my next post (Part-2), I will share the data storage onto the main Control Sheet as I called it then. Of course there were a good 10,000+ lines of coding that went into the working of the program, which may not be possible for me to share, since in any case this is more like a case study and a ‘conceptual’ posting that only wants to demonstrate my programming prowess to you. Cheers till next time.

CA Vikram Shankar Mathur
(aka Deadly-VBA.Programmer)
31-May-2017 | 01:17 Hours IST | #CAVSM

#Basics of #VBA #Programming

The basics of Formatting in Excel

WHAT YOU SEE IS WHAT YOU GET·TUESDAY, 28 FEBRUARY 201716 reads
The Windows GUI, when it was introduced way back in the 1990’s changed a lot of things, especially how the human mind works. However, the first concept of WYSIWYG (What You See Is What You Get) was initially introduced to me while using one of the later versions of Lotus 1-2-3, wherein the usual non-graphical interface of Lotus 1-2-3 was not very appealing when it came to the rendition of Charts and Graphs. However, we are not going to discuss the real appearance of WYSIWYG, which you can find under Wikipedia here.The real reason for this article is therefore, why I chose to name this page as WYSIWYG and not something that is more apparently linked to Microsoft Excel. From the time that Microsoft Excel – I have been creating formats that have been used by the clients that I have had in the past and even today in the present. And basically, what this page tries to emphasise is that any physical format, be it a voucher or an invoice or any other printed document, can be created in Microsoft Excel. With the advent of Laserjet Printers, and now that they are also affordable at home, printing a copy that would not really look that much different than if it were printed by a proper publisher/printing agency, certainly became very much possible.Just as an example, here I will show you a format created by me way back in 2006-2007, the Saral 2D Income-Tax Return, below:

Saral 2D Form, Page 1

Saral 2D Form Page 2

Another example that I have created from scratch for a friend and then it became quite popular with some of my other clients too is the “Cash Payment Voucher” or CPV, for short, shown below. In this format, you had the freedom to make whatever sort of entry you would usually make on a printed CPV and the entry was saved onto another worksheet, which was aptly called the Cash Payment Register. Not only that, you could even retrieve past vouchers onto the voucher format shown below and print it again, if you liked another copy for any reason at all.

Cash Payment Voucher

I think the above two examples illustrate quite beautifully why I have named this page as WYSIWYG.

CA VIKRAM SHANKAR MATHUR 28-Feb-2017 | 16:28 Hours IST

VBA Programming is an art, not a science


Visual Basic for Applications
 (VBA for short) is actually quite an interesting activity for people like me, for whom it is really an art, and not a science. Why, did I hear you ask? Well, because the one thing that I have learnt in my experience of more than 15 years is that it is very important to name your controls in such a way that repetitive referencing becomes a breeze. For example, if I am creating a userform with four fields represented by textboxes, I would name them as follows:

NAME OF THE FIELD NAME OF THE TEXTBOX
Address1 txtAddress1
Address2 txtAddress2
Address3 txtAddress3
Address4 txtAddress4

The reason for doing this is very simple, if I had to code the above, I would create a class called “clsAddress” with the following code:

Set clsAddress.Address1 = Userform1.txtAddress1
Set clsAddress.Address2 = Userform1.txtAddress2
Set clsAddress.Address3 = Userform1.txtAddress3
Set clsAddress.Address4 = Userform1.txtAddress4

And to put the fields back into the used database after the editing is over, one could use:

Set/Let Database.Address1 = clsAddress.Address1
Set/Let Database.Address2 = clsAddress.Address2
Set/Let Database.Address3 = clsAddress.Address3
Set/Let Database.Address4 = clsAddress.Address4

See, wasn’t that the simplest thing ? Better still, I will share my trade secret with you in the form of the image below:

Cells_A1_D5

This is an actual screen shot of a worksheet in the very file that I intend to use the aforesaid code!! Imagine the possibilities that I can have at my disposal to simplify my coding!!

CA VIKRAM S. MATHUR
09-Mar-2017 | 23:32 Hours IST

Beginner’s Course on VBA-Excel

vba-excel-beginners-course-20170101-v01

Announcing for the first time in the city of #Ahmedabad, the above course to be conducted by CA Vikram Shankar Mathur with effect from January 1st, 2017. Currently, it is proposed to be held at the aforesaid premises only, to be extended, possibly in the form of webinar’s in the future, depending on the success of the aforesaid #event. The same has also beein simultaneously announced at our FB-VSMathurCo profile and the CA Vikram Shankar Mathur – Live Page on Facebook. Cheers !! Contact us at DeadlyVBAProgrammer@outlook.com for further details.

CA Vikram Shankar Mathur (DVBP)
deadlyvba.programmer@gmail.com

PS: You can now make the payment for the aforesaid course by simply scanning the QR Code in this picture (or, alternatively enter the mobile number provided) to securely and instantly make the payment online!!

 

Pay by either scanning the QR Code or by entering the Mobile Number provided.

Pay by either scanning the QR Code or by entering the Mobile Number provided.

 

01-Jan-2017 | 21:43 Hours IST | #CAVSM |