Thread: Excel VBA Guru needed....

I would rate myself as an average VBA user, but I play with it in Access, not Excel.

Happy to waste some of my time looking at it, if you want to email.

If anybody else has more l33t s|<1llz, jump right in.

forsythem1972gmail.com

I do some excel based VBA work commercially.

If you wish I'll have a look - but no promises. First the error has to reproducible and it depends on other things like how well the code is documented, how much time needs to be put into etc.

PM me with an email address if you wish to make contact.

Have you upgraded recently to a different version of Office. I have found office 2010 to break previously working excel vba code I maintain.

"Reproducable"? Wouldn't that be nice!

We all run Win 7, 64 bit laptops, Office 2010.

PM sent.

Originally Posted by Ferret

I do some excel based VBA work commercially.

If you wish I'll have a look - but no promises. First the error has to reproducible and it depends on other things like how well the code is documented, how much time needs to be put into etc.

PM me with an email address if you wish to make contact.

Have you upgraded recently to a different version of Office. I have found office 2012 to break previously working excel vba code I maintain.

Are you using Office 2010 32-bit or 64-bit version? 64-bit can do all sorts of funny things.

Also the amount of RAM in the PC's can impact on Excel iterations as Excel still leaks memory as much as it use ever has.

As for the VBA a few things to check...
Does the spreadsheet have any potential particularly via the code to create circular references?
What reference libraries is the VBA using? Is there newer ones being used or that can replace older ones? This could also relate to the link you posted.

I would definitely be declaring everything, always use Option Explicit and clear objects by setting them to nothing when no longer used.

I've encountered this error in every version of office since vBA came along I'd hazard a guess. Often you can't use debug successfully on this type of error but you can use Debug.Print statements to narrow down where in the code it is occurring.

Thanks Derek.

The OS is definitely W7 64 bit but I'm not sure about Excel 2010. His laptop will be a basic Dell with maybe 4 GB RAM where as mine is a hi spec Asus with 12 GB RAM.

Re the code; no circular references and I'll have to have a look at the libraries. There's been nothing installed or added to these laptops beyond the standard install.

One thing I'm not doing is clearing the objects after use.

Cheers,

Mark

If you need someone to pass an eye over any code snippets let know. I have done too many millions of lines of code including a lot in VBA in MSOffice. I don't run any MSOffice products at home though so can't debug anything for you.

Gents,

Thanks for your help above. I've modified the code to qualify objects, declaring variables and setting them to 0 after use. As a result the crashes have gone from frequent to sometimes.

Below is the module and form code for copying rows. This is the most used code in the spreadsheet and results in the most errors. Always the same "disconnect from client" error.

I'd really appreciate any comments on what I've missed in my clean up.

Cheers,

Mark

Modules:

Option Explicit
Public Number_of_Rows, Copy_Start_Row, Copy_End_Row, Copy_Insert_Row, i As Integer

Sub Row_Add()
'Adds rows in multiples of 1, 5, 10 or 20 depending on what the user selects. Rows can be added anywhere within a section.
'Dim Number_of_Rows As Integer - Is declared as a Public variable at the top of this module as "Number_of_rows" is passed between this sub and the Add_Rows form code.

Number_of_Rows = 0 'Value assigned in the fmAdd_Rows form.
'Check that the current active cell is a valid selection
If ActiveCell.Row <= Range("PT_Start").Row + 1 Or ActiveCell.Row >= Range("PT_Total").Row - 2 Or Left(Cells(ActiveCell.Row, 1).Value, 8) = "Subtotal" Or Cells(ActiveCell.Row, 2).Value = "DO" Then
MsgBox "You must select a cell within a section"
GoTo Skip_Add
End If
'The current selection is valid so.......
fmAdd_Rows.Show 'Load the Add_Rows form to get the number of rows to be added.

