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`](http://stackoverflow.com/questions/10714251). — David Zemens, Jul 28 '17 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, Jul 28 '17 at 13:41
What is the idea of this `Cells(i, 64).Select` ? It goes to Column `BL`? — Vityata, Jul 28 '17 at 13:43
This has nothing to do with protection. Even if i put a small input like 64, when it reaches line Cells(i, gridSize).Select when it is trying to do the left side, it crash with runtime error @DavidZemens I will try using Dim rng as Range and update. — user3449011, Jul 28 '17 at 13:44
@user3449011 - well, bot codes "work" for me. Try openning an empty Excel file and run them there. — Vityata, Jul 28 '17 at 13:48
@Vityata try running it dynamic. Create a msgbox and enter value like 64. Then run it. When I don't do it dynamic it works. Any suggestions? — user3449011, Jul 28 '17 at 13:51
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, Jul 28 '17 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, Jul 28 '17 at 14:02

Vityata · Accepted Answer · 2017-07-28T14:26:36.720

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` :) — David Zemens, Jul 28 '17 at 13:55
@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`. — Vityata, Jul 28 '17 at 13:57
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 :) — David Zemens, Jul 28 '17 at 14:01
@user3449011 - see the edit, now it is dynamic. Plus, the 4. time I am calling from an InputBox. — Vityata, Jul 28 '17 at 14:01
`InputBox("Dynamically", Type:=1)` will *require* a numeric input — David Zemens, Jul 28 '17 at 14:02
@Vityata The problem was that i used Integer instead of Long (not sure why.). Thanks for help everyone. — user3449011, Jul 28 '17 at 14:06
In your original code change only to this -> `gridSize = InputBox("Enter grid size:",type:=1)`. Then it should actually work as well. @user3449011 — Vityata, Jul 28 '17 at 14:09

score 1 · Answer 2 · 2017-07-28T14:18:22.293

1

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.

edited Jul 28 '17 at 14:18

answered Jul 28 '17 at 14:08

Just the borders, without borders for the internal cells. – Vityata Jul 28 '17 at 14:11
1

OK, adjusted per your requirements above. – Jul 28 '17 at 14:18
1

but you do not have the **animation** effect from the selection of the cells :) – Vityata Jul 28 '17 at 14:22
1

Well, maybe I could import some kernel functions and make the cursor dance a bit. :D – Jul 28 '17 at 14:31

Excel VBA error 1004 when using range

2 Answers2