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

**Dschwarz** · 01-23-2024, 08:47 AM

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** · 01-23-2024, 01:09 PM

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** · 01-23-2024, 01:49 PM

Originally Posted by 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.

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** · 01-25-2024, 11:58 AM

Originally Posted by Dschwarz

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** · 01-25-2024, 12:40 PM

Originally Posted by Paul_Hossler

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** · 01-25-2024, 01:00 PM

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** · 01-25-2024, 02:04 PM

Originally Posted by Paul_Hossler

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** · 01-25-2024, 07:29 PM

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** · 01-25-2024, 07:51 PM

Originally Posted by Paul_Hossler

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** · 01-25-2024, 08:17 PM

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

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

Thread Tools

Display

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

Posting Permissions