Debug.Print Cause Crashes
Continuing with my goal to learn VBA and while playing with the following code, I encountered strange behavior of debug statements. In the code below I have put my comments to identify where the "funny behavior" is encountered.
Will appreciate if you can help me understand. I also have a simple request/question at the end of the code below: ....
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Debug Error 13
I have a working Excel 2007 macro that accesses two worksheets. I then added a third worksheet and want to access it from the macro. I get a debug error 13 Type Mismatch when the macro tries to access the third worksheet. I know I need to add the third worksheet on a pop-up but I don't remember how to open it.
View Replies!
View Related
Two Different Passwords Without Debug
I have a problem with a HUGE macro project I'm working on. The macro itself isn't huge, but it's being applied to about 10,000 files. The macro is updating information on three spreadsheets in each workbook, but the problem is that the password protection (and Macro in general) fails to unlock when the password was entered in UPPERCASE. So the password is "king" and/or "KING" depending on the sheet. There is no way of predicting which sheets will be caps and which will not, but it's frustrating when my macro stops every 5 files with an error because of a wrong password (even though it's always one of those two). Is there an IF THEN statement or something that I can do so that the macro doesn't stall every 30 seconds to 2 minutes... I have 10,000 files to crawl through.
View Replies!
View Related
Debug :: I Get: Division By Zero
When I debug this script I get: division by zero! In 3 lines over the debugging line, the script are writing out the variable "prosentref" as 1. Whatt can be wrong? (the value in cell T8769 is 1) Option Explicit Sub optimaliseringDrift() Dim Pgm1 As Integer Dim Pgm2 As Integer Dim PL As Integer Dim lamda_gm1 As Double Dim lamda_gm2 As Double Dim deriv1 As Double Dim deriv2 As Double Const Pgm1_max As Integer = 200 Const Pgm2_max As Integer = 300 Const Pgm1_min As Integer = 45 Const Pgm2_min As Integer = 40 Const deltaP As Integer = 5
View Replies!
View Related
Debug Statement
I am trying to put together an automation. I am having problem debuging the code. I am trying to have the automation autofilter with "Interior" and "Exterior" at column P. I am using a statement as follow to pick the cell that is showing at at second row each time after the autofilter ran. (the first row of Exterior and Interior are different) However, when i pick Exterior, the automation was able to locate the second cell after picking Exterior, but when I try Interior, then i will have a debug meesage. Range("Q2:Q" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(12)(1).Select The code that I used is as follow: Sub Macro4() Range("L1").Activate Selection.AutoFilter ' Selection.AutoFilter Selection.AutoFilter Field:=15, Criteria1:="=*Fixture*", Operator:=xlAnd Selection.AutoFilter Field:=16, Criteria1:="=" Range("P2:P" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(12)(1).Select ActiveCell.FormulaR1C1 = _
View Replies!
View Related
Find Function Debug
What I'm doing in this script is taking a whole bunch of variables and pasting them into a seperate sheet "Checks" which will construct a cashiers check. The problem is where I've highlighted the code in red. At that point I have copied the Vendor name to the clipboard - I then go to the "Vendor Info" page and search for that Vendor name. If that name exists on the page it works perfectly...moving one column to the right, grabbing the Address Line 1 and dropping it into the check, then going back and grabbing the Address Line 2 and dropping it into the check. The problem is when that Vendor name doesn't exist in the "Vendor Info" page...I want it to just paste two blank cells into the check (because I obviously don't have the address info for that Vendor)...but instead it gives me an error: Run-Time error '91': Object variable or With block variable not set Sub Checks() ' ' Checks Macro ' Macro recorded 2/14/2007 by Derek Minner ' ' Keyboard Shortcut: Ctrl+Shift+P ' Selection.Copy Sheets("Checks").Select Range("I4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
View Replies!
View Related
Debug F8 Stopped Working
I recently got a new laptop. I now have excel 2007 and vista. The debug step key, F8, does not work. It toggles out of excel when pushed. If I start the procedure in the debug menu, the SHIFT - F8 will step through though. Is there a setting I am missing? It must have somehting to do with VISTA since in the debug section it says to use F8.
View Replies!
View Related
Crash When Debug Addin
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 Replies!
View Related
Customizing Debug Pop Up Window
If a user decides to hit the esc key right in the middle of one of my procedures (bug free, etc), this will leave the file in an odd state. The file is set up so if the routine is re-run it cleans the file up and restores it to proper form. But hitting esc exposes my code which is password protected when the file is idle. I employ a little trick that makes it hard to hack into my worksheet code, but one achilles heel is the esc button. What I want is to have the file close if the routine is stopped in mid stream. This way the code is not exposed, and the user cannot monkey with the file. I wanted to set the file up where i have a custom debug window that when a button is pressd the file closes OR when the user escapes out of the debug window the file closes as well.
View Replies!
View Related
Reading String Debug Kicking Out
I'm trying to create a macro that will send out emails to people based on an excel file that has 4 columns, nickname, purchased, owed, email address. I am able now to open the email, fill out to field, subject, body (mostly) and have it send if i change the code. The problem is kinda 2 part. I am trying to read in the amount owed cell (which is formatted to be $#.##) and then display that in the email, as well as reading in any other things and storing them to declared values. I am able to step through the code, but once it gets to the first part of reading in the nickname, it goes to the next line, you step through that, and then it immediately kicks out to the cleanup section. Below is the code... (Also, why is it that I need to set the row int to 1 insted of 2 (2 being where my data starts on the excel chart)). Sub TestFile() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Dim row, purch, owed As Integer Dim nick As String row = 1 purch = 0 owed = 0
View Replies!
View Related
Debug Function Called From Another File
I have a macro that calls functions from another workbook. For example: Private Sub Worksheet_Change(ByVal target As Range) If ThisWorkbook.OpenCommon <> -1 Then _ Application.Run Workbooks("common.xls").name & "!Arkusz1.CellChange", target End Sub But when I get an error in CellChange function from "common.xls" workbook, debugger points only to the third line of the code above, not to the bad line in called function. Is there any way to change this behaviour (maybe some tool etc.)?
View Replies!
View Related
Macro Runs Fine In Debug But Not From ComboBox
I have a workbook that contains 9 worksheets. Four of the worksheets have the same row lables in column B and must always be the same. Three of the worksheets are fed from the 4th sheet so that the integrity of the lables is maintained. The user can change the lable value to meet their needs and they can insert or delete rows (within limits) as they see fit. The attached macro "Sub Delete_Row_All_Sheets()" works fine when I run it from Debug (F8). However, when I run it from Forms.ComboBox the macro returns to the "y = Application.InputBox("Enter The Row Number You Wish To Delete", _" screen. If I select cancel, the results I anticipated occur but I don't want the user to have to assume this will happen. Why does the macro return to this screen when executed from the ComboBox but not when executed from Debug?
View Replies!
View Related
Macro Works In Debug Mode But Not In Runtime
I am trying to open a xls file and convert into csv. My macro works when I'm in debug mode. but If i run the macro (Not in debug) mode then After opening a file control is not going to next function. What is problem? Even I am not getting any error too sub open file (FileName as string) Dim xlx As Object, xlw As Object, xls As Object, xlc As Object Dim RowNo As Integer Set xlx = CreateObject("Excel.Application") xlx.Visible = True 'Set xlw = xlx.Workbooks.Open(SourceFolder + "" + FileName) Workbooks.Open FileName:=SourceFolder + "" + FileName ' Columns("F:G").Select ' Selection.Delete Shift:=xlToLeft Save_in_WDrive (FileName) Set xlx = Nothing end sub
View Replies!
View Related
Debug Selection & Cash Register
I'm working on a sheet where I can specify sales by selection of month (Sheetname= ZoekenMaand). Everything works fine for the months "januari" and "februari" (yes, it's in dutch ) but from "maart" it doesn't seem to work any more.
View Replies!
View Related
Debug Code & See Results As Stepping Through
When stepping through my macro it moves through each line of the code showing me the code as it steps through. Is there a way to step through and see the results of the code as it is going through. I have an "IF THEN" statement in the macro that is not giving me any results so I think I must have an incorrect reference in the code, but I can't see it. I think if I could watch what it is supposed to be doing as I step through I might be able to find the error.
View Replies!
View Related
Allow User To Enter Break Mode (Debug)
i want to do is throw a break in my vba code if the user selects yes through a msgbox vbYesNo prompt and proceed in debug mode. is it possible to code in a break point in this fashion? my desired pseudo if user selects yes: set break point (to send to debug mode at that point... i dont mean to end the code with a END statement.) if user selects no: proceed program normally
View Replies!
View Related
Debug Assistance - Auto Date Entry
i try to lock cells in the area the VBA code affects. My goal is to actually have affected cells by this VBA code lock immediately after anything is entered in the affected cells. Area needing Debugging is in the If - Else portion. My worksheet will be protected. Private Sub Worksheet_Change(ByVal Target As Range) Dim LLoop As Integer Dim LTargetRange1 As String Dim LDestRange1 As String LLoop = 10 While LLoop <= 1000 'Link column B to A LTargetRange1 = "B" & CStr(LLoop) LDestRange1 = "A" & CStr(LLoop) If Not Intersect(Range(LTargetRange1), Target) Is Nothing Then If Len(Range(LTargetRange1).Value) > 0 Then Range(LDestRange1).Value = Date Else Range(LDestRange1).Value = Null End If End If LLoop = LLoop + 1 Wend End Sub
View Replies!
View Related
Pasting Data Debug Error On A Sheet With Borders
I have code that selects and copies data from one sheet onto another sheet. the sheet that is getting data copied too has borders defined. When i run the code i get a debug error. Here is the issue.... when i remove all the borders it works fine. I have tried everything i can think of to solve the problem but have had no luck. anybody have an idea what is causing this. i attached the workbook file so you can see what is going on.
View Replies!
View Related
Step Through & Debug VBA Macro Code
I don't understand the relationships that are happening and what the final value would be if for example the PT was Red. I'm confused and was curious how four lines could all equal different values(highlighted). UpUserPaint = UCase(RegPaint.Value) Dim icount As Integer LenRegPaint = Len(RegPaint.Value) For icount = 1 To LenRegPaint ValPAint = ValPAint + Asc(Mid(UpUserPaint, icount, 1)) Next icount If PTRed.Value Then ColorCompare = Abs(Pallet * (ValPAint + PrdtSeed - LenRegPaint)) ColorCompare = Abs( Round(ColorCompare / PrdtSeed - 7227, 0)) ColorCompare = ColorCompare And 6215971 ColorCompare = ColorCompare Xor 6215971.............................
View Replies!
View Related
Remove References Programmatically & How To Prevent Debug Error
i m working on an excel 2007 workbook with ms project functionality. in order to be able to use ms project from within excel i m using early binding. for those who dont know what early binding is: http://www.dicks-clicks.com/excel/olBinding.htm this works fine if the user has ms project installed on his/her pc. if the user does not have ms project installed i remove the broken references. this works fine. but my problem is that when i remove the reference to ms project, my vba project will not compile correctly. thus each time when a user opens the workbook the user gets a "compile error in hidden module" , since the functions of ms project are not available. for example i get a compile error in the following source Public Function getResID(ResName As String, ActiveProject As Project) As Integer Dim res As Resource For Each res In ActiveProject.Resources If res.name = ResName Then getResID = res.ID Exit Function End If getResID = -1 Next End Function i just want to know what i can do to prevent the compile error? would the use of late binding remove the problem? actually i dont want to use late binding since my source code is already very complex and it would be a huge effort to change it.
View Replies!
View Related
OpenText Function In Macro Hangs Unless Stepping Through Code In Debug
We have had a macro running for a few years (Excel 2007 now, but started in 2003) that imports about 35 text files into separate sheets, creates a calculated SUM field for each sheet and copies that value to a title (or summary) sheet. We use this 2 to 4 times per year at inventory time, copying to a new file and deleting the old data before running the macro. The imported files initially create new workbooks, but the data is copied to the initial workbook into a distinct sheets for each file. Recently (well, last August) this macro started hanging after importing (Workbooks.OpenText) a number of files, and not necessarily the same file every time (on repeated runs.) While trying to figure out the problem, I have now managed to get it to hang every time on the first file! However, if I am stepping through in the debugger it continues past the OpenText command and on F5:Run/Continue will then continue processing the rest of the files normally. If I delete the first file before running the macro, it then hangs on the second file instead. Without debugging, the first file will import, display on-screen, and there it stops. If I put a break-point on the very next instruction after the import, that break-point is never reached. THINGS I'VE TRIED:.......
View Replies!
View Related
Function Macro Debug: Continue Onto The Do Loop At The Bottom That Does All The Work
I am having a problem with a custom funciton I am trying to create. It will exit after it is finished with the IF Then Else statement. I need it to continue onto the Do loop at the bottom that does all the work. Function UPCECheck( num As String) As Long Dim CheckNum As Long Dim TempCheck As Long Dim X As Long Dim Holdtxt As Variant UPCECheck = 0 CheckNum = 0 Debug.Print Len(num) If Len(num) = 12 Then Holdtxt = num ElseIf Len(num) < 12 Then Holdtxt = "000000000000" & num Holdtxt = Val(Mid(Holdtxt, Len(holdtext) - 12, 12)) End If...................
View Replies!
View Related
Do Until And Crashes
My main workbook basically starts with, Sub macro1 (), Do Until, Web Query, many many many calculations in between and finally ActiveWorbook.Save. After ActiveWorbook.Save it will start the same process with a new web query and works all perfectly and perpetually just as I would like it to....
View Replies!
View Related
Listbox Crashes
I'm trying to use a listbox and wanted to have the data sit in a different sheet. When I do this, I get a message about low memory and then excel says it has an error and shuts down. When I move the data to the same sheet as the listbox, there is no problem. I'm using excel 2000 and that's all I can get (at work). Any thoughts? Should I just put the data on the same sheet and hide it? Is there a way around this?
View Replies!
View Related
Crashes Without Warning
On one laptop Excel crashes, without warning. It happens during copy/paste job (from one workbook to another). But not every time she copies something. It is completly the same laptop like others in my company. My user has deadlines, and lot of copy/paste job.
View Replies!
View Related
Open And It Crashes Down?
Whenver I open the Excel App it crashes down ? Why ? I Deactivated all the add ins, but the problem still exists. Deleted the registery of Excel and un- install the Excel App then Re-install it again, but the problem still exists......
View Replies!
View Related
Crashes Logs
I wrote some VBA code detailed here. Is there any Excel Crash log I can look at? I have tested it on three computers. It worked on my computer just fine, but when testing it on the two other users of this spreadsheet it crashes when they click the mouse on a list box after about 5 to 10 clicks.
View Replies!
View Related
UserForm Crashes
It is a simple form which has a Module/Macro to load the form on clicking a shape. Although the form works fine for me it occasionally crashes for other user. The Debug says that there is an error in the Module with the line "frmCallData.Show". Below is the Code for the form and also the Module: ....
View Replies!
View Related
BeforeSave Cause Crashes
I have the following code that (should) either save the file with a generic name if a value exists in a certain field, or else save the file as the contents of the value in the field, with a message box appearing in either case. The problem is, the message box appears twice (not once) and then Excel crashes! Can anyone tell me what's going on? The code is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FSR As String FSR = Range("I7").Value If FSR = "" Then MsgBox "Service Report Number not entered." ActiveWorkbook.SaveAs Filename:="Service Report Navilas.xls" End If If FSR "" Then MsgBox "This action will save the file as the Service Report Number." ActiveWorkbook.SaveAs Filename:=FSR & ".xls" End If End Sub
View Replies!
View Related
Print Previewed A Worksheet And Find A Tiny Image With A Small Portion Of The Print Area
I just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%. I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.
View Replies!
View Related
File Crashes When Opened
I run a macro which runs saves the active file to a different location to ensure that I retain the original like so - If ActiveWorkbook. Name = "KEY_DATA_CFT77" + ".xls" Then ActiveWorkbook.SaveAs Filename:= _ "H:HOME imcEXCELKEY_DATA_2006KEY_DATA_2006_01KEY_DATA_CFT77_01.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End If .................................
View Replies!
View Related
Crashes When Inserting A Comment
I open Excel. I click Insert-> Comment. Excel crashes. I try to open an excel file with comments, Excel crashes. I have already uninstalled and reinstalled, Detected & Repaired. Nothing appears in the event viewer. I am at my wits end. Any ideas on what this could be? My only guess is a windows issue vs. an excel issue, but I thought I'd at least ask.
View Replies!
View Related
How Many Records To Sort Without Crashes
I have a column of data that is an 850,000+ list of phone numbers. I need to peridoically insert another 10,000+ at a time and see if there are duplicates. Using conditional formatting to highlight the duplicates with a colored cell, and then sorting those records to the top is what I want to do, but it's too much for Excel to handle, and it hangs. It looks like I need to break up the 850k records into smaller chunks. Anyone know what an average pc with Excel 07 can handle? It's a 2.8ghz w/2gb ram.
View Replies!
View Related
SaveAs Error Crashes
I have a very strange error at present. I am saving a sheet as a file using VBA from within Excel and when I run the program from the environment of my flash drive it works without a problem but when I run the same code on my main PC it causes an error : Run time error 1004 Method SaveAs error ... and it causes Excel to crash and then recover the file. The code that causes this is shown below : ....
View Replies!
View Related
Userform Crashes Upon Submitting
I worked really hard on this userform for a work project - my first and likely last attempt at coding! It used to work, and then I changed a bunch of listboxes to comboboxes, and now my file gives the 'Excel has encountered a problem and needs to close' error - but ONLY when I'm working directly with the excel file and NOT when I've got VBA open. Compiling shows no problems - I have no idea what to do because this is a very important file for work and I am a very inexperienced coder! ...
View Replies!
View Related
Solver Macro Crashes
When I run a group of macros for some reason after the marcro runs and I hit F2 excel crashes. I have figured out that it is one of the marcros that makes the excel crash. It is only when I run the solver application. The code is below. Any thoughts why hitting F2 after running this marcro would crash the machine?
View Replies!
View Related
Crashes On 2nd Run
I have the following code which filters data in a sheet, and loads the numbers into textboxes on a userform. There are two multipage controls, each with several textboxes on them. I have commented out several lines because if I leave any of them in, it crashes excel. It will work once. But if I close the workbook, re-open it, and run the code again, it crashes. The thing I find really interesting is that all the 'problem lines' were added in as part of a retro-fit. The person I created this workbook for needed one more column of data. I followed the same programming pattern as what I used on the other, but only these ones seem to cause problems. Any thoughts? NOTE: The message boxes were just to help me figure out which lines of code were causing the crash. ....
View Replies!
View Related
Workbook Crashes On Open
Three times (by three different users) over the past two months, a workbook has gotten into what appears to be some sort of corrupted state. When you attempt to open the workbook you get the "report to Microsoft" screen which states "Microsoft Excel has encountered a problem and needs to close". Proceeding on this screen closes the entire instance of Excel, including the offending workbook and any others that are open. However, if I simply open and save the workbook in Excel safe mode, then it resolves the problem. But, again, it has happened three times so it keeps coming back. A little more background: there is a custom add-in referenced which contains many custom functions. This add-in is also referenced by other workbooks, none of which have had this problem. The Workbook_Open event only does what I consider to be superficial processing; it hides some sheets, selects a sheet, defaults some shape colors, and defaults a checkbox to True. In other words, nothing too heavy going on at startup, in my opinion. I have tried several times to break into the code between the time the workbook begins to load and when the "report to Microsoft" screen is displayed, but this was unsuccessful. I was hoping that the fact that simply saving it from safe mode resolves the problem might indicate the problem to someone on the forum. I have a copy of the file in its corrupted state but I have not attached it because it contains some business sensitive information.
View Replies!
View Related
Crashes With Application.quit
I'm trying to close out excel through a user form (excel is hidden) button but excel crashes when it reaches the application.quit line of code. I searched google and found one message that said that if you have add-ins that they can cause excel to crash when closing but there was no explanation or work around. Is there a way around this problem?
View Replies!
View Related
Vlookup Crashes Userforms
I'm having a problem with Vlookup locking up excel everytime it tries to call a userform. If I rem either the .show lines or the entire Vlookup loop it works. I suspected it was the exit handler but that doesn't seem to be the case. If I use vlookup code and data validation instead of vb vlookup it all works except for the problem of having the code in the cells overwritten. It's probably something really simple that I overlooked. Any suggestions would be welcome. Newvendin is a multi-input textbox and frmcalendar is a calendar....
View Replies!
View Related
Crashes When Saving Workbook...
As I've learned to develop Excel apps with vba over the years I've noticed that, if the Excel app starts to get too large (over 1MB) saving it will often cause the application to crash for no apparent reason - the result being that I lose all the vba code I entered before the last save - with the worst case being that, on occasion I have lost all of the vba code in an applications!!! So, I've learned to back up often using a third-party utility that I use to save my applications, which works wonders in allowing me to save my application without it crashing. The application is called Code Cleaner 97.exe. It was designed around Excel 97 (which I still use) but if possible I'd like to get an updated version for Excel 2003 and Excel 2007, which I also use. Does anyone know of a more recent version of this utility, or another similar utility that allows large Excel applications to be properly saved, etc? Code Cleaner 97.exe was a freebie that was available through one of the old Compuserve Excel forums. It was a very popular utility and was suggested as a means to save one's Excel apps without the app getting trashed, etc. I'm not expecting a freebie solution so I'll gladly pay for something that allows me to save my large Excel apps without them crashing, etc.
View Replies!
View Related
Allow Users To Print Hidden Sheet But Not Change Print Settings
I am having trouble disabling the "Preview" button when calling Application.Dialogs(xldialogprint) in XL2003. I have an xlSheetVeryHidden worksheet containing a form which is made visible programatically when the user wishes to print a copy (code below). I want the user to be able to have access to the functionality of the xlDialogPrint dialog (i.e. select desired print destination, number of copies, pages per sheet etc.) but I also want to prevent them modifying the page setup of the protected worksheet by initiating a preview from the print dialog. Private Sub btnPrintReturnForm_Click() wbkRUSC.Unprotect sysPass 'unprotect workbook to enable changes to sheet visibility ' (sysPass is project constant string containing password) shtStaffForm.Visible = xlSheetVisible 'make staff form visible shtCalc.Visible = xlSheetVeryHidden 'hide main calculator in case user gets smart and hits..............................
View Replies!
View Related
Vba Form Crashes Intermittent
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 Replies!
View Related
Using Sort Function In Macro Crashes
I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message: Run-time error ‘1004’: Sort method of Range class failed When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?
View Replies!
View Related
|
TRACKER
