Is the 64kb size limit to modules corrupting macros for excel 365?
I'm having random macro corruptions where I can't open the workbook.
I would have to disable all macro without notifications then open the workbook and recompile the macros, save close. Then enabled macros and open the workbook, then the workbook opens fine. Its almost like refreshing the macros helps reopen the workbook. I've checked some of my modules and a few are over the 64kb size. I've read some articles pertaining to module size limits that causes corruption.
Anyone verify if this is also for excel 365 or any reasons for macros to get corrupted?
microsoft-excel vba
edited Dec 7 at 15:07
Albin
2,2971129
asked Dec 7 at 15:02
ThisGuyJustNeedsHelp
62
add a comment |
I'm having random macro corruptions where I can't open the workbook.
I would have to disable all macro without notifications then open the workbook and recompile the macros, save close. Then enabled macros and open the workbook, then the workbook opens fine. Its almost like refreshing the macros helps reopen the workbook. I've checked some of my modules and a few are over the 64kb size. I've read some articles pertaining to module size limits that causes corruption.
Anyone verify if this is also for excel 365 or any reasons for macros to get corrupted?
microsoft-excel vba
edited Dec 7 at 15:07
Albin
2,2971129
asked Dec 7 at 15:02
ThisGuyJustNeedsHelp
62
4
Write smaller modules? Read up on cohesion and principles like Single Responsibility - your code will be easier to maintain, and you won't be dealing with module size limits.
– Mathieu Guindon
Dec 7 at 15:05
well, the point is, i'm just wondering if it could be a factor for my macros to be corrupting? Also excel randomly closes when copy, paste text into other sheets within the workbook causing the same macro corruption.
– ThisGuyJustNeedsHelp
Dec 7 at 15:23
I've read some articles pertaining to module size limits that causes corruption - the internal storage format hasn't changed in 20 years, what makes you think O365 changes any of that? The limits aren't the problem here...
– Mathieu Guindon
Dec 7 at 15:24
FWIW the 64KB limit isn't on the exported file size / text source code, it's on the compiled p-code in the internal storage.
– Mathieu Guindon
Dec 7 at 15:36
add a comment |
I'm having random macro corruptions where I can't open the workbook.
I would have to disable all macro without notifications then open the workbook and recompile the macros, save close. Then enabled macros and open the workbook, then the workbook opens fine. Its almost like refreshing the macros helps reopen the workbook. I've checked some of my modules and a few are over the 64kb size. I've read some articles pertaining to module size limits that causes corruption.
Anyone verify if this is also for excel 365 or any reasons for macros to get corrupted?
microsoft-excel vba
edited Dec 7 at 15:07
Albin
2,2971129
asked Dec 7 at 15:02
ThisGuyJustNeedsHelp
62
I'm having random macro corruptions where I can't open the workbook.
I would have to disable all macro without notifications then open the workbook and recompile the macros, save close. Then enabled macros and open the workbook, then the workbook opens fine. Its almost like refreshing the macros helps reopen the workbook. I've checked some of my modules and a few are over the 64kb size. I've read some articles pertaining to module size limits that causes corruption.
Anyone verify if this is also for excel 365 or any reasons for macros to get corrupted?
microsoft-excel vba
microsoft-excel vba
edited Dec 7 at 15:07
Albin
2,2971129
asked Dec 7 at 15:02
ThisGuyJustNeedsHelp
62
edited Dec 7 at 15:07
Albin
2,2971129
asked Dec 7 at 15:02
ThisGuyJustNeedsHelp
62
edited Dec 7 at 15:07
Albin
2,2971129
edited Dec 7 at 15:07
Albin
2,2971129
edited Dec 7 at 15:07
Albin
2,2971129
2,2971129
asked Dec 7 at 15:02
ThisGuyJustNeedsHelp
62
asked Dec 7 at 15:02
ThisGuyJustNeedsHelp
62
asked Dec 7 at 15:02
ThisGuyJustNeedsHelp
62
62
4
Write smaller modules? Read up on cohesion and principles like Single Responsibility - your code will be easier to maintain, and you won't be dealing with module size limits.
– Mathieu Guindon
Dec 7 at 15:05
well, the point is, i'm just wondering if it could be a factor for my macros to be corrupting? Also excel randomly closes when copy, paste text into other sheets within the workbook causing the same macro corruption.
– ThisGuyJustNeedsHelp
Dec 7 at 15:23
I've read some articles pertaining to module size limits that causes corruption - the internal storage format hasn't changed in 20 years, what makes you think O365 changes any of that? The limits aren't the problem here...
– Mathieu Guindon
Dec 7 at 15:24
FWIW the 64KB limit isn't on the exported file size / text source code, it's on the compiled p-code in the internal storage.
– Mathieu Guindon
Dec 7 at 15:36
add a comment |
4
Write smaller modules? Read up on cohesion and principles like Single Responsibility - your code will be easier to maintain, and you won't be dealing with module size limits.
– Mathieu Guindon
Dec 7 at 15:05
well, the point is, i'm just wondering if it could be a factor for my macros to be corrupting? Also excel randomly closes when copy, paste text into other sheets within the workbook causing the same macro corruption.
– ThisGuyJustNeedsHelp
Dec 7 at 15:23
I've read some articles pertaining to module size limits that causes corruption - the internal storage format hasn't changed in 20 years, what makes you think O365 changes any of that? The limits aren't the problem here...
– Mathieu Guindon
Dec 7 at 15:24
FWIW the 64KB limit isn't on the exported file size / text source code, it's on the compiled p-code in the internal storage.
– Mathieu Guindon
Dec 7 at 15:36
4
4
Write smaller modules? Read up on cohesion and principles like Single Responsibility - your code will be easier to maintain, and you won't be dealing with module size limits.
– Mathieu Guindon
Dec 7 at 15:05
Write smaller modules? Read up on cohesion and principles like Single Responsibility - your code will be easier to maintain, and you won't be dealing with module size limits.
– Mathieu Guindon
Dec 7 at 15:05
well, the point is, i'm just wondering if it could be a factor for my macros to be corrupting? Also excel randomly closes when copy, paste text into other sheets within the workbook causing the same macro corruption.
– ThisGuyJustNeedsHelp
Dec 7 at 15:23
well, the point is, i'm just wondering if it could be a factor for my macros to be corrupting? Also excel randomly closes when copy, paste text into other sheets within the workbook causing the same macro corruption.
– ThisGuyJustNeedsHelp
Dec 7 at 15:23
I've read some articles pertaining to module size limits that causes corruption - the internal storage format hasn't changed in 20 years, what makes you think O365 changes any of that? The limits aren't the problem here...
– Mathieu Guindon
Dec 7 at 15:24
I've read some articles pertaining to module size limits that causes corruption - the internal storage format hasn't changed in 20 years, what makes you think O365 changes any of that? The limits aren't the problem here...
– Mathieu Guindon
Dec 7 at 15:24
FWIW the 64KB limit isn't on the exported file size / text source code, it's on the compiled p-code in the internal storage.
– Mathieu Guindon
Dec 7 at 15:36
FWIW the 64KB limit isn't on the exported file size / text source code, it's on the compiled p-code in the internal storage.
– Mathieu Guindon
Dec 7 at 15:36
add a comment |
1 Answer
1
active
oldest
votes
The 64KB limit isn't on the exported file size, it's the maximum size of a compiled module.
If your module is less than 10K lines, if can be compiled.
A heavy but likely still healthy module would be 1K lines, tops - that seems to hover around 40KB when exported to a text file; 64KB doesn't strike me as completely indecent, although it is definitely above 1K lines of code, and thus could likely use some tweaks.
If your modules are named e.g. Module8 or Utilities, verify how cohesive their members are - is it all related to the same functionality? Or does it feel like random functions were dumped in there?
Look for repeated code, refactor it. Extract methods, parameterize them, watch your module melt away, while retaining all its functionality.
The internal storage mechanics for VBA code, haven't changed in 20 years - I don't see a reason for that to have changed recently, especially since VBA is pretty much frozen now, and changing anything in the storage mechanics would break millions of things everywhere.
But, it's not impossible that something in O365 was changed recently (are you using an Insider build?), and something broke and your workbook somehow got corrupted.... but if your module is slightly above 64KB of text source code, it's very unlikely to be related: the compiled code would be much smaller than that... assuming the project compiles.
answered Dec 7 at 15:50
Mathieu Guindon
587211
All my modules when exported, is below 64KB and a few in the 100KB size. The workbook only randomly become corrupted. Sometimes copy paste from sheet to sheet within the workbook causes excel to crash. Then on reopening of the same workbook, the whole workbook is corrupted and I have to do the process stated in my post to recompile the macros for it to be able to reopen and everything works fine.
– ThisGuyJustNeedsHelp
Dec 7 at 15:54
The file size is irrelevant, only number of lines matter (storage size isn't shown anywhere).
– Mathieu Guindon
Dec 7 at 15:56
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1381665%2fis-the-64kb-size-limit-to-modules-corrupting-macros-for-excel-365%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The 64KB limit isn't on the exported file size, it's the maximum size of a compiled module.
If your module is less than 10K lines, if can be compiled.
A heavy but likely still healthy module would be 1K lines, tops - that seems to hover around 40KB when exported to a text file; 64KB doesn't strike me as completely indecent, although it is definitely above 1K lines of code, and thus could likely use some tweaks.
If your modules are named e.g. Module8 or Utilities, verify how cohesive their members are - is it all related to the same functionality? Or does it feel like random functions were dumped in there?
Look for repeated code, refactor it. Extract methods, parameterize them, watch your module melt away, while retaining all its functionality.
The internal storage mechanics for VBA code, haven't changed in 20 years - I don't see a reason for that to have changed recently, especially since VBA is pretty much frozen now, and changing anything in the storage mechanics would break millions of things everywhere.
But, it's not impossible that something in O365 was changed recently (are you using an Insider build?), and something broke and your workbook somehow got corrupted.... but if your module is slightly above 64KB of text source code, it's very unlikely to be related: the compiled code would be much smaller than that... assuming the project compiles.
answered Dec 7 at 15:50
Mathieu Guindon
587211
All my modules when exported, is below 64KB and a few in the 100KB size. The workbook only randomly become corrupted. Sometimes copy paste from sheet to sheet within the workbook causes excel to crash. Then on reopening of the same workbook, the whole workbook is corrupted and I have to do the process stated in my post to recompile the macros for it to be able to reopen and everything works fine.
– ThisGuyJustNeedsHelp
Dec 7 at 15:54
The file size is irrelevant, only number of lines matter (storage size isn't shown anywhere).
– Mathieu Guindon
Dec 7 at 15:56
add a comment |
The 64KB limit isn't on the exported file size, it's the maximum size of a compiled module.
If your module is less than 10K lines, if can be compiled.
A heavy but likely still healthy module would be 1K lines, tops - that seems to hover around 40KB when exported to a text file; 64KB doesn't strike me as completely indecent, although it is definitely above 1K lines of code, and thus could likely use some tweaks.
If your modules are named e.g. Module8 or Utilities, verify how cohesive their members are - is it all related to the same functionality? Or does it feel like random functions were dumped in there?
Look for repeated code, refactor it. Extract methods, parameterize them, watch your module melt away, while retaining all its functionality.
The internal storage mechanics for VBA code, haven't changed in 20 years - I don't see a reason for that to have changed recently, especially since VBA is pretty much frozen now, and changing anything in the storage mechanics would break millions of things everywhere.
But, it's not impossible that something in O365 was changed recently (are you using an Insider build?), and something broke and your workbook somehow got corrupted.... but if your module is slightly above 64KB of text source code, it's very unlikely to be related: the compiled code would be much smaller than that... assuming the project compiles.
answered Dec 7 at 15:50
Mathieu Guindon
587211
All my modules when exported, is below 64KB and a few in the 100KB size. The workbook only randomly become corrupted. Sometimes copy paste from sheet to sheet within the workbook causes excel to crash. Then on reopening of the same workbook, the whole workbook is corrupted and I have to do the process stated in my post to recompile the macros for it to be able to reopen and everything works fine.
– ThisGuyJustNeedsHelp
Dec 7 at 15:54
The file size is irrelevant, only number of lines matter (storage size isn't shown anywhere).
– Mathieu Guindon
Dec 7 at 15:56
add a comment |
The 64KB limit isn't on the exported file size, it's the maximum size of a compiled module.
If your module is less than 10K lines, if can be compiled.
A heavy but likely still healthy module would be 1K lines, tops - that seems to hover around 40KB when exported to a text file; 64KB doesn't strike me as completely indecent, although it is definitely above 1K lines of code, and thus could likely use some tweaks.
If your modules are named e.g. Module8 or Utilities, verify how cohesive their members are - is it all related to the same functionality? Or does it feel like random functions were dumped in there?
Look for repeated code, refactor it. Extract methods, parameterize them, watch your module melt away, while retaining all its functionality.
The internal storage mechanics for VBA code, haven't changed in 20 years - I don't see a reason for that to have changed recently, especially since VBA is pretty much frozen now, and changing anything in the storage mechanics would break millions of things everywhere.
But, it's not impossible that something in O365 was changed recently (are you using an Insider build?), and something broke and your workbook somehow got corrupted.... but if your module is slightly above 64KB of text source code, it's very unlikely to be related: the compiled code would be much smaller than that... assuming the project compiles.
answered Dec 7 at 15:50
Mathieu Guindon
587211
The 64KB limit isn't on the exported file size, it's the maximum size of a compiled module.
If your module is less than 10K lines, if can be compiled.
A heavy but likely still healthy module would be 1K lines, tops - that seems to hover around 40KB when exported to a text file; 64KB doesn't strike me as completely indecent, although it is definitely above 1K lines of code, and thus could likely use some tweaks.
If your modules are named e.g. Module8 or Utilities, verify how cohesive their members are - is it all related to the same functionality? Or does it feel like random functions were dumped in there?
Look for repeated code, refactor it. Extract methods, parameterize them, watch your module melt away, while retaining all its functionality.
The internal storage mechanics for VBA code, haven't changed in 20 years - I don't see a reason for that to have changed recently, especially since VBA is pretty much frozen now, and changing anything in the storage mechanics would break millions of things everywhere.
But, it's not impossible that something in O365 was changed recently (are you using an Insider build?), and something broke and your workbook somehow got corrupted.... but if your module is slightly above 64KB of text source code, it's very unlikely to be related: the compiled code would be much smaller than that... assuming the project compiles.
answered Dec 7 at 15:50
Mathieu Guindon
587211
answered Dec 7 at 15:50
Mathieu Guindon
587211
answered Dec 7 at 15:50
Mathieu Guindon
587211
answered Dec 7 at 15:50
Mathieu Guindon
587211
587211
All my modules when exported, is below 64KB and a few in the 100KB size. The workbook only randomly become corrupted. Sometimes copy paste from sheet to sheet within the workbook causes excel to crash. Then on reopening of the same workbook, the whole workbook is corrupted and I have to do the process stated in my post to recompile the macros for it to be able to reopen and everything works fine.
– ThisGuyJustNeedsHelp
Dec 7 at 15:54
The file size is irrelevant, only number of lines matter (storage size isn't shown anywhere).
– Mathieu Guindon
Dec 7 at 15:56
add a comment |
All my modules when exported, is below 64KB and a few in the 100KB size. The workbook only randomly become corrupted. Sometimes copy paste from sheet to sheet within the workbook causes excel to crash. Then on reopening of the same workbook, the whole workbook is corrupted and I have to do the process stated in my post to recompile the macros for it to be able to reopen and everything works fine.
– ThisGuyJustNeedsHelp
Dec 7 at 15:54
The file size is irrelevant, only number of lines matter (storage size isn't shown anywhere).
– Mathieu Guindon
Dec 7 at 15:56
All my modules when exported, is below 64KB and a few in the 100KB size. The workbook only randomly become corrupted. Sometimes copy paste from sheet to sheet within the workbook causes excel to crash. Then on reopening of the same workbook, the whole workbook is corrupted and I have to do the process stated in my post to recompile the macros for it to be able to reopen and everything works fine.
– ThisGuyJustNeedsHelp
Dec 7 at 15:54
All my modules when exported, is below 64KB and a few in the 100KB size. The workbook only randomly become corrupted. Sometimes copy paste from sheet to sheet within the workbook causes excel to crash. Then on reopening of the same workbook, the whole workbook is corrupted and I have to do the process stated in my post to recompile the macros for it to be able to reopen and everything works fine.
– ThisGuyJustNeedsHelp
Dec 7 at 15:54
The file size is irrelevant, only number of lines matter (storage size isn't shown anywhere).
– Mathieu Guindon
Dec 7 at 15:56
The file size is irrelevant, only number of lines matter (storage size isn't shown anywhere).
– Mathieu Guindon
Dec 7 at 15:56
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1381665%2fis-the-64kb-size-limit-to-modules-corrupting-macros-for-excel-365%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
Write smaller modules? Read up on cohesion and principles like Single Responsibility - your code will be easier to maintain, and you won't be dealing with module size limits.
– Mathieu Guindon
Dec 7 at 15:05
well, the point is, i'm just wondering if it could be a factor for my macros to be corrupting? Also excel randomly closes when copy, paste text into other sheets within the workbook causing the same macro corruption.
– ThisGuyJustNeedsHelp
Dec 7 at 15:23
I've read some articles pertaining to module size limits that causes corruption - the internal storage format hasn't changed in 20 years, what makes you think O365 changes any of that? The limits aren't the problem here...
– Mathieu Guindon
Dec 7 at 15:24
FWIW the 64KB limit isn't on the exported file size / text source code, it's on the compiled p-code in the internal storage.
– Mathieu Guindon
Dec 7 at 15:36