2148 lines of code = "Error when compiling, procedure too big"

[buhnen]

27.05.png

Hi,


I´ve got the error message above, which in English comes to mean " Error when compiling, procedure too big"

Well, no wonder, I have an array of 537x4 elements which took 2148 lines of code to be declared. And I don´t even mention how big the rest of the code ...
The code by the way is placed within a UserForm...

I´m a newbie at VBA programming and for sure I´m not following the best practices to code things....Could someone please give me some hints or directions about how to avoid this problem? ...

Thanks in advance,
Ruben

[Kyle123]

[quote]I´m a newbie at VBA programming and for sure I´m not following the best practices to code things.[quote]
You aren't

It's hard to say without seeing a sample of your code, but I suspect a loop is in order.

[6StringJazzer]

It's hard to say without seeing a sample of your code, but I suspect a loop is in order.

Agree. Earlier this week I rewrote some code for somebody that did this. It was a 9 x 8 array and he declared 72 variables to hold the data and individual lines of code for each element. It was hundreds of lines of code that I rewrote as 15 lines.

Please attach your file for more help.

[LOKLUK]

Hello

It's hard to say without seeing a sample of your code, but It s clair what mean in English " Error when compiling, procedure too big " and that because

I´m a newbie at VBA programming and for sure I´m not following the best practices to code things

so for minimizes your code you have :
loop, arrays, call ........

[buhnen]

...So, thanks for help, just like always

I´ve uploaded the code...
As far as I´ve watched in other posts and google searches , I should divide my code in subroutines and call them whenever I need them...
The problem is, as I said, that I´m not too deep into VBA programming and since I have some actions linked to UserForm events (clicks on buttons, etc)
and these are declared as Private, I´m not sure if I can place them on modules. Please, keep in mind that this is the second Macro I´ve ever created
and I´ve needed a lot of time, effort, and constant help from the contributors of this forum (to whom I´m sincerely grateful).

I have still a long long way ahead to learn programming consistently

[Norie]

Couldn't you store the data for FunkDef_InterneWerte on a worksheet?

Also, this,

Please Login or Register  to view this content.

could be cut down to 1 line of code if you used List and Array.

Please Login or Register  to view this content.

I know that might not look like one line but it kind of is.

Oh and most of your With statements aren't needed.

[cytop]

Beaten to it...! Example attached loads the array when the userform initialises - previously the array was being re-populated every time a choice was made in a combobox. The debug window will be displayed immediately after (deliberate, before anyone complains about errors )

Also shows a method of storing the Combo box options on a worksheet,

Your original project contained over 3700 lines of executable code - now down to about 75.

I have some actions linked to UserForm events (clicks on buttons, etc) ... I´m not sure if I can place them on modules

No - they are Event Handlers and must exist in the same object as the control they relate to.

[Norie]

buhnen

Quick question, why do you have 25 comboboxes?

[buhnen]

Hi all!

I come back with some more feedback for you all

@Norie:

Couldn't you store the data for FunkDef_InterneWerte on a worksheet?

I thought of this way at the very beginning of starting the code. What put me a little off was the fact that if I can have all the code under the UserForm, then later if I'd like to implement this code in other workbooks, then I could very easily export the UserForm as a single file and simply import it in the target workbooks. If I read the variables from a worksheet in the workbook, later on I´d also have to copy the worksheet with the variables (array elements) and paste it to the target workbooks...It isn´t a big deal but I saw saving the code in only one file (UserForm) like easier to manage in the future...

But if doing it so, I can compile the code, avoid that error message and keep on coding further, then I guess I can accept this solution

In order to build this code lines up, I´ve used the Ampersand excel function which it was of a great help naturally...

Oh and most of your With statements aren't needed.

I´m intrigued about this .... In my limited experience of three weeks as a VBA Newbie I´ve managed to learn this way with With Statements for setting up 3 dependent ComboBoxes. I learnt it from here: http://www.excel-easy.com/vba/exampl...mbo-boxes.html and when I needed to add a third ComboBox the issue of lacking know-how on VBA programming came up and opened a thread in this forum to get some more information/help. In the end, I used a If Statement. I´m sure there are more efficient ways to do this but I don´t know them... How do you mean exactly about not using With statements? How would you do that instead?

