Ian Simmons
New Member
- Joined
- Jan 16, 2016
- Messages
- 1
I have a serious problem, it started several years ago with Excel 2007. Today I am using Office 365 and the problem persists.
I have a “Menu” program and about a dozen separate workbooks opened from the Menu. Menu.xlsm contains all the User Defined Types and all the general subs and functions. It is always open and is referenced in all the other workbooks.
All the workbooks are stored on a network server and accessed Read Only by the users. All data is stored in Random Access files and loaded when the respective workbook opens.
Sometimes all works well. Sometimes an error 59 – bad record length occurs. The highlighted command in the VBA editor is always a GET statement, never a PUT statement.
It is normal for one user to receive the error and another user, simultaneously, not receive it. How can this be? They both have a read only copy of the SAME program.
I have done endless searches on the internet and found many people with the same problem. The usual answer is: see Microsoft VBA help for GET. The code or UDT description is not the problem. The simultaneous operation above proves this.
In the current case I have:
• LOF(datafile.rnd) = 148004, also checked in properties.
• LEN(UDT) = 227 which agrees with design.
• Number of Records = 652 calculated from LOF / LEN which is correct
Several (temporary) workarounds have been proposed in the various posts on the internet; the most common is to fool the compiler into believing that compilation is required (cut / paste a section of code) and then Debug / Compile.
I have found that doing something else on the computer also works sometimes. (Open an explorer window / open a pdf file / even opening another excel workbook.)
I interpret this to mean that Windows / Excel has a problem managing memory, either because there is an omission in my code or because Windows/Excel actually has a bug.
Please can somebody help me.
I have a “Menu” program and about a dozen separate workbooks opened from the Menu. Menu.xlsm contains all the User Defined Types and all the general subs and functions. It is always open and is referenced in all the other workbooks.
All the workbooks are stored on a network server and accessed Read Only by the users. All data is stored in Random Access files and loaded when the respective workbook opens.
Sometimes all works well. Sometimes an error 59 – bad record length occurs. The highlighted command in the VBA editor is always a GET statement, never a PUT statement.
It is normal for one user to receive the error and another user, simultaneously, not receive it. How can this be? They both have a read only copy of the SAME program.
I have done endless searches on the internet and found many people with the same problem. The usual answer is: see Microsoft VBA help for GET. The code or UDT description is not the problem. The simultaneous operation above proves this.
In the current case I have:
• LOF(datafile.rnd) = 148004, also checked in properties.
• LEN(UDT) = 227 which agrees with design.
• Number of Records = 652 calculated from LOF / LEN which is correct
Several (temporary) workarounds have been proposed in the various posts on the internet; the most common is to fool the compiler into believing that compilation is required (cut / paste a section of code) and then Debug / Compile.
I have found that doing something else on the computer also works sometimes. (Open an explorer window / open a pdf file / even opening another excel workbook.)
I interpret this to mean that Windows / Excel has a problem managing memory, either because there is an omission in my code or because Windows/Excel actually has a bug.
Please can somebody help me.
