Auto generated input text boxes on specialised user form (Enter Table of Contents)

Hi,

The file associated with post #11 in the following thread should help you get started: http://www.excelforum.com/excel-prog...matically.html

It creates several dynamic text boxes, and has several static text boxes. One 'Class Event Handler' services 'Double Click Events' and 'Change' Events for all Text Boxes. Similar code can generate other types of dynamic controls. Similar Class modules can handle events from other controls.

In the file, Class Module 'Class4' contains the following code:

Please Login or Register  to view this content.

UserForm1 code module contains the following code:

Please Login or Register  to view this content.

Ordinary code module code (concatenated from 3 different code modules contains):

Please Login or Register  to view this content.

Lewis

Hi,

Thanks for the Rep points.

mc84excel said: <One problem I have so far is that the controls run off the form>

There are several ways to address this including:
a. Make the TextBoxes (and inside text) smaller
b. Adding a Vertical ScrollBar
c. Use a multi-page UserForm
d. Display only some of the TextBoxes. Have a CommandButton (or other control) that will toggle between the 1st half and 2nd half of the text boxes. You can have the TextBoxes on top of each other and toggle between visible and NOT visible.

Lewis

FWIW I'd do something along these lines, I think it's easier to follow code that's tailored to the application than using something very generic that needs more parsing.

@mc84Excel
I don't like the way you break encapsulation, the approach below keeps the code where it should be and not scattered about - I find your code very difficult to follow.

One could argue that the interface is superfluous, however, for little effort, it's nice to be able to return a clean, validated typed array without the additional properties.

You're also auto-instancing your form, don't use default initialization, it's better to be explicit.

There's code in the userform to increase the scroll height of the form should the height of the controls exceed the size of the form, this could just as easily be applied to a frame (and the contentsControl object allows for it)

Module:

Please Login or Register  to view this content.

Userform - ContentsForm:

Please Login or Register  to view this content.

Interface - IContents

Please Login or Register  to view this content.

Class - ContentsControl

Please Login or Register  to view this content.

To address your questions via pm and add some clarity:

You're also auto-instancing your form, don't use default initialization

Refers to this code in fnavarUserReponseFromEnterToC

Please Login or Register  to view this content.

Is better as:

Please Login or Register  to view this content.

The former is essentially the same as:

Please Login or Register  to view this content.

Or even frmEnterTOC.Show

This uses a default auto-instanced version of the form, use New when you want the form instanced, rather than relying on an implicit New. That is what I mean by being explicit, nothing to do with using Option Explicit.

Yes, you're breaking encapsulation with your t_ modules, you have logic explicitly controlling actions within an object taking place outside that object. An example would be:

Please Login or Register  to view this content.

This directly changes the value of a textbox in a userform from a module outside that userform. A way of refactoring it is to remove its dependency on the userform so that is simply a string formatting function:

Please Login or Register  to view this content.

This would then be called in the userform like this:

Please Login or Register  to view this content.

This decoupling also has the added benefit of making your code easier to unit test:

Please Login or Register  to view this content.

Also, in terms of making your code hard to follow, your naming conventions drive me mad they're not event consistent!

Originally Posted by Kyle123

This uses a default auto-instanced version of the form, use New when you want the form instanced, rather than relying on an implicit New.

Thanks Kyle. I didn't realise that's what it was being treated as. I will avoid this in future.

In general, you should avoid auto-instancing variables for two reasons:
First, it adds overhead to the code because the variable must be tested for Nothing every time it is encountered in code.
Second, you have no way to test whether a auto-instancing variable is Nothing because the very act of using the variable name in an If Obj Is Nothing Then statement will automatically create an instance of the variable.

(Thanks to Chip Pearson)

Originally Posted by Kyle123

Yes, you're breaking encapsulation with your t_ modules, you have logic explicitly controlling actions within an object taking place outside that object.

Yes, I had a question about that in our PMs. If those t_modules were classes, would that still be bad practice? (to control actions within an object taking place inside a class)

Originally Posted by Kyle123

A way of refactoring it is to remove its dependency on the userform so that is simply a string formatting function [snip] This decoupling also has the added benefit of making your code easier to unit test

