Macros run in spreadsheet but produce errors when run in vba editor

[Dschwarz]

When I attempt to run a macro from the tools menu in the VBA editor I get varying errors (tools -> macros). Initially I could open the macros menu but when I attempt to run a particular macro from the list, excel crashes and closes without any error report. I tried opening the file again and all the macros seem to run in the spreadsheet but I get the same error when attempting to run the macros with the vba editor. I thought maybe the VBA code file is corrupt so I have tried stripping the VBA code out of the file, saving it as a non-macro enabled file, and then reassemble a new macro enabled file. Now I get an "out of memory error" before the macros menu even opens in the editor.

[Aussiebear]

Select all your Macros and Comment them out. If this enables your file to load and run then, one by one, uncomment each macro and test.

[Dschwarz]

Select all your Macros and Comment them out. If this enables your file to load and run then, one by one, uncomment each macro and test.

Ok, that makes sense. I repeated the method of stripping the vba code out, saving a new file, and putting the code back in and everything started working again without any changes to the code (no more insufficient memory error). It made me wonder if there is some type of hidden compiling log somewhere (since the vba code seems to compile automatically) that would help with troubleshooting? I guess I will need to see if I can reproduce the problem to try that method. Thanks!

[Paul_Hossler]

Ok, that makes sense. I repeated the method of stripping the vba code out, saving a new file, and putting the code back in and everything started working again without any changes to the code (no more insufficient memory error). It made me wonder if there is some type of hidden compiling log somewhere (since the vba code seems to compile automatically) that would help with troubleshooting? I guess I will need to see if I can reproduce the problem to try that method. Thanks!


http://www.appspro.com/Utilities/CodeCleaner.htm


During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

32 bit Excel only, but I use the Code Cleaner addin all the time and except of my programming or my logic errors it usually fixes Excel-wierdness

[Dschwarz]

32 bit Excel only, but I use the Code Cleaner addin all the time and except of my programming or my logic errors it usually fixes Excel-wierdness

Thank you, for the input Paul! My code is not particularly large or complicated but I have done many revisions to the VBA code and the spreadsheet as a beginner. At this point, I don't see any options for error handling beyond what I have already implemented but there is still a lack of stability in the final product. So, it seemed like the problem must exist behind the scenes with the Compiling/Interpreting process of the VBA instructions. Is Ribbon Commander a good solution to this problem? I found a video on youtube entitled "Ribbon Commander (RC): Testimonial from an expert about VBA code cleaner" (https://youtu.be/Kc-S0_NJyow?si=yzHpoMow85oRVBiW)

[Paul_Hossler]

So, it seemed like the problem must exist behind the scenes with the Compiling/Interpreting process of the VBA instructions.

Unlikely, but possibly possible


Is Ribbon Commander a good solution to this problem?

Don't know about RC but like the CC it seems to me that they just automate the manual export and then import of the various modules that you already did

It is possible that something in the worksheets(s) is corrupted so you could try to copy and paste just the cells used into a new workbook and then copy the modules over


I don't see an attachment or code listing so it's hard to guess

[Dschwarz]

Unlikely, but possibly possible



Don't know about RC but like the CC it seems to me that they just automate the manual export and then import of the various modules that you already did

It is possible that something in the worksheets(s) is corrupted so you could try to copy and paste just the cells used into a new workbook and then copy the modules over


I don't see an attachment or code listing so it's hard to guess


Paul, I attached the whole VBA code minus redundant macros with identical structure but different cell references (mostly clear, copy, and default settings buttons scattered around the workbook). To summarize the code below, the workbook opens with virtually no excel UI as the file should not be modified and saved but rather used as a calculator with a very specific function. The workbook is locked with the exception of user input fields. Data from the user input fields is stored in text files that are exported from and imported into the spreadsheet. There is also a print function to print a predefined report as none of the excel menus are accessible with the stripped UI. I would appreciate any input you have regarding the code and any error handling that may be missing.

[This Workbook]


