Sub that has an input of an array of ranges... what's wrong?

[Abraham.Olson]

Ah, the fun continues. I really ought to buy the VBA power programming
book or something.

So here's the current issue. I have a sub in a module that is public
and a command button in a user form that is private, as below.

The cmdPlaceXcells button is clicked after some cells are selected in
worksheets(2)

Private Sub cmdPlaceXcells_Click()
Dim rngeSelected As Range
Set rngeSelected = ActiveWindow.RangeSelection
Dim aryOfRanges() As Range 'for placing pictures in
Dim maxAryOfRanges As Integer
Dim cell As Range

maxArrOfRanges = 0
'Insert X in each selected cell. Add each selected cell _
' to the aryOfRanges
For Each cell In rngeSelected
maxOfAryOfRanges = maxAryOfRanges + 1
cell.Formula = "X"
ReDim Preserve aryOfRanges(1 To maxAryOfRanges)
Set aryOfRanges(maxArrayOfRanges) = cell
Next cell

TestPictureInsert (aryOfRanges())

End Sub


Public Sub TestPictureInsert(arrayOfRanges() As Range)
Set picQCAnormal = _
ThisWorkbook.Worksheets("Variables").Pictures("Picture 158")
'declarations
Dim rnge As Range
Dim pic As Picture

For Each rnge In arrayOfRanges.Cells
With rnge.Parent 'worksheets(2)
picQCAnormal.Copy
.Paste
Set pic = .Pictures(.Pictures.Count) 'the one just pasted
End With
With pic
.Top = rnge.Top
.Left = rnge.Left
.Width = rnge.Width
.Height = rnge.Height
.Name = "Pic_" & rnge.Address(ReferenceStyle:=xlR1C1)
End With
Next rnge
End Sub


I am quite sure the picture inserting works correctly. I get an error
that states "array or user-defined type expected". Any suggestions??

Thanks,

Abe

 

[Jim Cone]

For one thing, you have multiple misspellings of your variable names.
Add "Option Explicit" at the top of each module and Excel will highlight
them for you.
When you get that squared away, then add the word "Call" in front
of your call to the function or remove the ( ).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


<[email protected]>
wrote in message
Ah, the fun continues. I really ought to buy the VBA power programming
book or something.

So here's the current issue. I have a sub in a module that is public
and a command button in a user form that is private, as below.

The cmdPlaceXcells button is clicked after some cells are selected in
worksheets(2)

Private Sub cmdPlaceXcells_Click()
Dim rngeSelected As Range
Set rngeSelected = ActiveWindow.RangeSelection
Dim aryOfRanges() As Range 'for placing pictures in
Dim maxAryOfRanges As Integer
Dim cell As Range

maxArrOfRanges = 0
'Insert X in each selected cell. Add each selected cell _
' to the aryOfRanges
For Each cell In rngeSelected
maxOfAryOfRanges = maxAryOfRanges + 1
cell.Formula = "X"
ReDim Preserve aryOfRanges(1 To maxAryOfRanges)
Set aryOfRanges(maxArrayOfRanges) = cell
Next cell

TestPictureInsert (aryOfRanges())

End Sub


Public Sub TestPictureInsert(arrayOfRanges() As Range)
Set picQCAnormal = _
ThisWorkbook.Worksheets("Variables").Pictures("Picture 158")
'declarations
Dim rnge As Range
Dim pic As Picture

For Each rnge In arrayOfRanges.Cells
With rnge.Parent 'worksheets(2)
picQCAnormal.Copy
.Paste
Set pic = .Pictures(.Pictures.Count) 'the one just pasted
End With
With pic
.Top = rnge.Top
.Left = rnge.Left
.Width = rnge.Width
.Height = rnge.Height
.Name = "Pic_" & rnge.Address(ReferenceStyle:=xlR1C1)
End With
Next rnge
End Sub


I am quite sure the picture inserting works correctly. I get an error
that states "array or user-defined type expected". Any suggestions??

Thanks,

Abe

 

[Norman Jones]

Hi Abraham,

Try something like:
'=============>>
Private Sub cmdPlaceXcells_Click()
Dim rng As Range
Dim rCell As Range

Set rng = Selection

rng.Value = "X"

Call TestPictureInsert(rng)
End Sub
'<<=============


'=============>>
Public Sub TestPictureInsert(aRng As Range)
Dim rCell As Range
Dim picQCAnormal As Picture
Dim pic As Picture

Set picQCAnormal = ThisWorkbook.Worksheets _
("Variables").Pictures("Picture 1")

For Each rCell In aRng.Cells
With rCell.Parent
picQCAnormal.Copy
.Paste
Set pic = .Pictures(.Pictures.Count)
End With

With pic
.Top = rCell.Top
.Left = rCell.Left
.Width = rCell.Width
.Height = rCell.Height
.Name = "Pic_" & rCell.Address(ReferenceStyle:=xlR1C1)
End With
Next rCell
End Sub
'<<=============

 

[Abraham.Olson]

Thanks Jim and Norman. Works great now. I had forgotten to add an
Option Explicit to the top of the module, that was the source of the
majority of the problems, but its great to see good examples of good
code, esp. as I am just starting out.

-Abe