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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s