Private Sub Workbook_Open()
'Hide user interface when the workbook is opened
Call UIHide
'Pause screen updating
Application.ScreenUpdating = False
'Clear the contents of selected cells
Sheet1.Range("E7,E8").Value = ""
Sheet1.Range("E12,E13,E14").Value = ""
Sheet1.Range("E18,E19,E20,E21").Value = ""
Sheet6.Range("K7,M7").Value = ""
Sheet5.Range("B6,D6,G6,I6").Value = ""
Sheet5.Range("B14,D14,G14,I14").Value = ""
Sheet5.Range("B23,D23,G23,I23").Value = ""
Sheet5.Range("B31,D31,G31,I31").Value = ""
Sheet4.Range("B6,D6,G6,I6").Value = ""
Sheet4.Range("B14,D14,G14,I14").Value = ""
Sheet4.Range("B36,D36,G36,I36").Value = ""
Sheet4.Range("B44,D44,G44,I44").Value = ""
Sheet21.Range("A7,C7,P7,R7").Value = ""
Sheet21.Range("G9,I9,N9,P9").Value = ""
Sheet21.Range("A19,C19,P19,R19").Value = ""
Sheet21.Range("A25,C25,P25,R25").Value = ""
Sheet21.Range("G27,I27,N27,P27").Value = ""
Sheet21.Range("A37,C37,P37,R37").Value = ""
Sheet3.Range("D7,F7,O7,Q7").Value = ""
Sheet3.Range("B9,D9,I9,K9").Value = ""
Sheet3.Range("D19,F19,O19,Q19").Value = ""
Sheet3.Range("D25,F25,O25,Q25").Value = ""
Sheet3.Range("B27,D27,I27,K27").Value = ""
Sheet3.Range("B37,D37,O37,Q37").Value = ""
Sheet31.Range("A7,C7,Q7,S7").Value = ""
Sheet31.Range("C9,E9,H9,J9").Value = ""
Sheet31.Range("A19,C19,Q19,S19").Value = ""
Sheet31.Range("A25,C25,Q25,S25").Value = ""
Sheet31.Range("C27,E27,H27,J27").Value = ""
Sheet31.Range("A37,C37,Q37,S37").Value = ""
Sheet11.Range("F8,J8,M8,P8").Value = ""
Sheet11.Range("F9,J9,M9,P9").Value = ""
'Set default values
Sheet6.Range("T17").Value = 51
Sheet4.Range("Z33").Value = 40.25
Sheet3.Range("AE39").Value = 12
Sheet3.Range("AE40").Value = 9
Sheet21.Range("AF39").Value = 12
Sheet21.Range("AF40").Value = 9
Sheet31.Range("AG39").Value = 12
Sheet31.Range("AG40").Value = 9
Sheet11.Range("W43").Value = 45
Sheet8.Range("AB6").Value = 0.0359
Sheet8.Range("AB7").Value = 0.0598
Sheet8.Range("AB8").Value = 0.1196
'Restore screen updating
Application.ScreenUpdating = True
'Set calculation method to automatic mode
Application.Calculation = xlAutomatic
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Test if exit button was pressed accidentally before saving data
If MsgBox("Exit now?" & vbCr & "(any unsaved data will be lost)", vbQuestion + vbYesNo + vbDefaultButton2, "Exit ") = vbNo Then
'Cancel the call to close the application if user inputs "no"
Cancel = True
Else
'Suppress excel alert messages
Application.DisplayAlerts = False
'Set save status to true
ThisWorkbook.Saved = True
'Close application
Application.Quit
End If
End Sub

[Module 1]

Sub UIHide()
'Hide elements of the standard excel user interface that are not needed
With Application
.WindowState = xlMaximized
.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",False)"
.CommandBars("Full Screen").Visible = False
.CommandBars("Worksheet Menu Bar").Enabled = False
.DisplayStatusBar = True
.DisplayScrollBars = False
.DisplayFormulaBar = False
End With
'Hide elements of the active window that are not needed
With ActiveWindow
.DisplayWorkbookTabs = True
.DisplayHeadings = False
.DisplayRuler = False
.DisplayFormulas = False
.DisplayGridlines = False
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
End Sub

Sub UIShow()
'Restore standard excel configurations when the workbook is closed
With Application
.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",True)"
.DisplayStatusBar = True
.DisplayScrollBars = True
.DisplayFormulaBar = True
End With
'Restore standard window configurations when the workbook is closed
With ActiveWindow
.DisplayWorkbookTabs = True
.DisplayRuler = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
End Sub

Sub todays_date()
'Enter todays date into the date field
Range("E14").Value = Date
End Sub

