More Compact Code 1

[peterpcu]

Hi there,

I have a number of Dim lines which are very similar. Any suggestions on how I would compress these lines by maybe using something like a for next loop??

The code is:-

Dim lbl1C1 As MSForms.Label
Dim lbl2C1 As MSForms.Label
Dim lbl3C1 As MSForms.Label
Dim lbl4C1 As MSForms.Label
Dim lbl5C1 As MSForms.Label
Dim lbl6C1 As MSForms.Label
Dim lbl7C1 As MSForms.Label
Dim lbl8C1 As MSForms.Label
Dim lbl9C1 As MSForms.Label
Dim lbl10C1 As MSForms.Label
Dim lbl11C1 As MSForms.Label
Dim lbl12C1 As MSForms.Label
Dim lbl13C1 As MSForms.Label
Dim lbl14C1 As MSForms.Label
Dim lbl15C1 As MSForms.Label
Dim lbl16C1 As MSForms.Label
Dim lbl17C1 As MSForms.Label
Dim lbl18C1 As MSForms.Label
Dim lbl19C1 As MSForms.Label
Dim lbl20C1 As MSForms.Label

Many thanks

Pete

 

[mp9]

I think yiou could take a look at Arrays, i.e.

Dim lblC1(19) As MSForms.Label


Then refer to lblC1(0), lblC1(1), lblC1(2), etc, instead of lbl1C1, lbl2C1, lbl3C1, etc.

http://www22.brinkster.com/accessory/

 

[TonyJollans]

Hi Pete,

Silly question, probably, but why do you need so many MSForms.Label variables in the first place?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[peterpcu]

Hi Tony,

An excellent question. The answer is I needed to build a number of user forms which were to be propagated with data from a spatial database. So to quicken the process up I have built an application which converts formatted info on a worksheet to a VBA user form with each object on the userform having its own unique name. Works very well although it would be nice to compress some of the code to make it a little more pleasing to the eye. Hence the reason for starting the thread.

Also another question - is it possible to inhibit the enanble macros message which come up on Excel start up?

Best Regards

Pete

 

[TonyJollans]

Hi Pete,

Now I remember your earlier thread about dynamically creating userforms. But do you actually need to keep all those object variables concurrently? After you've added a label, do you really need to keep a reference to it? Couldn't you reuse one of the variables over and over?

The answer to your second question is No, you can't do it in code. Actually on re-reading, I'm not sure that is what you're asking. You can set security to Low but that isn't really advisable or you can sign your project. I haven't got a reference to hand but google or search msdn for SelfCert and you should find out how to do it, after which people only need to trust you once.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[