( NOTE: My only experience with programming is regarded to PLC´s ( Programmable Logic Controllers) ...VBA is something totally new to me. 10 years ago I learn in the school some very basics of C++ )

Quick question, why do you have 25 comboboxes?

I forgot to delete the VBA Sheet "Entwurf" (German for "Draft")

It´s a bit tricky to be explained ....but in the end I dismissed that tasks' approach . This little Macro shows orderly through the dependant comboboxes several categories of elements sorted out according to a criteria based on a database hierarchy structure. In the DB are allowed up to 100 assignments (entries on the ComboBox 3)...it takes long to properly explain it but keeping it straight: I can manage it with only 3 ComboBoxes and some extra functions (numbering button)

@Cytop: Thanks for the example! I´m though trying to understand the code you added....

Please Login or Register  to view this content.

a) I have to google "Option Compare Text" to fully understand what this line means.
b) Public FunkDef_InterneWerte As Variant...ok, the variable "FunkDef_InterneWerte " is defined as public, to be available later on for multiple Subs I guess..or is it only for test purposes? ---> Sub test()

c) Under the Sub UserForm_Initialize ()...

Please Login or Register  to view this content.

c.1) I dont understand here how .UsedRange references to the values in the Worksheet "Funkdef" ...could you maybe please explain me a little bit further about it? ...or point me to some other posts in the forum related to this ?
https://msdn.microsoft.com/en-us/lib.../ff840732.aspx ...unfortunately I cant still see how the values in "Funkdef" are selected

c.2) I also have to google this "Debug.Assert False"

On Monday I go further with the code.
Thanks for your hints and a nice weekend!

[cytop]

"Option Compare Text" is a compiler directive (Only used by the compiler before the code is compiled and run). It tells the compiler to ignore case when comparing strings.

VBA, by default is case sensitive "a" <> "A". Using Option Compare Text makes "a" = "A". In my experience, there are very few times when you need to compare stings exactly - checking a password that must contain Upper & Lower case characters is probably the most common, but other than that, say searching for names, you don't really care if it is entered as 'Jim', 'JIm' or 'jiM' so no need to convert everything for every comparison. This only applies to VBA - The standard Excel Find has a MatchCase parameter.

The Debug.Assert false is just to pause execution to view the code (The one line assignment to the array). It can be removed.

The worksheet UsedRange is the range of filled cells, You can assign a range to a variant array and the array will have the same number of elements (rows) dimensions (columns) as te range. If you were to add a 5th column to the worksheet, the code does not need to be changed to load the range to the array, it will just pick it up. Same if you add rows.

The Public declaration is my mistake - it's in the userform so should be a standard 'Dim Funk...'

only one file (UserForm) like easier to manage in the future

If you can guarantee the data is static and will never change? It's easier to change a worksheet than code. Overall the worksheet approach is simpler, faster and greatly reduces the amount of code, in my opinion.

Speaking with my 'boss' hat on, if anyone ever turned in a project where there were 2800 lines of code assigning values to an array I'd question that come next appraisal. That's not a criticism of your code, just my 'professional' thoughts.

The attachment was updated, now loads the comboboxes from a worksheet in case you missed that.

[Norie]

buhnen

Can't go into all your points right now, kind of late, but in this code the With isn't really being used.

Please Login or Register  to view this content.

I would expect to see something more like this.

Please Login or Register  to view this content.

PS If you know C++ you must know that all the files/data need to be stored in one file, for example you could use a text file for some of your data.
Working with that might not be as straightforward as using an include directive but it might be an option to consider.

[Norie]

Correction.

PS If you know C++ you must know that all the files/data don't need to be stored in one file, for example you could use a text file for some of your data.

Working with that might not be as straightforward as using an include directive but it might be an option to consider.

[buhnen]

Hello Norie and Cytop,

I´m back, sorry for the delay in getting back to the thread...My boss kept me busy with another tasks and today finally I´ve got the time to come back to this topic....I´ll have a look to the last answers you posted and I let the thread still opened since probably more questions are still yet to come...

[buhnen]

Everything has worked out fine in the end....I set this topic as "SOLVED". Thanks Norie and cytop for your time helping me and your valuable hints !