Sub clear_all()
'Declare variables
Dim answerA As Integer
'Test if clear button was pressed accidentally before deleting data
answerA = MsgBox("Clear data from all pages?", vbQuestion + vbYesNo + vbDefaultButton2, "Clear All")
If answerA = vbNo Then
Exit Sub
End If
'Clear the contents of selected cells
Sheet1.Range("E7,E8").Value = ""
Sheet1.Range("E12,E13,E14").Value = ""
Sheet1.Range("E18,E19,E20,E21").Value = ""
Sheet6.Range("K7,M7").Value = ""
Sheet5.Range("B6,D6,G6,I6").Value = ""
Sheet5.Range("B14,D14,G14,I14").Value = ""
Sheet5.Range("B23,D23,G23,I23").Value = ""
Sheet5.Range("B31,D31,G31,I31").Value = ""
Sheet4.Range("B6,D6,G6,I6").Value = ""
Sheet4.Range("B14,D14,G14,I14").Value = ""
Sheet4.Range("B36,D36,G36,I36").Value = ""
Sheet4.Range("B44,D44,G44,I44").Value = ""
Sheet21.Range("A7,C7,P7,R7").Value = ""
Sheet21.Range("G9,I9,N9,P9").Value = ""
Sheet21.Range("A19,C19,P19,R19").Value = ""
Sheet21.Range("A25,C25,P25,R25").Value = ""
Sheet21.Range("G27,I27,N27,P27").Value = ""
Sheet21.Range("A37,C37,P37,R37").Value = ""
Sheet3.Range("D7,F7,O7,Q7").Value = ""
Sheet3.Range("B9,D9,I9,K9").Value = ""
Sheet3.Range("D19,F19,O19,Q19").Value = ""
Sheet3.Range("D25,F25,O25,Q25").Value = ""
Sheet3.Range("B27,D27,I27,K27").Value = ""
Sheet3.Range("B37,D37,O37,Q37").Value = ""
Sheet31.Range("A7,C7,Q7,S7").Value = ""
Sheet31.Range("C9,E9,H9,J9").Value = ""
Sheet31.Range("A19,C19,Q19,S19").Value = ""
Sheet31.Range("A25,C25,Q25,S25").Value = ""
Sheet31.Range("C27,E27,H27,J27").Value = ""
Sheet31.Range("A37,C37,Q37,S37").Value = ""
Sheet11.Range("F8,J8,M8,P8").Value = ""
Sheet11.Range("F9,J9,M9,P9").Value = ""
End Sub

Sub copy_T_O()
'Copy intial data entries to the final fields
Range("B23").Value = Range("B6").Value
Range("D23").Value = Range("D6").Value
Range("G23").Value = Range("G6").Value
Range("I23").Value = Range("I6").Value
Range("B31").Value = Range("B14").Value
Range("D31").Value = Range("D14").Value
Range("G31").Value = Range("G14").Value
Range("I31").Value = Range("I14").Value
End Sub

Sub default_A_CC_parameter()
'Restore default
Range("T17").Value = 51
End Sub

Sub print_setup_dialog()
'Open print dialog box to print report
Application.Dialogs(xlDialogPrint).Show
End Sub

Sub choose_directory()
'Declare variables
Dim path As String
Dim folder As FileDialog
'Prompt user to select a save directory with a dialog box
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
With folder
.Title = "Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub 'Check if user clicked cancel button
path = .SelectedItems(1)
End With
'Display chosen directory path in the report sheet interface
Range("M3").Value = path
End Sub

Sub suggest_file_name()
'Declare variables
Dim customer As String
Dim WO As String
'Reference customer name and work order number
customer = Sheet1.Range("E7")
WO = Sheet1.Range("E8")
'Combine the customer name and alignment number into a suggested file name
Sheet2.Range("M5") = customer & " - " & WO
End Sub

