Intermittent Crash When Running Macro
Apr 9, 2008
I have having alot of frustation with a big group of Macro's I have written,
all the macros work and run fine most of the time but they crash Excel on occasion and the only way I have been able to fix it is to go into Visual Basic and opening the project up (it is password protected) but not making any changes, after doing this it seems to magically stop crashing, for a while, then doing it again.
I can't work out how or why that would make a difference,
I dont expect you to try and work out a problem in the code or anything like that cause it is too big and would be unreasonable of me to ask,
What I would like to know is if anyone else is aware of this sort of issue, why it happens and if there is any good cure for it?
I am running excel 2003 if that also makes any difference.
View 5 Replies
ADVERTISEMENT
Feb 22, 2014
Have a code to clear contents on one of my worksheets and it continually causes excel to freeze and forces me to exit the program. When the button is selected it takes approx. 30-45 seconds to clear the data and then excel freezes up. The code is as follows:
Sub()
Sheets("Sheet1").Range("B2:D16000").ClearContents
End Sub
The data in the range B2:D16000 comes from another macro that copies and pastes values to this sheet. I am also working on the mac 2011 version.
View 5 Replies
View Related
Feb 10, 2006
I'm just wondering if any of you can lend some help on a bug i'm encountering. When I press a button on my worksheet to call a macro that brings up a userform, the excel will crash, and give that "do you want to report this error", close down excel, and reopen on a blank worksheet. Strange thing is, this error is avoided if I save the workbook upon opening it, and then call that macro.
The workbook does not read data from external sources, so i dont think is that problem.
Has anyone encountered this?
View 9 Replies
View Related
Jul 10, 2012
I am trying to sort a bunch of data by a custom list from left to right and after running the macro successfully excel crashes upon saving. I have tried this on different computers, resaved, started from scratch and it always crashes so there must be something with the macro that is causing this.
here is my macro:
Edit: It seems that the last line of code is causing my problem. I have started from scratch and gone though all the code one at a time and saved after each run. everything is fine until the "Application.DeleteCustomList Application.CustomListCount" is run. after that excel crashes when saved.
VB:
Sub Macro3()
'
' Macro3 Macro
'
[Code]....
View 1 Replies
View Related
Feb 3, 2014
I am creating a report which lists gifts during a two week time period. Most donors have only one gift, listed in column C and D. Some donors have donated twice or three timies in the two week period. I would like these entries, which are intermittent in Cloumns H-I and K-L, to be automatically moved under the corresponding cells in columns C and D. This way an individuals two or three donations all end up in one column.
View 4 Replies
View Related
Feb 6, 2009
I have written a fairly large spread sheet, and I have consistently had excel crashes when the first form initialises at startup.
However if I resave the workbook over itself or sometimes as another name it will then work. Once it works - it will always work while I dont restart my computer. The next day the same workbook will crash on form startup. Once this happens t always crashes on startup. If it works OK first up it always works. However if I edit it the process may happen all over again. I am 100% their is no error in the code as in certain stages it has worked for months. It seams like some bug in excel?
Has anyone ever had this issue?
View 9 Replies
View Related
Jul 8, 2007
I am analysing market data to determine when trades should take place and when they should be closed. This works well. But I need to find the intermittent high value between the dates for purchase and the closing trades, and then insert these values in column S in the attached worksheet.
I don't know if a function can find these values or if a macro is needed (or if I should amend the existing macro that currently finds the
closing trades).
This macro is included in the attached spreadsheet. It Is:
....
View 9 Replies
View Related
Jun 13, 2014
K
L
M
N
L
L
-2.33
View 6 Replies
View Related
Feb 6, 2009
I have a multipage userform with about 7 pages. When page 5 is activated, it caused excel to completely lock up. This does not happen everytime but randomly, and I can't seem to figure out what is causing this.
Here is the code -
If Me.MultiPage1.Value = 5 Then
Call LaborCosts
End If
Sub LaborCosts()
If Not Range("A46").Text = "" Then
frmpricingtemplate.Label29.Visible = True
frmpricingtemplate.TextBox46.Visible = True
frmpricingtemplate.Label29.Caption = Range("A46").Text
Else
View 9 Replies
View Related
May 3, 2007
I searched the forum for an answer to my problem since I'm positive its a FAQ. Well I was right and many other users have asked a similiar question. My problem is this: I am looking to find the average of a set of numbers in column T that meets certain criteria. I was suggested by other user to use this function which finds me the total number. =SUMPRODUCT(--($E$1:$E$400="S"),--($H$1:$H$400=17),--($U$1:$U$400<>"BEBLT"),--($U$1:$U$400<>"NVLIV"),--($T$1:$T400 > 0),$T$1:$T$400)
I have tried inputing Count, Countif, Average in place of Sumproduct as well as Count enclosing the entire sumproduct function. Nothing seems to work for me. I have also tried using a SQL statement in my VBA to provide a count but it doesn't seem to produce the required result. The above function works like a charm for finding the sum, but I need to find the average of this number thus I am looking for the Count equivilant. I'm sure this is a simple problem and I'm still a rookie when it comes to functions in excel.
View 5 Replies
View Related
Apr 10, 2008
I've created a price sheet that I distribute to customers that is very customer specific. I have to add spacing, logo's, borders and highlighting depending on the products being listed. Because of all of this specialization I'm controlling the page breaks and overflow. I have a counter to keep the current line number of the report and another to determine the row within the current page. I've designed my form to have 77 lines per page and I call a pagebreak/overflow routine when I hit line 72 of each page. Works perfectly when I test on my PC and works about 80% of the time when I send to customers, but I have about 20% who have problems with page size and overflow. In working with them I've found that it's not just the printed copy, but also when they print preview on screen. The first few lines of the second page print at the bottom of the first, so the top of page two is cut off. Then 4 lines from page three print at the bottom of two and so on...
View 3 Replies
View Related
Aug 12, 2008
I have a set of userforms, two of which are big with 4 pages and many buttons, labels and textboxes that run or are involved in code being run from user input to the userform.
For some reason certain codes are cauing excel to fully crash, executing these codes will make excel crash each time and the only way to prevent it (that I have found) is to open the userform in VB editor and then close VB editor again, the code will then work perfectly for a while and then it will start crashing again (it is normally a few saves and open/closes later that this occurs).
Logically thinking, opening the userform in VB must reset something that gets corrupted or set incorrectly....
View 7 Replies
View Related
Mar 16, 2009
I am charting the following data (for a lot of countries, and the data changes based on a pull down menu which selects the country).
Yr1Yr2Yr3Yr4Yr5
Country1167155175188155
Country2145n.a.150140166
Country3142154n.a.170160
What I don't want is the line on the chart plummeting to zero when there is a "-", i.e. no value.
If I chart this instead:
Yr1Yr2Yr3Yr4Yr5
Country1167155175188155
Country2145[.....]150140166
Country3142154[.....]170160
..., where [.....] means a blank cell, then the 145 does now show up at all.
View 5 Replies
View Related
Nov 10, 2013
I've got a spreadsheet that lists employee hours worked by week, I'm trying to create a function that would give an automatic look-back of both 90 days and 6 months. I've found some useful answers in other threads, but they tend to hinge on a zero value cell signaling the latest week (not) worked; however, for this purpose, some employees may have zero value fields for some weeks but have worked others.
I'm looking for a formula that can be uniformly applied to all employees in the spreadsheet and average their weekly hours for the past 90 or 180 days without having to update the formula each week new hours are entered.
View 3 Replies
View Related
Jan 4, 2010
Intermittent run-time error '75'
Could not find the specified object.
Debug shows VBA Code that simply loads a form called controls
Sub loadcontrols()
Load Controls
Controls.Show
End Sub
Generally works upon restart. Can load and unload the form numerous times. Seems loading the form after a few minutes results in the form failing to load generating the error. Found no consistent cause thus far.
Results in a hard crash: Restart Excel as only option.
The Form Code for Controls loads a modeless transaprent form. Has been working for months. Don't know if this error is new or has been there all along.
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function ReleaseCapture Lib "user32" () As Long
Private Const GWL_STYLE As Long = (-16)
Private wHandle As Long 'For moving the form
Private m_objCFormSkin As CFormSkin 'For transparancy
View 9 Replies
View Related
Dec 31, 2008
I recently added some command buttons to 6 excel files each with about 20 sheets. most of the sheets got 6 new buttons. After I added the buttons I would get memory errors when I treied to open other files. So I upgraded to Office 2003 and Excel 2003 because I read that it had increased memory available. It did not seem to help much, and now on average about evry say 10 minutes Excel hangs for about 1o seconds, sometimes saying "Not Responding" up the top. Then it starts working again. MY PC has Server 2003 and 1GHz and 512MB of Ram. Here is some info:
=INFO("memavail")1048576
=INFO("memused")50769256
=INFO("totmem") 51817832
=INFO("numfile") 173
=INFO("osversion")Windows (32-bit) NT 5.02
=INFO("release") 11.0
I do not think the PC memory or chip is being stressed it seems to be Excel's memory?
View 4 Replies
View Related
Jul 3, 2007
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
View 9 Replies
View Related
Jan 7, 2009
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
View 9 Replies
View Related
Oct 9, 2007
I have a file with about 30 sheets and a size of 14 meg. When the file is saved by using the save button it seems to save. When the file is closed the Microsoft crash message appears. The changes seem to save and if you choose not to have Excel attempt to repair the file everything seems fine.
The other odd behavior the file has that is if you run a for loop on any tab it takes about 3-4 minutes to run. If you copy the tab to another book and run the same code it runs in 3-4 seconds. Any ideas?
View 6 Replies
View Related
Nov 16, 2009
I have a multi-tabbed spreadsheet that keeps crashing when I try to set up a reference from one tab to another tab in the same spreadsheet (e.g., =Sheet2!B1) . The spreadsheet does NOT have any macros in it although my Personal.xls DOES have numerous macros that I have been using for over 6 years with no problems. In any ase, when I start Excel without loading personal.xls, so that NO macros are loaded, the spreadsheet still causes Excel to crash when I try to set up the reference to another tab.
View 9 Replies
View Related
Apr 14, 2014
The situation is a Workbook_Activate macro is conflicting with a worksheet module which is a worksheet_calculate that also runs a macro when there is a query refresh from another book. How do I get the workbook activate macro to run before the worksheet one activates? Right now everything crashes.
View 1 Replies
View Related
Mar 11, 2009
This file contains the printing options of a larger userform. But I am having problems with the print preview crashing.
I'd click one of the printing options on the userform, this would bring up the print preview of that sheet.
And when I try to "test" its stability by going crazy on the view next/ previous page on preview, it'd crash. This is especially when I've got the screenupdating set to false.
Just wondering if anyone else can varify the crash/ suggest what maybe its cause?
View 9 Replies
View Related
Jun 3, 2012
I have problems with my excel crashing all the time when I try to run my code. It seems to happen in a loop where I open a .txt file and pull data from it, then close it, and open the next one...
There are no error code, just a message that Excel has stopped working and it will be closed, and then it is forced to restart. Just a mess!
This is my loop code:
Code:
Option Explicit
Dim Desktop As String
Sub Open60only()
Desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
Dim which_folder As String, which_file As String, which_string As String, tick As String
Dim lastrow As Long
Dim wb1 As Workbook, wb2 As Workbook
[code].....
View 9 Replies
View Related
Apr 15, 2014
I have a piece of code in one of my Macro's, if I run the macro and don't let this piece of code run everything works fine, when I let this piece of code execute the excel file keeps running until I go to save, at which point I get a message saying "Microsoft office excel has encountered a problem and needs to close. We are sorry for any inconvenience".
Code is as follows:
If MsgBox("Copy and hardcode this scenario?", vbYesNo) = vbYes Then
Calculate
Application.DisplayAlerts = False
[Code]....
View 3 Replies
View Related
Feb 7, 2007
what excel can process without having the program crash. Our company is using an old dos program to store payroll information (don't ask -they won't change it). Anyway - I have the ability to use pervasive software to pull the information into excel.
The problem here - is that the information is stored line by line. And in order for me to get useful information out of it, I need to sort those lines by date and a task code.
Here's the formula I am using.
=(SUMPRODUCT(($I$2:$I$65536>=$D$2)*($I$2:$I$65536
View 9 Replies
View Related
Nov 24, 2008
I have an add-in procedure that creates a new workbook and adds code to the new workbook's ThisWorkbook object (a Workbook_Open event handler). However, the .AddFromString and .InsertLines methods are causing the accursed "Excel has encountered a problem and needs to close" error. I've stripped the code down to rule out possible syntax errors in the generated code, and it still crashes.
If Not wbTemplate Is Nothing Then
Set objVBProj = wbTemplate.VBProject
If Not objVBProj Is Nothing Then
strCodeText = "Private Sub Workbook_Open()" & vbCr
strCodeText = strCodeText & "MsgBox " & Chr(34) & "test" & Chr(34) & vbCr
strCodeText = strCodeText & "End Sub ' Workbook_Open"
With objVBProj.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
.AddFromString strCodeText
End With
End If ' access vbproject
End If ' workbook exists
Set objVBProj = Nothing
View 9 Replies
View Related
May 19, 2009
I have an add-in that has one function in it. We plan on adding more, but not until we figure out what's happening. The one function is a Select- Case statement...send the function an alpha code and it returns a numeric account number based on the alpha. About 20 "cases" in total. The add-in loads fine and the function works fine from a spreadsheet cell. However, we're getting differing results when we try to access the function from VBA code. I've been able to duplicate this several times on three different computers on our network.
The VBA code will work fine, several times and then on the 4th or 5th attempt it will cause Excel to crash ==> "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience." If I " recover" the crashed file, comment out the line to call the function, run the program again it works. Then I can un-comment the line, re-run the program and it works fine again for 4 or 5 more attempts. I've tried running the Repair tool already. I've also copied the code out of the spreadsheet and started a new file. The same errors occur...works 4 or 5 times and then crashes the program. The add-in and the spreadsheet exists on networked drives.
View 3 Replies
View Related
Jan 8, 2007
I'm working with Excel 2000 on Windows XP machines. I have the following
Private Sub soInputBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
PushScreenUpdateState False
clearAllData
loadAllData
Worksheets("Main Screen").Activate
PopScreenUpdateState
End If
End Sub
Private Sub loadAllData()
soInputBox.Enabled = False
End Sub
The idea is this: the user can either type the value into the text box (soInputBox) and then click a button, or type the value into the box and then hit enter. They will both do the same thing; set in motion all the functions that load a bunch of data, etc.The code for the event handler for hitting enter is shown above. Inside of the loadAllData function, I disable the text box. What seems to be happening is that after the soInputBox_KeyDown function exits, Excel crashes. If I comment out the line to disable the text box, Excel doesn't crash. My question is this: is this a known problem, and is there a known workaround? I would like it so that the text box is disabled so that the value that was input cannot be changed accidentally during the rest of the programs execution, so if there's a way to change the control to be unmodifiable instead of disabling it that would be a reasonable alternative.
View 4 Replies
View Related
May 13, 2007
Examples:
This is the userform with the 'No Answer' boxes added into the user interface but not added to the code - this one doesn't crash excel: [url]
and this is the userform with the 'No Answer' boxes added into the code - this one will crash excel (but only when you open it directly from excel, it doesn't crash it if you test it out from vb): [url]
View 5 Replies
View Related
Jun 19, 2007
I am having a strange error occur. I am trying to debug a function in an add-in I developed, but whenever I try to hover over a value or add a watch, the whole application crashes. It runs without crashing when I don't try to debug it.
View 3 Replies
View Related
