Impact on execution speed and application size from modules and func.. 3

[rickyoswaldiow]

Good afternoon everybody.
Today I am working on some project design work. I am reverse-engineering parts of an application that have been built in VBA. I have all of the technical information required for the project but I am wondering; If I go for a highly modularised design using many functions, often to call just half a dozen lines of code, will there be a high impact on application size and execution speed?

As I understand it, the application size will increase as I add more modules. The execution speed will NOT decrease as functions are a human concept and a way of helping us build programs - the computer reads it the same way after compilation. Is this true?

 

[strongm]

Er --- this sounds like an educational issue ...

 

[rickyoswaldiow]

In addition to the above question I would like to ask if the following method is technically viable:

My form has a SSTab control and also references several different tables in a database. The controls on each tab do not nessicarily relate to a single table, controls relating to tblVairables can be found on all tabs while tblBanks is limitied only to the Banks tab.

I am looking into different methods of saving the data when it is edited and this is what I would like to do; A string array would be created for each table. When a field is edited, the controls Change event adds itself to the string array.
When the user hits the save button, each string is checked to see if it has any members. If data is found in a string, it's relevant function is called from the save module. This function opens up the relative recordset and loops through the string to update only the fields that have been edited.

Is there a way for a control to reference it's own name in the same way a form can reference itself with Me or must I hand write each change event?

 

[rickyoswaldiow]

this sounds like an educational issue ...

Please, enlighten me

 

[strongm]

>enlighten me

Sorry, it just sounded a bit like a classroom exercise ...

 

[Nimroduk]

Agreed with above tbh.

Ricky, you should go and read about "The Stack" and "The Heap" memory spaces. It might help to readup on Two and Three tier architectures. That should enlighten you.

 

[rickyoswaldiow]

Thankyou Nimroduk

 

[SBerthold]

For your second questions why would you want to go through all of that?

If you really want to go that route, you only need to use a recordset and either tie the recordset fields to the controls, or, more loosely, in the control's Change event, set the recordset field value for that certain control's DataField, to the control's value. Then call the recordset's Update method (or CancelUpdate) when all changes have been made.

 

[JoeAtWork]

  When the user hits the save button, each string is checked to see if it has any members.  If data is found in a string, it's relevant function is called from the save module.  This function opens up the relative recordset and loops through the string to update only the fields that have been edited.

This sounds like a code maintenance nightmare!

When you design the framework of an application, ask yourself how hard it would be for another programmer to take it over. How intuitive is the code and the way it is organized for the new programmer to understand what is going on?

I would suggest embedding the code in classes. This will add structure to your code.

How many lines of code you use is irrelevant. Always aim for readability of your code. Many small, highly specialized functions are almost always better than huge monolithic ones. Suggested reading on this is Code Complete from Microsoft Press.

 

[BobRodes]

For some higher level conceptual background, take a look at cohesion and coupling.

 

[tedsmith]

This may help
What I do is only ever have one control of each type on a form at design time. This has an index of 0 and in set visible=false.

I create all the other controls at run time. You can have 64 of each type on a form at once and it runs just as fast.

When I want a control to show I use
TextCounter=TextCounter+1
Load Text1(TextCounter)
Text1(TextCounter).Move L,T,H,W 'the postion & size I want it

From then on I refer to the particular control by its index (TextCounter) number.

Another thing you can do is to pass the control name to a common subroutine to save it's data

Call SaveData (Message,ControlName)

Sub SaveData(MyData as String, MyControl as Control)

 

[JoeAtWork]

tedsmith - I have done similar when there was a practical reason to dynamically create controls. In my case, I allowed the user to dynamically configure what fields they could search against, which meant at design time I didn't know what controls should be on the screen (the user's preferences were stored in a database table).

But if you are recommending to do this on all forms, even ones that remain fairly static in configuration, I just got to ask - why?

 

[tedsmith]

1. If you have a lot of controls it make it easier to place them exactly in line and size by having a for next loop in the form_load and incrementing the left and height

2 you can have a common lostfocus event to update the database
when you change data. You have a Select Case Index routine in one sub.

3 Its much easier to make changes, add extra controls without having to move the others to make them fit