Sub save_data()
'Declare variables
Dim d(122) As String
Dim fname As String
Dim directory As String
Dim test As String
Dim answer As Integer
Dim folder As String
Dim fso As Object
Dim streamout As Object
Dim n As Integer
'Job data entries into data array
d(0) = Sheet1.Range("E7")
d(1) = Sheet1.Range("E8")
d(2) = Sheet1.Range("E12")
d(3) = Sheet1.Range("E13")
d(4) = Sheet1.Range("E14")
d(5) = Sheet1.Range("E18")
d(6) = Sheet1.Range("E19")
d(7) = Sheet1.Range("E20")
d(8) = Sheet1.Range("E21")
d(9) = Sheet6.Range("K7")
d(10) = Sheet6.Range("M7")
d(11) = Sheet5.Range("B6")
d(12) = Sheet5.Range("D6")
d(13) = Sheet5.Range("G6")
d(14) = Sheet5.Range("I6")
d(15) = Sheet5.Range("B14")
d(16) = Sheet5.Range("D14")
d(17) = Sheet5.Range("G14")
d(18) = Sheet5.Range("I14")
d(19) = Sheet5.Range("B23")
d(20) = Sheet5.Range("D23")
d(21) = Sheet5.Range("G23")
d(22) = Sheet5.Range("I23")
d(23) = Sheet5.Range("B31")
d(24) = Sheet5.Range("D31")
d(25) = Sheet5.Range("G31")
d(26) = Sheet5.Range("I31")
d(27) = Sheet4.Range("B6")
d(28) = Sheet4.Range("D6")
d(29) = Sheet4.Range("G6")
d(30) = Sheet4.Range("I6")
d(31) = Sheet4.Range("B14")
d(32) = Sheet4.Range("D14")
d(33) = Sheet4.Range("G14")
d(34) = Sheet4.Range("I14")
d(35) = Sheet4.Range("B36")
d(36) = Sheet4.Range("D36")
d(37) = Sheet4.Range("G36")
d(38) = Sheet4.Range("I36")
d(39) = Sheet4.Range("B44")
d(40) = Sheet4.Range("D44")
d(41) = Sheet4.Range("G44")
d(42) = Sheet4.Range("I44")
d(43) = Sheet21.Range("A7")
d(44) = Sheet21.Range("C7")
d(45) = Sheet21.Range("P7")
d(46) = Sheet21.Range("R7")
d(47) = Sheet21.Range("G9")
d(48) = Sheet21.Range("I9")
d(49) = Sheet21.Range("N9")
d(50) = Sheet21.Range("P9")
d(51) = Sheet21.Range("A19")
d(52) = Sheet21.Range("C19")
d(53) = Sheet21.Range("P19")
d(54) = Sheet21.Range("R19")
d(55) = Sheet21.Range("A25")
d(56) = Sheet21.Range("C25")
d(57) = Sheet21.Range("P25")
d(58) = Sheet21.Range("R25")
d(59) = Sheet21.Range("G27")
d(60) = Sheet21.Range("I27")
d(61) = Sheet21.Range("N27")
d(62) = Sheet21.Range("P27")
d(63) = Sheet21.Range("A37")
d(64) = Sheet21.Range("C37")
d(65) = Sheet21.Range("P37")
d(66) = Sheet21.Range("R37")
d(67) = Sheet3.Range("D7")
d(68) = Sheet3.Range("F7")
d(69) = Sheet3.Range("O7")
d(70) = Sheet3.Range("Q7")
d(71) = Sheet3.Range("B9")
d(72) = Sheet3.Range("D9")
d(73) = Sheet3.Range("I9")
d(74) = Sheet3.Range("K9")
d(75) = Sheet3.Range("D19")
d(76) = Sheet3.Range("F19")
d(77) = Sheet3.Range("O19")
d(78) = Sheet3.Range("Q19")
d(79) = Sheet3.Range("D25")
d(80) = Sheet3.Range("F25")
d(81) = Sheet3.Range("O25")
d(82) = Sheet3.Range("Q25")
d(83) = Sheet3.Range("B27")
d(84) = Sheet3.Range("D27")
d(85) = Sheet3.Range("I27")
d(86) = Sheet3.Range("K27")
d(87) = Sheet3.Range("B37")
d(88) = Sheet3.Range("D37")
d(89) = Sheet3.Range("O37")
d(90) = Sheet3.Range("Q37")
d(91) = Sheet31.Range("A7")
d(92) = Sheet31.Range("C7")
d(93) = Sheet31.Range("Q7")
d(94) = Sheet31.Range("S7")
d(95) = Sheet31.Range("C9")
d(96) = Sheet31.Range("E9")
d(97) = Sheet31.Range("H9")
d(98) = Sheet31.Range("J9")
d(99) = Sheet31.Range("A19")
d(100) = Sheet31.Range("C19")
d(101) = Sheet31.Range("Q19")
d(102) = Sheet31.Range("S19")
d(103) = Sheet31.Range("A25")
d(104) = Sheet31.Range("C25")
d(105) = Sheet31.Range("Q25")
d(106) = Sheet31.Range("S25")
d(107) = Sheet31.Range("C27")
d(108) = Sheet31.Range("E27")
d(109) = Sheet31.Range("H27")
d(110) = Sheet31.Range("J27")
d(111) = Sheet31.Range("A37")
d(112) = Sheet31.Range("C37")
d(113) = Sheet31.Range("Q37")
d(114) = Sheet31.Range("S37")
d(115) = Sheet11.Range("F8")
d(116) = Sheet11.Range("J8")
d(117) = Sheet11.Range("M8")
d(118) = Sheet11.Range("P8")
d(119) = Sheet11.Range("F9")
d(120) = Sheet11.Range("J9")
d(121) = Sheet11.Range("M9")
d(122) = Sheet11.Range("P9")
'Reference the directory chosen by the user
directory = Sheet2.Range("M3").Value
'If a directory was not entered, exit the subroutine with a message
If directory = "" Then
MsgBox ("Data file not written. Please choose a directory.")
Exit Sub
End If
'Evaluate the folders existence in the directory
folder = Dir(directory, vbDirectory)
'If the folder does not exist in the choosen directoy, exit the subroutine with a message
If folder = vbNullString Then
MsgBox ("Data file not written. Please choose a valid directory.")
Exit Sub
End If
'Reference the file name chosen by the user
fname = Sheet2.Range("M5").Value
'If a file name was not entered, exit the subroutine with a message
If fname = "" Then
MsgBox ("Data file not written. Please enter a file name.")
Exit Sub
End If
'If the file name contains illegal characters, exit the subroutine with a message
If IsValidFileName(fname) Then
Else
MsgBox ("Data file not written. Please enter a valid file name.")
Exit Sub
End If
'Test if the file already exists and if it does, ask the user if they want to over write it
test = Dir(directory & "" & fname & ".txt")
If test <> vbNullString Then
answer = MsgBox("Overwrite File?", vbQuestion + vbYesNo + vbDefaultButton2, "File Already Exists")
If answer = vbNo Then
Exit Sub
End If
End If
'Create a new file system object
Set fso = CreateObject("Scripting.FileSystemObject")
'Create a new file with the user selected name and directory and open it for writing
Set streamout = fso.CreateTextFile(directory & "" & fname & ".txt")
'Write each data point from the array into a separate line of the new text file
For n = 0 To 122
streamout.WriteLine (d(n))
Next
'Close the newly created text file
streamout.Close
'Inform user if the file was created successfully
If fso.FileExists(directory & "" & fname & ".txt") Then
MsgBox "Data file written."
Else
MsgBox "Data file not written. Please check file name and directory."
End If
End Sub

