As far as I know, there is no documented limit on the size of a module. It seems the Excel development community has settled on 64KB as the size where strange problems start happening. So, as a rule of thumb, if you have a large module, it may be advantageous to split it up into smaller ones.
To check the size of a module, export it to a text file and check that file’s size in Windows.
Hmmm, 2KB. I think I’ll be okay with this one.
I too have searched in vain for a method to determine the size of a module, and finally ended up creating a method to quickly check it.
It’s not the best, but it creates a foundation you could use to do something like loop through all modules in a project and show their size. I never got to the point of doing that, however — this just works for a module in the same project that this code is in, and you have to specify the module name (it also works for forms).
It’s not the best, but it’s a bit faster than the method described above! Let me know if you find any way to improve upon it.
Sub get_Mod_Size()
Dim myProject As Object
Dim ComName As String
Dim tempPath As String
Dim fs As Object, a As Object
Dim result As String
‘ **************************************************************************************
‘ Use this to determine the size of a module
‘ Set ModName (component name) and tempPath (where to store the temp fule), then run
‘ **************************************************************************************
‘ Set these to run
ComName = “UserForm1?
tempPath = “c: 01 test.bas”
‘ ***** No action needed after this point *****
‘ Export the component (module, form, etc) – this is only temporary
Set myProject = Application.VBE.ActiveVBProject.VBComponents
myProject(ComName).Export (tempPath)
‘ Get the size of the file created
Set fs = CreateObject(“Scripting.FileSystemObject”)
Set a = fs.getfile(tempPath)
result = ComName & ” uses ” & (a.Size / 1000) & ” KB.”
‘ Return the file size
MsgBox result, vbExclamation
‘ Delete the exported file
fs.Deletefile tempPath
End Sub
Not done any real testing on this but it appears to be quite accurate.
Result in KB’s
Enter this in the Immediate pane, making sure the code module is active.
?len(application.VBE.ActiveCodePane.CodeModule.Lines(1,application.VBE.ActiveCodePane.CodeModule.CountOfLines))/1000
Felt pretty good about my code until Andy posted his comment. He always makes me feel like I should buy Computers for Dummies.
I followed your link to Stephen Bullen’s new site (19-Oct-2004).
I downloaded his VBETools utility which among other things provides a module size on the toolbar, if you have it displayed.
When compiled, the code for a procedure can’t exceed 64K.
I can’t believe no one knows this. The key word is “compiled”. I get these errors all the time. I have to find ways to reduce the “compiled” size.
So far I have been able to each time.
OMG!! Your blog entry about Excel VB size limited saved me from getting fired on my job!!!! My excel file kept crashing on me because I’ve written all my codes behind the userform and the userform is 120K. Not until my friend send me your link, then I understand about the limitations and I moved all the codes behind the form onto 6 different modules (about 20-30 k each), then it resolved the file crashing problem.
THANK YOU SO MUCH Santa!!!!!!!!!! This is the BEST x’mas GIFT EVER!!!!!!!!!!!
My problem wasn’t this simple. I get continuous “Out of Memory” errors. 1 thing I have considered is that the use of range references to populate form fields could be the culprit. I now only Initialize values via vb code.