4 Cuts down the code a whole lot if you have many controls on a form and makes it easier to have one common syntax or content checking routine apply to all controls of the one type.

 

[JoeAtWork]

1. If you have a lot of controls it make it easier to place them exactly in line and size by having a for next loop in the form_load and incrementing the left and height

Well, OK, if you have a lot of controls I can see this. But it seems a pretty rare occurence to have so many controls on a form. I can't imagine scenarios where so much clutter is needed.

2 you can have a common lostfocus event to update the database
when you change data. You have a Select Case Index routine in one sub.

Hmmm, what I see there is one huge monolithic subroutine to handle a bunch of unrelated events. And you are updating the database on every field change?

3 Its much easier to make changes, add extra controls without having to move the others to make them fit

Well, I guess, if your form is very cluttered with controls. I'm just having a hard time imagining forms with hundreds of controls. I'd say 95% of my forms have a couple dozen controls or less.

4 Cuts down the code a whole lot if you have many controls on a form and makes it easier to have one common syntax or content checking routine apply to all controls of the one type.

The same can be accomplished by looping through the controls collection of the form and checking their type.

It just seems to me if all the fields are so generically the same, couldn't you accomplish the same with a single grid control?

It just seems like a maintenance nightmare to me.

So if I took over one of your projects, when I open each form I would see one type of each control sitting in the corner of an otherwise empty form? I could get no sense of the form's purpose from the layout, because there is no layout.

And the only way I could figure out the forms behaviour is by looking at pure code? And the code I have to look at is basically a few huge monolithic subroutines?

So if I wanted to see what happens when a dropdown is clicked, I've got to look in one huge combo_Click event, scrolling through the code until I spot the "Select Case" that applies to the one I am looking for.

Perhaps you could describe one of your applications to me, cause I'm just not seeing the benefit of this non-Visual Basic technique.

 

[rickyoswaldiow]

JoeAtWork Said:

I would suggest embedding the code in classes. This will add structure to your code.

Most definately. Part of the problem with the application I am working from is there are too few modules and classes, each handling unrelated events. This is why I was asking if it would be a sensible move to add more modules and functions.


SBerthold Said:

why would you want to go through all of that?

Under the current setup, if you change on field on one tab, hundreds of records are updated. I want the recordset to be dynamic so that it is not executing unessecarily.

 

[rickyoswaldiow]

*EDIT* ...if you change one field on one tab...

 

[tedsmith]

Sorry if I am a bit revolutionary but I went through all the
"normal" way of doing it when Access 1 came out in 1986 or whenever and I often thought there had to be an easier way.

One simple application of my method would be a "modify client details form".