Sub open_data()
'Declare variables
Dim fopen As FileDialog
Dim fpath As String
Dim fso2 As Object
Dim streamin As Object
Dim i As Integer
Dim r(122) As String
'Create a dialog box to select a file to open
Set fopen = Application.FileDialog(msoFileDialogFilePicker)
'Configure the dialog box to select a single text file only
fopen.AllowMultiSelect = False
fopen.Title = "Select file to open"
fopen.Filters.Clear
fopen.Filters.Add "Text Files", "*.txt"
'Open the dialog box and save the file path selected by the user
If fopen.Show = -1 Then
fpath = fopen.SelectedItems(1)
Else
Exit Sub
End If
'Create a new file system object
Set fso2 = CreateObject("Scripting.FileSystemObject")
'Open the user selected file for reading
Set streamin = fso2.openTextFile(fpath)
'Read data from each line of the file into the corresponding position in the array
i = 0
Do Until i = 123
r(i) = streamin.ReadLine
i = i + 1
Loop
'Close the text file when reading is finished
streamin.Close
'Job data entries into data array
Sheet1.Range("E7").Value = r(0)
Sheet1.Range("E8").Value = r(1)
Sheet1.Range("E12").Value = r(2)
Sheet1.Range("E13").Value = r(3)
Sheet1.Range("E14").Value = r(4)
Sheet1.Range("E18").Value = r(5)
Sheet1.Range("E19").Value = r(6)
Sheet1.Range("E20").Value = r(7)
Sheet1.Range("E21").Value = r(8)
Sheet6.Range("K7").Value = r(9)
Sheet6.Range("M7").Value = r(10)
Sheet5.Range("B6").Value = r(11)
Sheet5.Range("D6").Value = r(12)
Sheet5.Range("G6").Value = r(13)
Sheet5.Range("I6").Value = r(14)
Sheet5.Range("B14").Value = r(15)
Sheet5.Range("D14").Value = r(16)
Sheet5.Range("G14").Value = r(17)
Sheet5.Range("I14").Value = r(18)
Sheet5.Range("B23").Value = r(19)
Sheet5.Range("D23").Value = r(20)
Sheet5.Range("G23").Value = r(21)
Sheet5.Range("I23").Value = r(22)
Sheet5.Range("B31").Value = r(23)
Sheet5.Range("D31").Value = r(24)
Sheet5.Range("G31").Value = r(25)
Sheet5.Range("I31").Value = r(26)
Sheet4.Range("B6").Value = r(27)
Sheet4.Range("D6").Value = r(28)
Sheet4.Range("G6").Value = r(29)
Sheet4.Range("I6").Value = r(30)
Sheet4.Range("B14").Value = r(31)
Sheet4.Range("D14").Value = r(32)
Sheet4.Range("G14").Value = r(33)
Sheet4.Range("I14").Value = r(34)
Sheet4.Range("B36").Value = r(35)
Sheet4.Range("D36").Value = r(36)
Sheet4.Range("G36").Value = r(37)
Sheet4.Range("I36").Value = r(38)
Sheet4.Range("B44").Value = r(39)
Sheet4.Range("D44").Value = r(40)
Sheet4.Range("G44").Value = r(41)
Sheet4.Range("I44").Value = r(42)
Sheet21.Range("A7").Value = r(43)
Sheet21.Range("C7").Value = r(44)
Sheet21.Range("P7").Value = r(45)
Sheet21.Range("R7").Value = r(46)
Sheet21.Range("G9").Value = r(47)
Sheet21.Range("I9").Value = r(48)
Sheet21.Range("N9").Value = r(49)
Sheet21.Range("P9").Value = r(50)
Sheet21.Range("A19").Value = r(51)
Sheet21.Range("C19").Value = r(52)
Sheet21.Range("P19").Value = r(53)
Sheet21.Range("R19").Value = r(54)
Sheet21.Range("A25").Value = r(55)
Sheet21.Range("C25").Value = r(56)
Sheet21.Range("P25").Value = r(57)
Sheet21.Range("R25").Value = r(58)
Sheet21.Range("G27").Value = r(59)
Sheet21.Range("I27").Value = r(60)
Sheet21.Range("N27").Value = r(61)
Sheet21.Range("P27").Value = r(62)
Sheet21.Range("A37").Value = r(63)
Sheet21.Range("C37").Value = r(64)
Sheet21.Range("P37").Value = r(65)
Sheet21.Range("R37").Value = r(66)
Sheet3.Range("D7").Value = r(67)
Sheet3.Range("F7").Value = r(68)
Sheet3.Range("O7").Value = r(69)
Sheet3.Range("Q7").Value = r(70)
Sheet3.Range("B9").Value = r(71)
Sheet3.Range("D9").Value = r(72)
Sheet3.Range("I9").Value = r(73)
Sheet3.Range("K9").Value = r(74)
Sheet3.Range("D19").Value = r(75)
Sheet3.Range("F19").Value = r(76)
Sheet3.Range("O19").Value = r(77)
Sheet3.Range("Q19").Value = r(78)
Sheet3.Range("D25").Value = r(79)
Sheet3.Range("F25").Value = r(80)
Sheet3.Range("O25").Value = r(81)
Sheet3.Range("Q25").Value = r(82)
Sheet3.Range("B27").Value = r(83)
Sheet3.Range("D27").Value = r(84)
Sheet3.Range("I27").Value = r(85)
Sheet3.Range("K27").Value = r(86)
Sheet3.Range("B37").Value = r(87)
Sheet3.Range("D37").Value = r(88)
Sheet3.Range("O37").Value = r(89)
Sheet3.Range("Q37").Value = r(90)
Sheet31.Range("A7").Value = r(91)
Sheet31.Range("C7").Value = r(92)
Sheet31.Range("Q7").Value = r(93)
Sheet31.Range("S7").Value = r(94)
Sheet31.Range("C9").Value = r(95)
Sheet31.Range("E9").Value = r(96)
Sheet31.Range("H9").Value = r(97)
Sheet31.Range("J9").Value = r(98)
Sheet31.Range("A19").Value = r(99)
Sheet31.Range("C19").Value = r(100)
Sheet31.Range("Q19").Value = r(101)
Sheet31.Range("S19").Value = r(102)
Sheet31.Range("A25").Value = r(103)
Sheet31.Range("C25").Value = r(104)
Sheet31.Range("Q25").Value = r(105)
Sheet31.Range("S25").Value = r(106)
Sheet31.Range("C27").Value = r(107)
Sheet31.Range("E27").Value = r(108)
Sheet31.Range("H27").Value = r(109)
Sheet31.Range("J27").Value = r(110)
Sheet31.Range("A37").Value = r(111)
Sheet31.Range("C37").Value = r(112)
Sheet31.Range("Q37").Value = r(113)
Sheet31.Range("S37").Value = r(114)
Sheet11.Range("F8").Value = r(115)
Sheet11.Range("J8").Value = r(116)
Sheet11.Range("M8").Value = r(117)
Sheet11.Range("P8").Value = r(118)
Sheet11.Range("F9").Value = r(119)
Sheet11.Range("J9").Value = r(120)
Sheet11.Range("M9").Value = r(121)
Sheet11.Range("P9").Value = r(122)
'Clear contents of the file name field
Sheet2.Range("M5").Value = ""
MsgBox ("Data file loaded.")
End Sub

