Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
I have this code which is not working. As presented
below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent(). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray(X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
Dim A as Object "ExcelMonkey" schreef in bericht ... I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent(). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray(X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
Declare A as type Variant, not Object.
-- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent(). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray(X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
Have not tried it yet but have a question. Why would it
work Dim As Object in the first scenario and not in the second? How does changing it to Variant make the difference? Thank-you -----Original Message----- Declare A as type Variant, not Object. -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent(). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
All in the userform Module:
Option Explicit Public vArray As Variant Private Sub CommandButton1_Click() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray(X)) Next Next End With End Sub Produced: 0 Range Range .. . . 0 Range Range 1 Comment Comments 1 Comment Comments 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets I removed all the comments from the sheet and it worked as well. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent(). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray(X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
So Tom. You are right. This does work in a userform. I
built it from scratch in a new userform with a new button and it works fine. But for some reason I cannot get it to work in my currrent code. My current code is longer and more extensive than what I am showing here. When I put a break right before the first For Loop and go to the immediate window, I get a value for X and I can also type in ?TypeName(vArray(X)) and get values for all the array elements. It fails on recognizing A. And I have defined A as: Dim A As Object right in that very procedure. ?A gives an error. What can I do to help me understand why this is not working. I keep thinking its something obvious. I know the array has transferred successfully, I know my loop counter works. Thanks -----Original Message----- All in the userform Module: Option Explicit Public vArray As Variant Private Sub CommandButton1_Click() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray(X)) Next Next End With End Sub Produced: 0 Range Range .. . . 0 Range Range 1 Comment Comments 1 Comment Comments 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets I removed all the comments from the sheet and it worked as well. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent(). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
Ok. Just figured something out. My routine creates a
worksheet in my file that it summarizes data in. When the macro runs it checks to see if the sheet exists. If not it creats it. If it does exist, it deletes it and creates a new one. The macro fails when the sheet already exists. When I manually go in an delete the sheet and then run the macro, it works fine. So when the sheet exists, the routine does not understand what A is. Yet when the sheet is not present, it does? Here is the exact code I am using: Private Sub ListAuditResults() Dim cmt As Comment Dim cell As Range Dim Row As Range Dim Column As Range Dim PasteStartCell As String Dim Comrng As Range Dim Hardrng As Range Dim Errrng As Range Dim Colrnge As Range Dim Validrng As Range Dim ValidErrrng As Range Dim HiddenRowrng As Range Dim HiddenColumnrng As Range Dim HiddenSheetrng As Range Dim ColKeyrng As Range Dim CellColour As Boolean Dim sStr As String Dim sStr1 As String Dim sh As Worksheet Dim sh1 As Worksheet Dim sh2 As Worksheet Dim NumColrs As Integer Dim Counter As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim NumCells As Integer Dim CellCounter As Integer Dim SheetNamePosition As Integer Dim A As Object 'Object like cell, comment Dim AuditTypes As Integer Dim AuditShtName As String Application.Calculation = xlManual Application.ScreenUpdating = False On Error Resume Next 'Set up name of new summary sheet Set sh1 = ActiveWorkbook.Sheets("Audit Results") On Error GoTo 0 'If Sheet called "Audit Results" already exists 'then delete it and prepare to create a new one If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook 'Add a worksheet for results to be pasted to .Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = "Audit Results" For AuditTypes = 0 To 5 For Each A In ObjCollArray(AuditTypes) 'Call MainAudit(AuditTypes) Debug.Print AuditTypes, TypeName(A), TypeName (ObjCollArray(AuditTypes)) Next Next End With -----Original Message----- All in the userform Module: Option Explicit Public vArray As Variant Private Sub CommandButton1_Click() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray(X)) Next Next End With End Sub Produced: 0 Range Range .. . . 0 Range Range 1 Comment Comments 1 Comment Comments 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets I removed all the comments from the sheet and it worked as well. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent(). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
As stated in the last post, this is bizarre. When I type
in TypeName(A) in the immediate window it registers as Nothing up until the line of code: For Each A In ObjCollArray(AuditTypes) Then it regesters proprerly. However, this only works if I do not have a sheet in the file called "Audit Results". That is, if the sheet already exists, the routine deletes it, and inserts a new one with the same name. When the code gets down to the line of code: For Each A In ObjCollArray(AuditTypes) It fails because ?TypeName(A) is Nothing. Why is this happening? Private Sub ListAuditResults() Dim cmt As Comment Dim cell As Range Dim Row As Range Dim Column As Range Dim PasteStartCell As String Dim Comrng As Range Dim Hardrng As Range Dim Errrng As Range Dim Colrnge As Range Dim Validrng As Range Dim ValidErrrng As Range Dim HiddenRowrng As Range Dim HiddenColumnrng As Range Dim HiddenSheetrng As Range Dim ColKeyrng As Range Dim CellColour As Boolean Dim sStr As String Dim sStr1 As String Dim sh As Worksheet Dim sh1 As Worksheet Dim sh2 As Worksheet Dim NumColrs As Integer Dim Counter As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim NumCells As Integer Dim CellCounter As Integer Dim SheetNamePosition As Integer Dim A As Object 'Object like cell, comment Dim AuditTypes As Integer Dim AuditShtName As String Application.Calculation = xlManual Application.ScreenUpdating = False On Error Resume Next 'Set up name of new summary sheet Set sh1 = ActiveWorkbook.Sheets("Audit Results") On Error GoTo 0 'If Sheet called "Audit Results" already exists 'then delete it and prepare to create a new one If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook 'Add a worksheet for results to be pasted to .Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = "Audit Results" For AuditTypes = 0 To 5 For Each A In ObjCollArray(AuditTypes) 'Call MainAudit(AuditTypes) Debug.Print AuditTypes, TypeName(A), TypeName (ObjCollArray(AuditTypes)) Next Next End With -----Original Message----- Ok. Just figured something out. My routine creates a worksheet in my file that it summarizes data in. When the macro runs it checks to see if the sheet exists. If not it creats it. If it does exist, it deletes it and creates a new one. The macro fails when the sheet already exists. When I manually go in an delete the sheet and then run the macro, it works fine. So when the sheet exists, the routine does not understand what A is. Yet when the sheet is not present, it does? Here is the exact code I am using: Private Sub ListAuditResults() Dim cmt As Comment Dim cell As Range Dim Row As Range Dim Column As Range Dim PasteStartCell As String Dim Comrng As Range Dim Hardrng As Range Dim Errrng As Range Dim Colrnge As Range Dim Validrng As Range Dim ValidErrrng As Range Dim HiddenRowrng As Range Dim HiddenColumnrng As Range Dim HiddenSheetrng As Range Dim ColKeyrng As Range Dim CellColour As Boolean Dim sStr As String Dim sStr1 As String Dim sh As Worksheet Dim sh1 As Worksheet Dim sh2 As Worksheet Dim NumColrs As Integer Dim Counter As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim NumCells As Integer Dim CellCounter As Integer Dim SheetNamePosition As Integer Dim A As Object 'Object like cell, comment Dim AuditTypes As Integer Dim AuditShtName As String Application.Calculation = xlManual Application.ScreenUpdating = False On Error Resume Next 'Set up name of new summary sheet Set sh1 = ActiveWorkbook.Sheets("Audit Results") On Error GoTo 0 'If Sheet called "Audit Results" already exists 'then delete it and prepare to create a new one If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook 'Add a worksheet for results to be pasted to .Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = "Audit Results" For AuditTypes = 0 To 5 For Each A In ObjCollArray(AuditTypes) 'Call MainAudit(AuditTypes) Debug.Print AuditTypes, TypeName(A), TypeName (ObjCollArray(AuditTypes)) Next Next End With -----Original Message----- All in the userform Module: Option Explicit Public vArray As Variant Private Sub CommandButton1_Click() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Produced: 0 Range Range .. . . 0 Range Range 1 Comment Comments 1 Comment Comments 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets I removed all the comments from the sheet and it worked as well. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent (). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
If it fails on the For Each line, yes, A would be Nothing because the line
never executes and returns an object; the root problem is most likely that *ObjCollArray* is Nothing or it is empty. Where do you Dim a variable named ObjCollArray, and where do you set it to point to something? Is this a module-level variable that is set in another routine? On Sun, 27 Feb 2005 07:04:54 -0800, "ExcelMonkey" wrote: As stated in the last post, this is bizarre. When I type in TypeName(A) in the immediate window it registers as Nothing up until the line of code: For Each A In ObjCollArray(AuditTypes) Then it regesters proprerly. However, this only works if I do not have a sheet in the file called "Audit Results". That is, if the sheet already exists, the routine deletes it, and inserts a new one with the same name. When the code gets down to the line of code: For Each A In ObjCollArray(AuditTypes) It fails because ?TypeName(A) is Nothing. Why is this happening? Private Sub ListAuditResults() Dim cmt As Comment Dim cell As Range Dim Row As Range Dim Column As Range Dim PasteStartCell As String Dim Comrng As Range Dim Hardrng As Range Dim Errrng As Range Dim Colrnge As Range Dim Validrng As Range Dim ValidErrrng As Range Dim HiddenRowrng As Range Dim HiddenColumnrng As Range Dim HiddenSheetrng As Range Dim ColKeyrng As Range Dim CellColour As Boolean Dim sStr As String Dim sStr1 As String Dim sh As Worksheet Dim sh1 As Worksheet Dim sh2 As Worksheet Dim NumColrs As Integer Dim Counter As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim NumCells As Integer Dim CellCounter As Integer Dim SheetNamePosition As Integer Dim A As Object 'Object like cell, comment Dim AuditTypes As Integer Dim AuditShtName As String Application.Calculation = xlManual Application.ScreenUpdating = False On Error Resume Next 'Set up name of new summary sheet Set sh1 = ActiveWorkbook.Sheets("Audit Results") On Error GoTo 0 'If Sheet called "Audit Results" already exists 'then delete it and prepare to create a new one If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook 'Add a worksheet for results to be pasted to .Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = "Audit Results" For AuditTypes = 0 To 5 For Each A In ObjCollArray(AuditTypes) 'Call MainAudit(AuditTypes) Debug.Print AuditTypes, TypeName(A), TypeName (ObjCollArray(AuditTypes)) Next Next End With -----Original Message----- Ok. Just figured something out. My routine creates a worksheet in my file that it summarizes data in. When the macro runs it checks to see if the sheet exists. If not it creats it. If it does exist, it deletes it and creates a new one. The macro fails when the sheet already exists. When I manually go in an delete the sheet and then run the macro, it works fine. So when the sheet exists, the routine does not understand what A is. Yet when the sheet is not present, it does? Here is the exact code I am using: Private Sub ListAuditResults() Dim cmt As Comment Dim cell As Range Dim Row As Range Dim Column As Range Dim PasteStartCell As String Dim Comrng As Range Dim Hardrng As Range Dim Errrng As Range Dim Colrnge As Range Dim Validrng As Range Dim ValidErrrng As Range Dim HiddenRowrng As Range Dim HiddenColumnrng As Range Dim HiddenSheetrng As Range Dim ColKeyrng As Range Dim CellColour As Boolean Dim sStr As String Dim sStr1 As String Dim sh As Worksheet Dim sh1 As Worksheet Dim sh2 As Worksheet Dim NumColrs As Integer Dim Counter As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim NumCells As Integer Dim CellCounter As Integer Dim SheetNamePosition As Integer Dim A As Object 'Object like cell, comment Dim AuditTypes As Integer Dim AuditShtName As String Application.Calculation = xlManual Application.ScreenUpdating = False On Error Resume Next 'Set up name of new summary sheet Set sh1 = ActiveWorkbook.Sheets("Audit Results") On Error GoTo 0 'If Sheet called "Audit Results" already exists 'then delete it and prepare to create a new one If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook 'Add a worksheet for results to be pasted to .Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = "Audit Results" For AuditTypes = 0 To 5 For Each A In ObjCollArray(AuditTypes) 'Call MainAudit(AuditTypes) Debug.Print AuditTypes, TypeName(A), TypeName (ObjCollArray(AuditTypes)) Next Next End With -----Original Message----- All in the userform Module: Option Explicit Public vArray As Variant Private Sub CommandButton1_Click() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Produced: 0 Range Range .. . . 0 Range Range 1 Comment Comments 1 Comment Comments 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets I removed all the comments from the sheet and it worked as well. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message .. . I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent (). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working when transfered into Userform
Yes I delcare ObjCollArray as a PublicVariable. To avoid
confustion I have changed the name of the variable from "A" to "ObjCur" Then on a Button click Event, I set the elements in the array. Then I call the ListAuditResults routine. This checks to see if there is a sheet called "Audit Results". If there is, it deletes it and creates a new one called "Audit Results". If not, it simply creates on called "Audit Results". This routine will not work if the sheet "Audit Results" is already present. It failes to recognize "ObjCur". Yet if I manually delete before I run the routine, it works! Why is this Public ObjCollArray As Variant Private Sub OKButton_Click() Set ObjCollArray(0) = ActiveSheet.Comments Set ObjCollArray(1) = ActiveSheet.UsedRange Set ObjCollArray(2) = ActiveSheet.UsedRange Set ObjCollArray(3) = ActiveSheet.UsedRange Set ObjCollArray(4) = ActiveSheet.UsedRange Set ObjCollArray(5) = ActiveSheet.UsedRange call ListAuditResults End Sub Private Sub ListAuditResults() Dim PasteStartCell As String Dim sh As Worksheet Dim sh1 As Worksheet Dim AuditTypes As Integer Dim AuditShtName As String Application.Calculation = xlManual 'Application.ScreenUpdating = False On Error Resume Next 'Set up name of new summary sheet Set sh1 = ActiveWorkbook.Sheets("Audit Results") On Error GoTo 0 'If Sheet called "Audit Results" already exists 'then delete it and prepare to create a new one If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook 'Add a worksheet for results to be pasted to .Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = "Audit Results" 'Set up column headings for summary report 'these will set up based on the numbers 'options chosen. PasteStartCell = Range("B2").Address 'Set first paste cell and column header for Commented Cells If ComChkBx = True Then Set Comrng = .Worksheets("Audit Results").Range (PasteStartCell).Offset(0, ChkbxArray(0, 1) * 2 - 2) Comrng.Offset(-1, 0) = "Cell Comments" End If 'Set first paste cell and column header for Hard Coded Cells If HardCodedChkBx = True Then Set Hardrng = .Worksheets("Audit Results").Range (PasteStartCell).Offset(0, ChkbxArray(1, 1) * 2 - 2) Hardrng.Offset(-1, 0) = "Hard Coded Cells" End If 'Set first paste cell and column header for Cells with Errors If ErrorChkBx = True Then Set Errrng = .Worksheets("Audit Results").Range (PasteStartCell).Offset(0, ChkbxArray(2, 1) * 2 - 2) Errrng.Offset(-1, 0) = "Errors" End If 'Set first past cell for data validation cells If DataValChkBx = True Then Set Validrng = .Worksheets("Audit Results").Range (PasteStartCell).Offset(0, ChkbxArray(3, 1) * 2 - 2) Validrng.Offset(-1, 0) = "Validation" End If 'Set first past cell for data validation cells If DataValErrChkBx = True Then Set ValidErrrng = .Worksheets("Audit Results").Range(PasteStartCell).Offset(0, ChkbxArray(4, 1) * 2 - 2) ValidErrrng.Offset(-1, 0) = "Validation Errors" End If For Each sh In .Worksheets If LCase(sh.Name) < "Audit Results" Then 'After its been determined that the sheet is not the 'comments sheet, code checks various conditions 'For AuditTypes = 0 To 5 For Each CurObj In ObjCollArray(1) 'Debug.Print CurObj.Parent.Name, CurObj.Address Debug.Print sh.Name, ObjCollArray (AuditTypes).Parent.Name ObjType = TypeName(CurObj) CollType = TypeName(ObjCollArray(1)) Call MainAudit(2) Next 'Next End If Next End With -----Original Message----- If it fails on the For Each line, yes, A would be Nothing because the line never executes and returns an object; the root problem is most likely that *ObjCollArray* is Nothing or it is empty. Where do you Dim a variable named ObjCollArray, and where do you set it to point to something? Is this a module-level variable that is set in another routine? On Sun, 27 Feb 2005 07:04:54 -0800, "ExcelMonkey" wrote: As stated in the last post, this is bizarre. When I type in TypeName(A) in the immediate window it registers as Nothing up until the line of code: For Each A In ObjCollArray(AuditTypes) Then it regesters proprerly. However, this only works if I do not have a sheet in the file called "Audit Results". That is, if the sheet already exists, the routine deletes it, and inserts a new one with the same name. When the code gets down to the line of code: For Each A In ObjCollArray(AuditTypes) It fails because ?TypeName(A) is Nothing. Why is this happening? Private Sub ListAuditResults() Dim cmt As Comment Dim cell As Range Dim Row As Range Dim Column As Range Dim PasteStartCell As String Dim Comrng As Range Dim Hardrng As Range Dim Errrng As Range Dim Colrnge As Range Dim Validrng As Range Dim ValidErrrng As Range Dim HiddenRowrng As Range Dim HiddenColumnrng As Range Dim HiddenSheetrng As Range Dim ColKeyrng As Range Dim CellColour As Boolean Dim sStr As String Dim sStr1 As String Dim sh As Worksheet Dim sh1 As Worksheet Dim sh2 As Worksheet Dim NumColrs As Integer Dim Counter As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim NumCells As Integer Dim CellCounter As Integer Dim SheetNamePosition As Integer Dim A As Object 'Object like cell, comment Dim AuditTypes As Integer Dim AuditShtName As String Application.Calculation = xlManual Application.ScreenUpdating = False On Error Resume Next 'Set up name of new summary sheet Set sh1 = ActiveWorkbook.Sheets("Audit Results") On Error GoTo 0 'If Sheet called "Audit Results" already exists 'then delete it and prepare to create a new one If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook 'Add a worksheet for results to be pasted to .Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = "Audit Results" For AuditTypes = 0 To 5 For Each A In ObjCollArray(AuditTypes) 'Call MainAudit(AuditTypes) Debug.Print AuditTypes, TypeName(A), TypeName (ObjCollArray(AuditTypes)) Next Next End With -----Original Message----- Ok. Just figured something out. My routine creates a worksheet in my file that it summarizes data in. When the macro runs it checks to see if the sheet exists. If not it creats it. If it does exist, it deletes it and creates a new one. The macro fails when the sheet already exists. When I manually go in an delete the sheet and then run the macro, it works fine. So when the sheet exists, the routine does not understand what A is. Yet when the sheet is not present, it does? Here is the exact code I am using: Private Sub ListAuditResults() Dim cmt As Comment Dim cell As Range Dim Row As Range Dim Column As Range Dim PasteStartCell As String Dim Comrng As Range Dim Hardrng As Range Dim Errrng As Range Dim Colrnge As Range Dim Validrng As Range Dim ValidErrrng As Range Dim HiddenRowrng As Range Dim HiddenColumnrng As Range Dim HiddenSheetrng As Range Dim ColKeyrng As Range Dim CellColour As Boolean Dim sStr As String Dim sStr1 As String Dim sh As Worksheet Dim sh1 As Worksheet Dim sh2 As Worksheet Dim NumColrs As Integer Dim Counter As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim NumCells As Integer Dim CellCounter As Integer Dim SheetNamePosition As Integer Dim A As Object 'Object like cell, comment Dim AuditTypes As Integer Dim AuditShtName As String Application.Calculation = xlManual Application.ScreenUpdating = False On Error Resume Next 'Set up name of new summary sheet Set sh1 = ActiveWorkbook.Sheets("Audit Results") On Error GoTo 0 'If Sheet called "Audit Results" already exists 'then delete it and prepare to create a new one If Not sh1 Is Nothing Then Application.DisplayAlerts = False sh1.Delete Application.DisplayAlerts = True End If With ActiveWorkbook 'Add a worksheet for results to be pasted to .Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = "Audit Results" For AuditTypes = 0 To 5 For Each A In ObjCollArray(AuditTypes) 'Call MainAudit(AuditTypes) Debug.Print AuditTypes, TypeName(A), TypeName (ObjCollArray(AuditTypes)) Next Next End With -----Original Message----- All in the userform Module: Option Explicit Public vArray As Variant Private Sub CommandButton1_Click() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Produced: 0 Range Range .. . . 0 Range Range 1 Comment Comments 1 Comment Comments 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets I removed all the comments from the sheet and it worked as well. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message . .. I have this code which is not working. As presented below it works fine. Sub One Sets a public array. It then Calls Sub Two and I print values to the Immediate Window. Now I cannot seem to get this to work when I put this code into a useform. I put the Option Explicit and Public Array Declaration at the top of the page. Then I put Sub One into a Private Sub OKButton_ClickEvent (). I then put Sub Two into a Private Sub as well on the same form. When I run it I get an Error 424 Object Required within what was Sub Two. On the line: For Each A In vArray(X) The code can't seem to make sense of the variable A. Yet it presents no problems when modelled as seen below. Why does it not work? Option Explicit Public vArray As Variant Sub One() ReDim vArray(0 To 2) Set vArray(0) = ActiveSheet.UsedRange Set vArray(1) = ActiveSheet.Comments Set vArray(2) = ThisWorkbook.Worksheets Call Two End Sub Sub Two() Dim A As Object Dim X As Integer With ActiveWorkbook For X = 0 To 2 'Unload public array and pass to A and B as Types 'How do you pass these to A and B?????? For Each A In vArray(X) ' Call Procedure2 Debug.Print X, TypeName(A), TypeName(vArray (X)) Next Next End With End Sub Immediate Window Values: 0 Range Range 2 Worksheet Sheets 2 Worksheet Sheets 2 Worksheet Sheets . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto saving transfered data | Excel Discussion (Misc queries) | |||
Saving transfered data | Excel Worksheet Functions | |||
Keeping a transfered value the same after changing the original va | Excel Worksheet Functions | |||
Unloading UserForm not working | Excel Programming | |||
Userform not working | Excel Programming |