Yes I update the database on each item if the data has changed at the point you leave that control - but not with every keystroke.
I use gotfocus to change its color to pink to indicate it is active then if then only if the data is changed in that box do I update the database when the control loses focus (EG tab or enter to go to another control.

I don't like "update buttons" after a group of inputs as they can be forgotton and computers can handle many updates without blinking these days if you use code simple recordsets. Some people have a habit of closing an application or switching off the computer just after entering something anyway before the database has been updated.

The index number of the "created" box can correspond to the field number in the database so you don't need databound controls. The trick is use indexes as much as possible to make multiple use of the same subroutine. It cuts down the number of subroutines dramatically.

You can also use the same form for many different tables if the text box names are the same name as the field in each table (or names can be contained in simple text strings delimited by commas and separated by Split)

Yes it looks pretty sparse to another programmer but I always have an invisible label on the form explaining it all. The code is actually much shorter and easier to understand and you only need a lot of select case statements if there is something you need to do that cant use index techniques.

I actually got the idea making a web site with Frontpage. There you can control where you put objects by putting them in invisible frame boxes.

A current application of mine uses 50 rich text boxes, a few graphic and movie boxes on one screen. It is a sort of public information menu display for a shop window. It turned out to be much easier than any other method with only a few pages of code.

 

[JoeAtWork]

Sorry if I am a bit revolutionary

"Anachronistic" is the word that comes to mind. Before visual languages, programmers had to spend a lot of time writing code for the UI elements of their programs. A huge part of Visual Basic's popularity was due to the fact that being able to drop controls on a form and lay them out in design mode meant the programmer could concentrate a lot more on the business logic rather than the mechanics of the UI code. Your technique takes you back to the days when we had to write code to initialize the UI.

I update the database when the control loses focus

Quite simply that is a hugely unnecessary number of calls to the database. It makes the application unscaleable. As your user base grows, you will likely need to upgrade your server hardware and bandwith at a much greater rate then if you only updated once per record.

I don't like "update buttons" after a group of inputs as they can be forgotton

That's what IsDirty flags and "Do you want to save your changes?" prompts are for. Plus, you are not giving the user any chance to cancel their changes. What if the user has changed 5 fields and suddenly realizes they are updating the wrong record? They would have to update the same fields again, assuming they can even remember what the original values were.

The trick is use indexes as much as possible to make multiple use of the same subroutine. It cuts down the number of subroutines dramatically.

You seem to advocate that the less subroutines the better. In fact the number of subroutines has no relation to the maintainability of code. I follow the Code Complete philosophy that subroutines should be highly specialized and do only one thing. Thus, 20 LostFocus events for 20 textboxes is better than one monolithic LostFocus event for all textboxes.
Code reuse is an important aspect of coding, but that is not the same as monolithic functions. If you want common validation for all textboxes, you should create one ValidateText function and then pass in the text from each textbox's LostFocus event. That way you have a lean validation function without a whole bunch of Select Case statements that obscure its purpose.

You can also use the same form for many different tables

Uh oh, even more Select Case statements? Cause now I have to check the table name in addition to the controls index, don't I?

Your example of the 50 rich text boxes might be one where I would agree to use a control array, as I am assuming their behaviour is generic.

But your example of "Modify Client Details" is most definitely not a candidate for control arrays. Validating a phone number is different than a postal code or SIN number - which would require the use of those dreaded Select Case statements. This breaks the best code practice where a function should do only one specific thing.

Sorry if I seem to be beating up on you Ted, but I believe the OP was looking for advice on best practices, and your theories run counter to much of my own experience and learning.

 

[BobRodes]

<there are too few modules and classes, each handling unrelated events. This is why I was asking if it would be a sensible move to add more modules and functions.

This is an example of a cohesion problem. A characteristic of entities that lack cohesion is excessive decision logic, which is one of the reasons why their performance is less than optimal. The excessive decision logic stems from the requirement to branch between unrelated functionalities before actually performing real work.

It sounds like you intutively grasp the issue at hand here. Reading up on cohesion and coupling will give you sme bakground.

Bob

 

[tedsmith]

To answer rickyoswaldiow's last original question, using a common function or subroutine rather than repeating the same procedure every time you use it does take a slightly longer time but it is only microseconds.
This is because more microprocessor cycles are used to reserve a memory area when you enter and dimension a private variable in the function and subsequently leave it.

Therefore longer code can run faster, once it is loaded.
You can easily test this by
debug.print timer
for a=1 to 1000000
Do 10 instances of the same routine (like multiply 20 numbers) in the code then try it with a for a=1 to 10 next loop with the same routine once inside the loop)
Next
debug.print timer
And compare the 2 numbers you find in the immediate window in each case. The timer is only good to 15 milliseconds so you should have a big enough routine so the total time takes about half a second to see the real difference


Actually I think some of the conventional uses of VB was a backward step although it makes a good training tool for beginners.

My experience of programs that have a sub or two for every control on the form are a nightmare to follow!
You have to handle either mouse or keyboard usage so you often need at least 2 and if you want it to change color when it has the focus you need to multiply this by a few more.
I am not advocating the massive use of select case statements with a case for every button at all.
Quite often you only need a few types of validation so you use
Case 1 to 6
'check length of text and capitalise the first word
Case 7 to 10
'check is a valid date
Case else
ChangedFlag=True
I think that is much easier to follow and understand.

If you have a common validation sub you are only doing this anyway but plus the intermediate step of the separate sub for each control!

Another convention I hate is
txtName.txt = strDfm 'Default family name
It only muddles the code text in the editor and makes some items look the same
Easier to understand if you have with my system using long real words.
EntryBox(Index).txt = DefaultFamilyName 'the comments are virtually unnecessary unless it has to be also in 2 boxes