[Paul_Hossler]

minus redundant macros with identical structure but different cell references (mostly clear, copy, and default settings buttons scattered around the workbook).


If you have all the macros in one module, it could be over the 64K limit (IIRC the limit is still there), I'd suggest breaking it up into some more logically devided modules

BTW, an unsolicited suggestion -- instead of "" you can use vbNullString since it saves a few bytes and I've been told processes a little faster, but I'd use .ClearContents instead of setting the cells = ""


From ChatGPT


In Microsoft Excel, there is a limit to the size of a module in the Visual Basic for Applications (VBA) editor. The limit is 64 kilobytes (KB) for a single module. If a module exceeds this size, you may encounter issues, and it is generally advisable to break down large modules into smaller ones for better organization and to avoid potential problems.

Breaking down your code into smaller, more manageable modules also has the advantage of improving code readability, maintainability, and debugging. It's good practice to modularize your code based on functionality, which makes it easier to understand and maintain over time.


Keep in mind that these limits can vary between different versions of Excel, and it's always a good idea to check the documentation for the specific version you are using for the most accurate information.

Other than the 64K limit, nothing jumped out me -- sorry

Do the input files always have 123 lines since you're counting and not using EOF()?

[Dschwarz]

If you have all the macros in one module, it could be over the 64K limit (IIRC the limit is still there), I'd suggest breaking it up into some more logically devided modules

