current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 1 down vote favorite

I have completed my VBA module which would calculate monthly record differences based off medical services provided. This was working well. However when I tried running the code for a 3rd month (i.e. March) and using the February data as static data - I was alerted to the fact that my code has started a buffer overflow.

I went over my code but I could not identify why this is the case - the only consistent factor is that when I go to the 3rd month (have not tested any further), 1 out of 4 times I will get an antivirus alert closing down excel indicating an overflow. Could anyone please help me identify why this is the case?

Sub monthlyCalculation()
Dim ws As Worksheet 'Worksheet Variable required for IF statement

Sheets("StaticRecord").Copy After:=Sheets("StaticRecord")
Sheets("StaticRecord (2)").Visible = True
'Rename Summary (3) to Monthly Comparison
Sheets("StaticRecord (2)").Name = "MonthlyComparison"
'Remember to do the subtraction calculations here
Sheets("MonthlyComparison").Select
'Don't use ActiveCell but rather a direct reference to subtract
Range("I6").Value = "=ABS(Summary!I6-'StaticRecord'!I6)"
Range("I6").Select
Selection.AutoFill Destination:=Range("I6:I28"), Type:=xlFillDefault

'Key Metrics Calculation for the created MonthlyComparison Tab
 Range("D6").Value = "= ABS(VALUE(LEFT(Summary!D6,2))-VALUE(LEFT('StaticRecord'!D6,2)))"
 Range("D7").Value = "=ABS((Summary!D7)-('StaticRecord'!D7))"
 Range("D8").Value = "=ABS((Summary!D8)-('StaticRecord'!D8))"
 Range("D9").Value = "= SUM('Template:Template - Book End'!H55)-2"
 Range("D10").Value = "= $D7/$D8"
 Range("D11").Value = "= 1 - D$10"
 Range("D12").Value = "= Summary!D12"
 Range("D13").Value = "= Summary!D13"
 Range("D14").Value = "= Summary!D14"
 Range("D15").Value = "= Summary!D15"

 '# Sessions Calculations
 Range("J6").Value = "=ABS('StaticRecord'!J6-Summary!J6)"
 Range("J6").Select
 Selection.AutoFill Destination:=Range("J6:J27"), Type:=xlFillDefault
 Range("J6:J27").Select

'Now that we have done the calculation we need to get rid of the initial Summary by replacing it with a blank template copy
'However we know that the summary tab CANNOT be cleared unless the user tabs are cleared so we must clear these tabs instead
'We will do this by looping through all user tabs and clearing the set fields'

For Each ws In Worksheets
 If Len(ws.Name) <= 5 Then
    ws.Range("B7:C100").ClearContents

 End If

 Next

'Lastly we need to ensure that if a new comparison is to be completed, it will compare this against the static record which is last
'months statistics. This means that MonthlyComparison will need to be copied across and renamed as a static record with static values.
Application.DisplayAlerts = False
   'StaticRecord has now been deleted so we need to create a new StaticRecord
    Sheets("MonthlyComparison").Copy After:=Sheets("MonthlyComparison")
    Sheets("MonthlyComparison (2)").Visible = True
    Sheets("MonthlyComparison (2)").Name = "StaticRecord (2)"
'Once the monthlyComparison is deleted, the copy of staticRecord (2) will show all REF values
'This will need to be corrected by making the values static
Sheets("MonthlyComparison").Select
 Range("I6:J28").Select
 Selection.Copy
 Sheets("StaticRecord (2)").Select
 Range("I6:J28").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 Sheets("MonthlyComparison").Select
 Range("D6:D15").Select
 Selection.Copy
 Sheets("StaticRecord (2)").Select
 Range("D6:D15").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


For Each ws In Worksheets
  If ws.Name = "StaticRecord" Then
     ws.delete
  End If

Next

'Rename the newly created StaticRecord (2) into StaticRecord
Sheets("StaticRecord (2)").Name = "StaticRecord"
'Now that we have copied the data from MonthlyComparison we can eliminate this tab as it is no longer required
For Each ws In Worksheets
  If ws.Name = "MonthlyComparison" Then
     ws.delete
  End If

Next

End Sub
share|improve this question
    
This is tricky without an example notebook. Any chance you can submit one? Have you checked, when the overflow occurs? –  Klaster Aug 18 at 10:02
    
Your use of comments are a little unorthodox here; it looks more like you've left in notes in unfinished code, instead of actually indicating what your code currently does. If your module is theoretically complete, your comments should indicate the purposes of individual sections. Also - what is 'Template:Template - Book End'? Is that a single worksheet? –  Grade 'Eh' Bacon Aug 18 at 14:40
    
Final note - you haven't included the section of code which loops, though your comment at the bottom indicates that a loop exists. I can't see anything in the tab above that would be particularly intensive, so what does the rest of your code do? Are you sure the overflow comes from this section? –  Grade 'Eh' Bacon Aug 18 at 14:44
    
This will generate an overflow: Dim i As Byte: i = 258 (when a variable cannot be assign a value larger than its type). Importing new data usually indicates your range formats are not expecting certain values, for example floating numbers: "Although Excel can display 30 decimal points, its precision for a specified number is confined to 15 significant figures, and calculations may have an accuracy that is even less due to ... binary storage". Does your data contain floats with a large number of decimal places (mantissa)? Check cell formatting for all your columns ("Format Cells...") –  paul bica Aug 18 at 15:08
    