Good points all. (FWIW I was aware I was doing this but since I had no use for these two functions outside of userforms, I didn't care! In any case they were both suffering from code bloat. Will be scrapping these and using KeyNo in event macros)

Originally Posted by Kyle123

Also, in terms of making your code hard to follow, your naming conventions drive me mad

Heh It's called Reddick. I prefer it over the more popular Hungarian. Of course, personal tastes are subjective

Out of curiosity, what is it about it that drive you mad? (I thought it would be fairly easily to read without prior knowledge. Especially so for those who use/learnt Hungarian. e.g. using str/bln/var/lng as prefixes to variable names should be self-explanatory)

Originally Posted by Kyle123

they're not event consistent!

Sorry I don't follow. Would you mind expanding?

No, simply making them classes wuuldnt change the situation. They’re still fiddling with the internals of other objects.

I don’t like Hungarian in the way most people implement it either - it’s useless, the IDE tells you the type, but yours is a level above, your names are long and hard to parse, I suspect you’ve gotten used to it so don’t notice it now.

Hungarian notation was never really designed to indicate the type, but rather the kind of data within the variable - however most people don’t use it as intended. Here’s some coffee time reading that you may find interesting https://www.joelonsoftware.com/2005/...de-look-wrong/ (Joel was a programme manager on Excel and co-founded stack overflow)

As to not consistent, I can’t remember specifics off hand (I’m on my phone and can’t open your workbook) but i think you have functions in your t modules that aren’t prefixed with fn (and other letters).

Another point about using the auto instanced form is that if you have more than one instance of it, you can’t be sure which one it refers to easily.

Originally Posted by Kyle123

No, simply making them classes wouldn't change the situation. They’re still fiddling with the internals of other objects.

Good point. OK. I will try to break myself of this habit. (Unlike the auto instancing, I'm going to find it hard to change this though. I find t_API_Form_Controls extremely useful. Particularly when I want to run code on the active control)

Originally Posted by Kyle123

I don’t like Hungarian in the way most people implement it either - it’s useless, the IDE tells you the type

True. I find it a lot easier to think of - and write - code with type prefixes. There is a reason for this. I used to write code freehand - without using any IDE. Not by choice I hasten to add. But if I was away from Excel and I suddenly had an idea that I wanted to save before I lost it (it happens!), I'd reach for a pad and dash out a few pages in code. Then it was an easy matter to type it verbatim into Excel later. I found it much easier to write IDE-less code after I adopted a naming convention.

Originally Posted by Kyle123

yours is a level above, your names are long and hard to parse

There is a reason for the longer names. I adopted Reddick. Without wanting to spiral off into a nerd rant, Reddick is an improvement on Hungarian Notation. To give one simple example - type prefixes (as commonly used in Hungarian) can be ambiguous e.g. if I see "bVariableName" is this a byte or boolean? Sure, I could check the IDE (if I am using one at the time! see above) or I could scroll up and check the declaration but either is a waste of time and effort. Once I started using three letters for type prefixes, I never looked back. One glance gives me the key data I want.

Can we agree to disagree on this one?

Originally Posted by Kyle123

I suspect you’ve gotten used to it so don’t notice it now.

You're right. In fact rather than "don't notice", I find it easier. I assumed Reddick would be easier for others to read too. Turns out I was wrong. (Then again, that may be because you don't use Hungarian. IMHO Reddick would be easier to read/take on if you already using Hungarian)

I'm afraid that by this point I couldn't contemplate abandoning a naming convention when writing VBA. I would be open to adopting a different naming convention however I only know of two for VBA and going back to Hungarian after Reddick would feel like a regressive move.

Originally Posted by Kyle123

Hungarian notation was never really designed to indicate the type, but rather the kind of data within the variable

I didn't know that. Interesting.

Originally Posted by Kyle123

Here’s some coffee time reading that you may find interesting https://www.joelonsoftware.com/2005/...de-look-wrong/ (Joel was a programme manager on Excel and co-founded stack overflow)

That was an interesting read. Thanks Kyle.

Originally Posted by Kyle123

As to not consistent, I can’t remember specifics off hand (I’m on my phone and can’t open your workbook) but i think you have functions in your t modules that aren’t prefixed with fn (and other letters).

What?! I may have my faults but at least I'm consistent about them!

Then I had a look at my code in these modules:

• Public Subs have no prefixes = Consistent
• Private Subs have a "p" prefix (p for private) = Consistent
• Private Functions start with a prefix made of a "p" and a "fn" (p for private sub/function, fn for function) = Consistent (Also followed by a type prefix. e.g. a private boolean function would start with "pfnbln")
• Public Functions start with a "fn" prefix (followed by a type prefix) = Inconsistent! Well there is a reason for that (Defensively ) All of these functions are consistent except for only two exceptions. I didn't apply a prefix to these 2 functions because they return a type ("IPicture") that I don't know what the type prefix should be. If anybody wants to volunteer a three letter prefix for IPicture type, I will get those exceptions fixed.