BTW, an unsolicited suggestion -- instead of "" you can use vbNullString since it saves a few bytes and I've been told processes a little faster, but I'd use .ClearContents instead of setting the cells = ""


From ChatGPT



Other than the 64K limit, nothing jumped out me -- sorry

Do the input files always have 123 lines since you're counting and not using EOF()?


No, I absolutely appreciate the input. I will implement all of your suggestions. And, yes the input files always have 123 lines. Would it be better to use EOF to find the last line? Are there any potential problems that result from not using EOF?

I actually left out some important context with regards to the stability issues. The errors I initially posted about seem to be resolved after stripping the code from the workbook. However, the stability issues I am experiencing currently are related to the use of XLS Padlock. Do you have any experience with XLS Padlock? I am not sure, but at this point the stability issues may be a product of my antivirus software stopping processes due to an unknown .exe running from XLS Padlock.

[Paul_Hossler]

And, yes the input files always have 123 lines. Would it be better to use EOF to find the last line? Are there any potential problems that result from not using EOF?

I was just wondering since I personally don't assume that an input file always has the same number of lines




I actually left out some important context with regards to the stability issues. The errors I initially posted about seem to be resolved after stripping the code from the workbook. However, the stability issues I am experiencing currently are related to the use of XLS Padlock. Do you have any experience with XLS Padlock? I am not sure, but at this point the stability issues may be a product of my antivirus software stopping processes due to an unknown .exe running from XLS Padlock.

I see that Padlock is some sort of code protecting, exe making tool. No experience with it, but my first guess would be that it is the culpret

If your users are normally non-computer geeky, maybe just the built in protections within Excel/VBA would be enough