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

#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