@Grade'Eh'Bacon: These comments were mainly for me so I could revisit certain bits of the code and verify what I was trying to do at that point in time. For that module - that's all the code there is. I have another module which I use for exporting to a PDF and doing a copy / paste - but these are not intensive at all. Paul: I don't believe I have any variables which would result in an overflow that you have described. I'll give it another crack this morning and if I can't solve it - I'll upload my workbook. From the code I have provided do you see any reason it would overflow? –  azurekirby Aug 19 at 0:42

1 Answer 1

up vote 0 down vote accepted

I tinkered around and I think I have discovered what is causing my buffer over-flow issue. With how I've coded the function, there is a lot of name-swapping of sheets as newly created sheets take on the name of the older deleted sheets. One of the work-sheets in particular (MonthlyComparisons) has calculations that rely on data from another work-sheet - StaticRecord. Once StaticRecord is deleted and subsequently re-named, I might have introduced a pointer issue where I'm pointing to memory which has been cleared which confuses excel and causes it to shut-down. Additionally I changed the order of which tabs are deleted. 

 For Each ws In Worksheets
  If ws.Name = "MonthlyComparison" Then  
     ws.delete
  End If

Next

For Each ws In Worksheets
  If ws.Name = "StaticRecord" Then
     ws.delete
  End If

Next

Initially I had the StaticRecord tab deleted first THEN the monthly comparison. MonthlyRecord relies on StaticRecord for data though. So once I deleted MonthlyRecord first and THEN StaticRecord, the issue seemed (at least for now) to resolve itself.

This is the rest of the code in case any of you can spot any other issues with what I've written up :)

Sub monthlyCalculation()
Dim ws As Worksheet

Sheets("StaticRecord").Copy After:=Sheets("StaticRecord")
Sheets("StaticRecord (2)").Visible = True
Sheets("StaticRecord (2)").Name = "MonthlyComparison"
Sheets("MonthlyComparison").Select
Range("I6").Value = "=ABS('StaticRecord'!I6-Summary!I6)"
Range("I6").Select
Selection.AutoFill Destination:=Range("I6:I28"), Type:=xlFillDefault

'Key Metrics Calculation
 Range("D6").Value = "= ABS(VALUE(LEFT('StaticRecord'!D6,2))-VALUE(LEFT(Summary!D6,2)))"
 Range("D7").Value = "=ABS(('StaticRecord'!D7)-(Summary!D7))"
 Range("D8").Value = "=ABS(('StaticRecord'!D8)-(Summary!D8))"
 Range("D9").Value = "= SUM('Template:Template - Book End'!H55)-2"
 Range("D10").Value = "= $D7/$D8"
 Range("D11").Value = "= 1 - D$10"
 Range("D12").Value = "= Summary!D12"
 Range("D13").Value = "= Summary!D13"
 Range("D14").Value = "= Summary!D14"
 Range("D15").Value = "= Summary!D15"

 '# Sessions Calculations
 Range("J6").Value = "=ABS('StaticRecord'!J6-Summary!J6)"
 Range("J6").Select
 Selection.AutoFill Destination:=Range("J6:J27"), Type:=xlFillDefault
 Range("J6:J27").Select


'For future calculations, comparisons between static record and the monthlyComparison tab will be made. This means that
'MonthlyComparison will need to be copied across and renamed as a static record with static values.
Application.DisplayAlerts = False
Sheets("MonthlyComparison").Copy After:=Sheets("MonthlyComparison")
Sheets("MonthlyComparison (2)").Visible = True
Sheets("MonthlyComparison (2)").Name = "StaticRecord (2)"
'Once the monthlyComparison is deleted, the copy of staticRecord (2) will show all REF values. It relies on another
'This will need to be corrected by making the values static so values from MonthlyComparison are copied to Static Record (2)
Sheets("MonthlyComparison").Select
Range("I6:J28").Select
Selection.Copy
Sheets("StaticRecord (2)").Select
Range("I6:J28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("MonthlyComparison").Select
Range("D6:D15").Select
Selection.Copy
Sheets("StaticRecord (2)").Select
Range("D6:D15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Now we delete the existence of MonthlyComparison as it relies on      StaticRecord for calculations
  For Each ws In Worksheets
  If ws.Name = "MonthlyComparison" Then  ''Or ws.Name = "StaticRecord"'
     ws.delete
  End If

Next

For Each ws In Worksheets
  If ws.Name = "StaticRecord" Then
     ws.delete
  End If

Next

End Sub
share|improve this answer
    
HI Everyone, I actually discovered another reason why my code was acting up. This was because I have a 'copy' function in my code which copies another spread-sheet (inclusive of all the formulas). Additionally one of the macros I had used a FOR loop which went through all my worksheets which resulted in a lot of iterations. In Debug-Mode as I kept running this, after 40 or so iterations - the sheet would crash. After removing the need for the for-loop, there is no longer any crashing. :) –  azurekirby Aug 24 at 0:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.