Hit Memory Limit in VBA, will Visual Studio VB be Better?

Question

A userform in Excel VBA started hitting the memory limit of 64 kB today. I expect this interface to grow by 10-15% at least and can easily see it growing larger now. VB6 sounded like it would be better, but I read it also has a 64 kB memory limit of the same kind.

I am stuck using Visual Basic since other workers on this project do not know C++ or any other language. Any quick suggestions besides condensing the form controls?

The issue is too many controls, not gibberish code. The form has multiple multipages, some as many as 10 pages containing multipages of about 4 pages with a number of text boxes, labels, and combo boxes in them. The userform is just big. — user1229293, Nov 06 '12 at 23:56
Why not just split your code out into other modules and/or classes? — Tim Williams, Nov 06 '12 at 23:58
Actually, he is correct, there is a limit on the size of the source code of any single module/class/form in VBA (and still in VB6, IIRC). The solution is to break those big modules down into smaller ones through subroutine/function/method/property calls. — RBarryYoung, Nov 06 '12 at 23:58
It's not the code that I am implementing that is too large. When you add objects like text boxes it adds to the memory of the userform. I also can't put those objects into other modules, they must be part of the actual userform module. There is relatively little other code that I have actually typed into the userform module itself. A sign of proof is the limit occurring and not occurring when I add or remove objects without touching actual code. — user1229293, Nov 07 '12 at 00:01
If you add controls using the designer, not through code, it actually edits a source file which you can't normally see - the code to add the controls and position them is just plain VB in that hidden file - so it may well be the file size limit you're reaching. I'm not aware of any per-form memory limit based on the number of controls (although I seem to recall a per-form control count limit of 256 of each control eg textbox) — Basic, Nov 07 '12 at 00:04
Right, I think that is what is happening. I am supposing now vb.net will not change much for me though, I wanted to clarify if it really would have the same kind of limit. Does anyone know why this limit exists for vb.net as well? — user1229293, Nov 07 '12 at 00:06
vb.net is a fundamentally different architecture - I don't believe it has any limit other than available resources. Ok, [it seems](http://bytes.com/topic/visual-basic-net/answers/492143-maximum-number-controls-form) that each control can have a maximum of ~32k child controls - eg each groupbox/tab pane/etc... I suspect you'd run out of memory before you hit that. — Basic, Nov 07 '12 at 00:07

Basic · Accepted Answer · 2012-11-07T00:25:58.653

0

I've just written the following simple test...

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        While True
            Dim B As New Button
            Me.Controls.Add(B)
            Me.Text = String.Format("Controls: {0}", Me.Controls.Count)
        End While
    Catch ex As Exception
        Me.Text = ex.Message
    End Try
End Sub

It started having issues ~4,000 controls. I suspect this is due to the memory limitations on the laptop I'm on.

edited Nov 07 '12 at 00:25

answered Nov 07 '12 at 00:15

Basic

26,321
24
115
201

Thank you, that's encouraging. I was doing some testing as well and it appears to hit the computer's limits not artificial ones. Thank you for confirming this limit. I did not want to pursue this project without additional reassurance. – user1229293 Nov 07 '12 at 00:19
You're welcome, glad I could help. I highly recommend .Net, especially if you're coming from a VB background - you'll find it a refreshing change. – Basic Nov 07 '12 at 00:20
A couple of things, firstly a typo: It was 4k not 14k. Secondly, I've tracked down the reason for the failure - it seems that windows was unable to allocate additional GDI (Graphical) handles... Outdated article: http://support.microsoft.com/kb/894500 and more recent and detailed: http://blogs.technet.com/b/markrussinovich/archive/2010/03/31/3322423.aspx - It looks like 10,000 is the default limit for handles per process on the Vista+ OSes – Basic Nov 07 '12 at 00:32
Finally... It looks like the limit can be bypassed if you _really_ need to (but if you're doing this, you're probably doing something wrong!) http://stackoverflow.com/a/9723784/156755 – Basic Nov 07 '12 at 00:39
That's a nifty trick, but I do hope I will not need to use it. I plan not to. – user1229293 Nov 07 '12 at 00:49

Hit Memory Limit in VBA, will Visual Studio VB be Better?

1 Answers1