Discrepancies Between Running Macro And Stepping Through The Code?
May 22, 2013
I have just written a macro to validate/clean up some data which I get on a monthly basis. Some of the columns come from a pdf file and thus excel recognises these as strings. I have written some code that would convert this string to an integer which runs fine when I step through the code. However when I run the macro from a button the number value goes from e.g. 5000 to 0.
The code is below:
VB:
Sub Condition2()
For k = 8 To LastRow
Worksheets("Bordx Format").Cells(k, i - 1).Value = CDec(Cells(k, i - 1).Value)
Next k
End Sub
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:.......
I do those things, I set every object = nothing... I even moved them up to right after I use the object. When I open the workbook, my process's shows excel at 38000K. my first go of running code, stepping thru is great... I watch my process and see that I get up to 78000K... I then exit my code, properly... and the process goes only down to about 50000K. If I run it a 2nd time excel process show almost a 100000k and stepping thru is slow, slow motion... a 3rd time and I am at 120000k. I am setting everything to nothing and can't figure it out... I have done bigger projects with more forms, recordsets, collections and never have had this problem.
Whilst stepping through code in any new sub/ function that I create in VBA, VBA Editor (seemingly arbitrarily) jumps to a line in a completely different function.
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.
I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.
Here is the code:
VB: Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet 'Copy the data to the 'Previous Part Data' worksheet then clear the data Range("A4:V" & CurrentPartDataFinalRow).Copy
[Code]....
I have two questions: 1. Why did this work in 2007 and not in 2013?
2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?
I have a macro, that basically runs a sub routine to count down say for example 1 min, at which time the workbook closes. However if the user opens one of the forms I would like to turn off the background timer macro. How can I do this with code? Is there code that does the opposite of 'application.run'?
I am writing a grading program, which during the course of functioning must run other code that has errors. I have used On Error Resume Next code to allow the program to keep functioning when it has run into errors so far. However, when a subroutine is called that calls another subroutine with incorrect parameters, I get a VBA error message pop up. Inserting On Error Resume Next directly into the other code doesn't stop this problem. I either need a way to have it skip over any call to a subroutine with improper parameters, or to check if a project can compile before grading it.
Now..when I run any of my macros, I get the following message.. "Code Execution has been interrupted".
I'm not sure why I'm getting this message but it happens everytime I run ANY macro. Note that if I hit "Continue" every time it gives me the option, I am able to successfully run the macro, but obviously, I shouldn't have to do this.
I am attempting to run a MS Ecel macro that is stored on a MS Excel shared file in a MS Ecel target file (locally stored on my C drive) from a VBA module in Ms Access. That is, from a code i want to open the .xls file that houses the macro, and then open the .xls file that i want to run the file in, and then run the macro.
Here is my situation: the file that houses the macro has the workbook hidden and causes in error.
if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set.
if i hide the workbook the vba coding can't find the macro...run time error 1004
once again, i currently have an excel file that houses macro whose workbook is hidden.
I want to be able to run a certain chunk of code in the macro if a certain condition is true, or if that condition is not true then I want to bypass that chunk of code. It doesn't matter if this is accomplished with an if... then... else statement, or some other way.
I've got a worksheet_selectionchange macro on a sheet, and another macro that you can run after it. The issue is that when the second macro runs, it also runs the selectionchange macro, and wipes some of the info that the second macro should be copying.
Is there a piece of code that I can use in the second macro to block the selectionchance code from running until it's compelte?
Is it possible to display both the current code line number and the total line count of a macro while it's running?
I was thinking it would be handy for a progress indicator if the ProgressBar max value could be set to the LineCount total of the procedure and each line of code would increment the progressbar as the macro ran.
I have an excel file with 2 different columns filled with letter combinations which I need to update every now and then. I would like a code that will automatically fill in the codes not available in each list at the next columns.
See attached Sample Data to look how it will look like : Sample Data.xlsx
I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?
I have some code which steps through a range of columns from A to Z ( using Chr(asc(col))+1 as the stepper.
This works fine until it encounters col AA then fails - I have added the function that does this - can you help me get beyond col Z?
Function findcol(strTitle As String, ws As Worksheet) If Left(strTitle, 1) = "0" Then strTitle = Right(strTitle, Len(strTitle) - 1) End If col = "A" findcol = "" While ws.Range(col & "1").Value <> "" And findcol = "" If ws.Range(col & "1").Value = strTitle Then findcol = col End If
I have excel 2007. I have a workbook with 2 tabs set up as follows:
Tab ATab B
Tab A contains a column with the source data, which will be constantly overwritten
Tab B has a row which references the data in tab A
I would like to copy the reference formula in Tab B down for say 100 rows, but when I use the fill tool, it automatically moves the reference cell up one. I want it to remain the same as the original.
E.g Tab B, Row 1 ColA = Tab A, Row 1 ColB = Tab A, Row 2 ColC = Tab A, Row 3
When I fill copy the above; on Tab B, Row 2, the formula changes to ColA = Tab A, Row 2, ColB = Tab A, Row 3 etc. I would like them to remain as ColA = Row 1, ColB = Row 2 etc.
I will then use Tab A to input each rows data in Tab B, having converted the previous row into just text so it doesn't change with each update.
This is bizarre - over the last year or so, on two machines (one Win98SE/Off2K, the other Win2KSP4/Off2K3), very occasionally a change I've made to Excel/VBA code has been ignored (eg to add a MSGBOX call) - even after I've restarted Excel. The workaround is to add a blank line and remove it again in the actual code module, but its both annoying and intriguing,
we would use a byte data type as a counter in a For Loop, but just today I have been having trouble with this. When I try to count backwards with " Step -1" I get an "error 6: overflow" on the FOR LOOP line. When I count forwards it works fine.
I have written some code intended to automatically clear a cell when data appears in it from an external source. In one spreadsheet, it seems to work exactly as expected, ie when the cell is polulated, it immediately clears the contents. In another spreadsheet, with exactly the same code, it will not automatically clear. If I put it in debug mode and step through it line by line, then it does do as expected but otherwise it won't.
I have used a code to find the character "-" and replace it with nothing ("") from several named worksheets across my workbook, which OzGrid helped me with before, and it worked fine. Now I've added the code into my module (at the bottom) it doesn't work. The rest of the code works fine, and it doesn't come up with any errors when I run it, but it just seems to ignore the 'find and replace' part of it.
Sub split1()
Dim fromSheet As Object Set fromSheet = Worksheets(1) Dim fromRow As Integer Dim toRow As Integer Dim lastRow As Integer Dim errCount As Integer Dim thisSectionName As String Dim i As Integer ' loop counter Dim rng As Range 'found range Dim emptyCellAbove As Range Dim rng1 As Range Dim arng1 As String 'Address of 1st hit rng1 Dim ws(7) As String Dim ii As Integer '2ND LOOP COUNTER Dim iii As Integer '3rd Loop Counter
I'm trying to create a spreadsheet that uses one cell to enter a dollar amount and stores the amount entered in another cell. But I would like the input cell be zero after each entry, and the cell storing the amounts to keep a running total.
Attached is an example of the program. The purpose of this program is to allow someone with very little knowledge about the information to enter data. The first tab labeled "Instructions" is will have two macros assigned to the buttons at the bottom of the page. The first macro labeled "Move Data" will move data will clear data in the shaded cell on the "Entry Form" sheet and fill those cells in with '----. The second macro labeled "Generate and Save" will save a master copy of the program, save a historical copy of the program by date, and print/save a pdf copy of the "Reports" sheet.
Each one of the historical (the last 8 sheets of the program) sheet receives data from the "Entry Form" and then moves the data by date into the table for archiving purposes.
If you look at the code for the "Entry Form". I have a couple of subs. The first being Worksheet_change. the first section of this code is to force certain cells to be uppercase. This part of the code is working fine. The second part of the code is the Dim MyValues. This part is working fine also, but...
My issue is... When I run the move data macro from the "Instructions" sheet the Dim MyValues reads these as changes to the cells and wants to push the data to the historical sheets.
What I would like is an IF Statement above the Dim MyValues part of this code that stops the Dim MyValue part of the code running if those values are blank, 0, or have '---- entered into them.
I have the following code, I have it running to just work on Sheet 2 but I would like it to be able to run on mulitple sheets, not all but a range like from Sheet2:Sheet30.
I have placed the code I am using below. It takes values from a hidden sheet (PACTAct) and places them on a visible sheet (Email). The code moves between the two.
It works at the minute by unhiding the PACTAct sheet then at the end re-hiding it. However I don't want the user to see this sheet whilst the vba is running. Is there a way I can simply refer to the worksheet without unhiding it?
Let's say I have 6 Worksheets in my Workbook with the following names:
Sheeta1 Sheeta2 Sheeta3 Sheetb1 Sheetb2 Sheetb3
And I have three Subs that I want to run, one Sub for two specific Worksheets.
How could I code my Subs so that the first Sub runs on Sheeta1 and Sheetb1, the second Sub runs on Sheeta2 and Sheetb2, and the last Sub runs on Sheeta3 and Sheetb3?
As you can see, the 1, 2 and 3 in the Worksheet names are what links the Subs to the Worksheet.
Sub OutOfStockDate() templateSheet.Range("B15").value = dataSheet.Range("G5").value If dataSheet.Range("G5").value = "" Then templateSheet.Range("B15").value = "There is no Out of Stock Date" End If End Sub
However when I run this code I get the infamous 1004 error.
I know how to enable/disable events using VBA code, however is there an option within excel to turn it on/off? My problem is this...
At the beginning of my code I disable events and at the end I enable it again (I need to do this to avoid being caught in a loop). However something is going wrong somewhere in my code and the code stops halfway through. I'm trying to test sections of the code, but I often inadvertently stop the code without enabling the events again. Therefore I can't get my VBA to execute again unless I close excel down and restart. This is a pain as I have to find my place in the code again!
