-1

I'm having a problem applying a loop for building rectangles in a spreadsheet, I'm getting error 1004,

Run-time error '1004':
Application-defined or object-defined error

On line:

Leftmargin = ThisWorkbook.Worksheets(2).Range(Cells(1, 1)).Left

I have no idea why this doesn't work. I would also want to switch to cells function with the rest of the parameters as I need to loop over 450 positions to build rectangles as for cards. I'm asking for explanation on the topic so that I can understand the cause of failure.

Dim sh As Shape

Dim Leftmargin
Leftmargin = ThisWorkbook.Worksheets(2).Range(Cells(1, 1)).Left
Leftmargin = Leftmargin + 2

Set sh = ThisWorkbook.Worksheets(2).Shapes.AddShape( _
  Type:=msoShapeRoundedRectangle, _
  Left:=Leftmargin, _
  Top:=ThisWorkbook.Worksheets(2).Range("A1").Top + 2, _
  Width:=ThisWorkbook.Worksheets(2).Range("A1:E1").Width - 4, _
  Height:=ThisWorkbook.Worksheets(2).Range("A1:A10").Height - 4)
GSerg
  • 76,472
  • 17
  • 159
  • 346
Michał Georgiew
  • 1
  • 1
  • 1
  • 6
    A `Range()` needs two `Cells()` you should use `Cells()` instead. – Damian Dec 03 '19 at 17:12
  • 1
    @MathieuGuindon While you are correct about qualifying calls, the OP's problem here is that, as Damian said above, that you cannot pass *one* `Range`-typed argument to `Range()`. – GSerg Dec 03 '19 at 19:40
  • @GSerg that is correct. Also the only reason for this to go undetected, is the implicit late binding induced by chaining the `.Range` call to `Workbook.Worksheets(2)`, which returns an `Object`. By pulling that `Worksheet` object into its own local variable, OP would restore early binding and compile-time checks, and get intellisense / parameter quick-info display a tooltip showing the parameters expected by the `.Range` property. Should the question be reopened? – Mathieu Guindon Dec 03 '19 at 19:45
  • @MathieuGuindon Not really caused by late binding, no. The second parameter to `Range()` is optional, and `Dim w As Worksheet : MsgBox w.Range(w.Cells(1, 1)).Address` compiles perfectly. Yes, I believe the question should be reopened. – GSerg Dec 03 '19 at 19:52
  • 1
    @GSerg here is a more appropriate duplicate: https://stackoverflow.com/questions/17636170/vba-excel-range-with-cell-argument – Scott Craner Dec 03 '19 at 20:04

2 Answers2

0

Cells(1, 1) is already a Range object, referring to cell A1 (row 1, column 1) of (assuming this is in a standard module) whatever worksheet happens to be the ActiveSheet.

Leftmargin = ThisWorkbook.Worksheets(2).Range(Cells(1, 1)).Left

But you mean to work with the sheet at index 2 of ThisWorkbook. Pull it into its own local variable:

Dim sheet As Worksheet
Set sheet = ThisWorkbook.Worksheets(2)

Now you no longer need to dereference ThisWorkbook.Worksheets(2) everywhere you need to get ahold of that particular sheet: just use this variable.

So Cells(1, 1) becomes sheet.Cells(1, 1) -- except it's still already a range, so the member call is either redundant, or malformed:

Leftmargin = sheet.Range(sheet.Cells(1, 1)).Left '<~ redundant or malformed range

When you pass Range objects to the Worksheet.Range property, you want to give it 2 cells: the first is the cell in the upper-left corner of the range you want; the second is the cell in the bottom-right corner of the range you want... but since you're using A1 and you want the .Left property of that cell, what you want is probably just this:

Leftmargin = sheet.Cells(1, 1).Left + 2

Set sh = sheet.Shapes.AddShape( _
  Type:=msoShapeRoundedRectangle, _
  Left:=Leftmargin, _
  Top:=sheet.Range("A1").Top + 2, _
  Width:=sheet.Range("A1:E1").Width - 4, _
  Height:=sheet.Range("A1:A10").Height - 4)

...but then I can't help but question why sh.Left would get to be precalculated, but not the other properties... why not just do this and drop the Leftmargin variable altogether?

Set sh = sheet.Shapes.AddShape( _
  Type:=msoShapeRoundedRectangle, _
  Left:=sheet.Range("A1").Left + 2, _
  Top:=sheet.Range("A1").Top + 2, _
  Width:=sheet.Range("A1:E1").Width - 4, _
  Height:=sheet.Range("A1:A10").Height - 4)

Note that Worksheet.Cells(1, 1) and Worksheet.Range("A1") both refer to the same cell/range.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • 1
    Note that `Worksheet.Cells(1)` also refers to same cell. Could have also used `With sheet.Range("A1:E10")` to reduce further the redundancy. – EEM Dec 04 '19 at 06:10
-1

After reading your answers and related links (in this posts), I came to the result which works for me:

Set sh1 = ThisWorkbook.Worksheets(1)
Set sh2 = ThisWorkbook.Worksheets(2)

Set sh = ThisWorkbook.Worksheets(2).Shapes.AddShape( _
Type:=msoShapeRoundedRectangle, _
Left:=sh2.Range(Cells(1, 1), Cells(1, 1)).Left + 2, _
Top:=sh2.Range(Cells(1, 1), Cells(1, 1)).Top + 2, _
Width:=sh2.Range(Cells(1, 1), Cells(1, 5)).Width - 4, _
Height:=sh2.Range(Cells(1, 1), Cells(10, 1)).Height - 4)

This way I prepared whole statement for looping through whole list of items, creating cards for it.

Thank you, Michal

Michał Georgiew
  • 1
  • 1
  • 1