ThisWorkbook.Worksheets("Points Takeoff").Unprotect "sysio"
'Copy and paste the template row
If Number_of_Rows = 0 Then 'The user has selected Cancel instead of 1, 5, 10 or 20 rows.
GoTo Skip_Add
ElseIf Number_of_Rows = 1 Then
ThisWorkbook.Worksheets("Points Takeoff").Range(Cells(Range("PT_Add_Row_1").Row + 1, 1), Cells(Range("PT_Add_Row_1").Row + 1, 1)).EntireRow.Copy
Selection.Insert Shift:=xlDown
ElseIf Number_of_Rows = 5 Then
ThisWorkbook.Worksheets("Points Takeoff").Range(Cells(Range("PT_Add_Row_5").Row + 1, 1), Cells(Range("PT_Add_Row_5").Row + 5, 1)).EntireRow.Copy
Selection.Insert Shift:=xlDown
ElseIf Number_of_Rows = 10 Then
ThisWorkbook.Worksheets("Points Takeoff").Range(Cells(Range("PT_Add_Row_10").Row + 1, 1), Cells(Range("PT_Add_Row_10").Row + 10, 1)).EntireRow.Copy
Selection.Insert Shift:=xlDown
ElseIf Number_of_Rows = 20 Then
ThisWorkbook.Worksheets("Points Takeoff").Range(Cells(Range("PT_Add_Row_20").Row + 1, 1), Cells(Range("PT_Add_Row_20").Row + 20, 1)).EntireRow.Copy
Selection.Insert Shift:=xlDown
End If

ThisWorkbook.Worksheets("Points Takeoff").Protect "sysio"
Skip_Add:
Number_of_Rows = 0
End Sub


Forms:

Private Sub UserForm_Initialize()
'This sub, called from the Points_Takeoff module, Copy_Rows sub, is called Userform_Initialize as the code resides in this form, therefore do not change the sub name.
'Set defualt values of the textboxes to zero as their default blank value is text, which is invalid when read into the 3 integer variables below.
tbStart_Row.Value = 0
tbEnd_Row.Value = 0
tbInsert_Row.Value = 0

tbStart_Row = Selection.Row
tbEnd_Row = Selection.Rows.Count + Selection.Row - 1

End Sub

Private Sub cbSelect_Range_Click()
tbStart_Row = Selection.Row
tbEnd_Row = Selection.Rows.Count + Selection.Row - 1
End Sub

Private Sub cbInsert_Row_Click()
tbInsert_Row = Selection.Row
End Sub

Private Sub cbOK_Click()
'Checks the validity of the selections, then copies and pastes the selections.
Copy_Start_Row = tbStart_Row.Value
Copy_End_Row = tbEnd_Row.Value
Copy_Insert_Row = tbInsert_Row.Value

'Check that the current copy and paste selections are valid.
If Copy_Start_Row < 11 Or Copy_End_Row < 11 Or Copy_Insert_Row < 11 Then 'Checks that all 3 rows selected not blank and are at least within the first section.
MsgBox "You must entered a row number in the First, Last and Insert boxes"
GoTo Skip_Copy
End If
'Loop through the copy range checking selection is valid.
For i = Copy_Start_Row To Copy_End_Row
If i <= Range("PT_Start").Row + 1 Or i >= Range("PT_Total").Row - 2 Or Left(Cells(i, 1).Value, 8) = "Subtotal" Or Cells(i, 2).Value = "DO" Then
MsgBox "You must select copy cells within a section"
GoTo Skip_Copy
End If
Next i
'Check the insert cell selection is valid.
If Copy_Insert_Row <= Range("PT_Start").Row + 1 Or Copy_Insert_Row >= Range("PT_Total").Row - 2 Or Left(Cells(Copy_Insert_Row, 1).Value, 8) = "Subtotal" Or Cells(Copy_Insert_Row, 2).Value = "DO" Then
MsgBox "You must select an insert cell within a section"
GoTo Skip_Copy
End If

'The current selection is valid so call the Copy sub below.

Copy_Rows

Skip_Copy:

End Sub

Private Sub cbCancel_Click()
'This is the code for the Cancel button, which closes the form without saving any changes.
Unload fmCopy_Rows 'Use of Unload is to remove the form from memory so that each time the user runs the form, the list is fresh.
End Sub

Private Sub cbHelp_Click()
'fmCopy_Rows.Hide
Unload fmCopy_Rows
fmHelp.Show
End Sub


Sub Copy_Rows()
'Called from cbOK_Click sub above.
'Copies and inserts rows.
ThisWorkbook.Worksheets("Points Takeoff").Unprotect "sysio"
ThisWorkbook.Worksheets("Points Takeoff").Range(Cells(Copy_Start_Row, 1), Cells(Copy_End_Row, 1)).EntireRow.Copy
ThisWorkbook.Worksheets("Points Takeoff").Cells(Copy_Insert_Row, 1).EntireRow.Select
Selection.Insert Shift:=xlDown
ThisWorkbook.Worksheets("Points Takeoff").Protect "sysio"

Copy_Start_Row = 0
Copy_End_Row = 0
Copy_Insert_Row = 0
End Sub

What line (or lines) of code do you get errors on?

PS: I would suggest setting variables to nothing rather than 0 after you are finished with them.