VBA Code Limit

A module in Visual Basic for Excel cannot contain an unlimited amount of code. As Microsoft confirms in this posting on its Developer Network, the limit is 64 K.

If you enter VBA code in a module which exceeds this limit, you will get this message:

. . . indicating that the 64K threshold has been exceeded.

You can check the size of a module by using the VBA code posted here by RoyUK on OzGrid.

Simply enter this additional vba code, below the vba code you're checking. A dividing line should appear in the module. Press play with your cursor still in the HowBig code section.

Sub HowBig() Dim ModSize As Double ModSize = Len(Application.VBE.ActiveCodePane.CodeModule. _ Lines(1, Application.VBE.ActiveCodePane.CodeModule.CountOfLines)) / 1000 MsgBox ModSize End Sub

A separate message box will appear listing the VBA code size.

If you get an error reading, "Programmatic Access To Visual Basic Project Is Not Trusted", go to File . . . Options . . .Trust Center . . . Trust Center Settings, and then under Macro Settings check off the option for 'Trust Access to the VBA project object model.'.