Excel VBA error 1004 when using range

Question

I have a work in VBA where I need to make a square size of 512X512 (cellsXcells). The square suppose to be with the borders of the cells. I made the size of the square dynamic so user can insert the size he wants (max is 512).

Now I tried with few techniques to do the above but always I fail because of error 1004 run time.

Sub printGrid(gridSize) Range(Cells(1, 1), Cells(gridSize, gridSize)).Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick End With End With End Sub

My second attempt was to do it cell by cell...

Sub printBorders(gridSize) For i = 1 To gridSize ' right side Cells(i, 1).Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick End With Next i For i = 1 To gridSize ' bottom Cells(gridSize, i).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick End With Next i For i = gridSize To 1 Step -1 ' top Cells(1, i).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick End With Next i For i = 1 To gridSize ' center Cells(i, 64).Select With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick End With Next i For i = 1 To gridSize ' left Cells(i, gridSize).Select With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick End With Next i End Sub

At printBorders I fail when I try to make the left grid. (Cells(i, gridSize).Select).

I am starting to think that this is sort kind of a limit in excel. Thanks for the help!

EDIT:

When I mean dynamic: Please trying running it with inputs like 64 or 512.

Sub main() Dim gridSize As Integer, numOfDots As Integer Call setGrid End Sub Sub setGrid() Dim size As Integer Cells.Select Selection.Delete Shift:=xlUp gridSize = setGridSize() Call printGrid2(1, 1, gridSize, gridSize) 'Call printGrid2(1, 1, gridSize, gridSize / 2) End Sub Function setGridSize() Do While True gridSize = InputBox("Enter grid size:") If (IsNumeric(gridSize)) And (gridSize Mod 2 = 0) Then setGridSize = gridSize Exit Do End If Loop End Function Sub printGrid2(x, y, rowSize, colSize) Dim rng As Range Set rng = Range(Cells(x, y), Cells(rowSize, colSize)) With rng.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick End With With rng.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick End With With rng.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick End With With rng.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick End With End Sub

Is the worksheet, or any part of it Protected? If so, you may not be able to select or perform certain actions. Also, it's generally advisable to avoid relying on Select or Activate. — David Zemens
– David Zemens, Commented Jul 28, 2017 at 13:40
So instead of Cells(i, 1).Select and With Selection... just do With Cells(i, 1).Borders(xlEdgeLeft). But if the sheet is protected, you still may not be able to do that. — David Zemens
– David Zemens, Commented Jul 28, 2017 at 13:41
What is the idea of this Cells(i, 64).Select ? It goes to Column BL? — Vityata
– Vityata, Commented Jul 28, 2017 at 13:43
While it's a good idea to use properly assigned range variables, that doesn't seem likely to solve the problem in this case. 1004 usually indicates a Protection issue, or that you've defined an inaccessible object (e.g., Cells(0, 2) will fail because it doesn't/can't exist. — David Zemens
– David Zemens, Commented Jul 28, 2017 at 13:51
Worth noting that YOUR code does not raise any error (apart from the compile error, which can be resolved by declaring your variables properly) when I run it in a blank workbook. — David Zemens
– David Zemens, Commented Jul 28, 2017 at 14:02

Vityata · Accepted Answer · 2017-07-28 14:26:36Z

This is how I run the code, "dynamically". After removing Option Explicit, on a new Excel and trying not to look at the selections, it works:

Sub DynamicTest() printBorders (10) printBorders (20) printBorders (30) printBorders (InputBox("Dynamically")) End Sub Sub printBorders(gridSize As Long) For i = 1 To gridSize ' right side Cells(i, 1).Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick End With Next i For i = 1 To gridSize ' bottom Cells(gridSize, i).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick End With Next i For i = gridSize To 1 Step -1 ' top Cells(1, i).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick End With Next i For i = 1 To gridSize ' center Cells(i, 64).Select With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick End With Next i For i = 1 To gridSize ' left Cells(i, gridSize).Select With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick End With Next i End Sub

This is what I have changed:

From: Sub printBorders(gridSize)

To: Sub printBorders(gridSize As Long)

Or you may ask for a numeric input in the InputBox like this: InputBox("Dynamically", Type:=1). (Credit to David Zemens)

In general, this is why the error appears:

The Cells takes parameter overloading in VBA. This means, that you can refer to Cells with any of these two:

Cells(Long, Long) -> Cells(1,1)

Cells(Long, String) -> Cells(1,"A")

gridSize is a String, but it is no problem to be used in a for-loop as such, because it is internally casted to a Numeric value.

However, when you try to select Cells(i, gridSize), VBA looks for the function Cells(Long, String) first. It expects that the String is a column name. However, you do not have a column named 111, thus it throws an error.

Why did you remove Option Explicit? Seems better to just declare the variable i :)
@DavidZemens - but you are not using it Dynamically, passing a String value from the InputBox as a parameter :) Then, indeed, you get a 1004 error somewhere on the last for-loop.
No, I'm not, but that can be handled by simply type-casting the return from InputBox, or requiring the InputBox to return numeric values :)
@user3449011 - see the edit, now it is dynamic. Plus, the 4. time I am calling from an InputBox.

score 1 · Accepted Answer · 2017-07-28 14:18:22Z

If I am reading this correctly, all you want to do is draw a square grid with surrounding borders. You do not have to perform the individual operations.

Option Explicit Sub test() printGrid 6 End Sub Sub printGrid(gridSize) With Worksheets("sheet1") With .Cells(1, 1).Resize(gridSize, gridSize) .BorderAround LineStyle:=xlContinuous, Weight:=xlThick End With End With End Sub

Recorded code often performs much more than is necessary. It is best to chop it down to what is actually needed.

but you do not have the animation effect from the selection of the cells :)
Well, maybe I could import some kernel functions and make the cursor dance a bit. :D

Collectives™ on Stack Overflow

Excel VBA error 1004 when using range

2 Answers 2

9 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

4 Comments

Linked

Related