error 1004 when assigning an array to a range

[Amedee Van Gasse]

Hello,

The following is a piece of code that I inherited and that I'm trying
to optimize.
In short it does the following:
* transform the value of each cell in a template range and put the
result in an array
* assign the array to a result range
* iterate trough the range, and set each cell's comment to the cell's
value
* again, transform the value of each cell in another template range
and put the result in an array
* assign the array to the same result range


' GENERATE ALL VERTICAL DATAS
Dim rng As Range
Set rng = Range(Cells(Ld_Recurrent, c1), Cells(Lf_Gral,
c1))

' Make and get Comments Data
-----------------------------------------
Dim CommentTemplateRange As Range
Set CommentTemplateRange = Range(Cells(Ld_Recurrent,
C_TemplateRef + 1), _
Cells(Lf_Gral, C_TemplateRef + 1))
Dim CommentArray As Variant
CommentArray = CommentTemplateRange
Dim i As Integer
For i = LBound(CommentArray, 1) To UBound(CommentArray, 1)
CommentArray(i, 1) = Template2Formula(CStr(CommentArray
(i, 1)), Excel_File_Shortname)
Next
rng = CommentArray

Dim Comment As String
For i = Ld_Recurrent To Lf_Gral
With Cells(i, c1)
Comment = .Value
If (Comment <> vbNullString) And (Comment <> "0")
Then
.AddComment CStr(Comment)
.Comment.Visible = False
End If
End With
Next

' Make Figures formula
----------------------------------------------
Dim FiguresTemplateRange As Range
Set FiguresTemplateRange = Range(Cells(Ld_Recurrent,
C_TemplateRef), Cells(Lf_Gral, C_TemplateRef))
Dim FiguresArray As Variant
FiguresArray = FiguresTemplateRange
For i = LBound(FiguresArray, 1) To UBound(FiguresArray, 1)
FiguresArray(i, 1) = Template2Formula(CStr(FiguresArray
(i, 1)), Excel_File_Shortname)
Next
rng = FiguresArray


I am getting an error 1004 on that last line, and I don't understand
why.


What happens there, is a 1-dimensional array that is copied to a
range. This should copy the value of each item of the array to the
corresponding item in the array. Each item in the array is a variant.
The array and the range have the same number of items.
But the same thing is done on the line with rng = CommentArray! Both
lines are functionally equivalent! Why doesn't it give an error the
first time?

This code used to work fine, until a few strings were changed in the
FiguresTemplateRange. But after running them trough Template2Formula,
they still evaluate to valid formulas.

I'm puzzled.

(Oh and by the way, one small optimisation would be to re-use the
array because it is always the same size in both cases. I know. It
would save some memory, but it wouldn't speed things up.)

 

[Shane Devenshire]

Hi,

The 1004 error usually occurs in this situation because the rng is not
exactly the same size as the array. It's also not clear if the dim statement
is in the same subroutine as the last line of code. If not you need to
declare it outside the subroutine.

It is good coding style to declare all variables at the top of a sub
routine, not scattered throughout.

Cheers,
Shane

 

[Amedee Van Gasse]

Hi,

The 1004 error usually occurs in this situation because the rng is not
exactly the same size as the array.

Hi,
The rng is exactly the same size as the array, I wrote that already:

It's also not clear if the dim statement is in the same subroutine
as the last line of code.  If not you need to declare it outside the
subroutine.

The dim statement is on the second line (first line is a comment)
so yes the dim statement really is in the same subroutine as the
last line of code.

Declaring it outside the subroutine is really bad coding practice
because why declare a variable when you are never going to enter
the scope where it will be used? And global variables shouldn't be
Dimmed, they should be Public or Private.

It is good coding style to declare all variables at the top of a sub
routine, not scattered throughout.  

I respectfully disagree.
While I agree that the code still needs major cleanup, it is very
*bad*
coding practice to have a behemoth of variable declarations at the
top. Quite often you'll have a variable that is only used inside a
conditional statement (If of Select Case) that you may never enter
in a particular run of your code, depending on the data you feed it.
It is *good* coding practice to declare variables only when and
where you need them.
On the other hand, comments at the top of the code explaining the
variables that wille be used, that's not forbidden.


Anyway, it seems like you don't know why this code is going wrong
either.

 

[Amedee Van Gasse]

I did some more debugging.

I changed a small part of the code so that the array isn't assigned to
the range in one operation, but cell per cell.
This is much slower but it allows me to find the problem.

OLD CODE:
' Make Figures formula
----------------------------------------------
Dim FiguresTemplateRange As Range
Set FiguresTemplateRange = Range(Cells(Ld_Recurrent,
C_TemplateRef), Cells(Lf_Gral, C_TemplateRef))
Dim FiguresArray As Variant
FiguresArray = FiguresTemplateRange
For i = LBound(FiguresArray, 1) To UBound(FiguresArray,
1)
FiguresArray(i, 1) = Template2Formula(CStr
(FiguresArray
(i, 1)), Excel_File_Shortname)
Next
rng = FiguresArray


NEW CODE:
' Make Figures formula
----------------------------------------------
Dim FiguresTemplateRange As Range
Set FiguresTemplateRange = Range(Cells(Ld_Recurrent,
C_TemplateRef), Cells(Lf_Gral, C_TemplateRef))
Dim FiguresArray As Variant
FiguresArray = FiguresTemplateRange
For i = LBound(FiguresArray, 1) To UBound(FiguresArray,
1)
FiguresArray(i, 1) = Template2Formula(CStr(FiguresArray
(i, 1)), Excel_File_Shortname)
On Error Resume Next
rng.Cell(1, 1).Offset(i - 1, 1).Formula = FiguresArray
(i, 1)
On Error Goto 0
Next
'rng = FiguresArray


All values in FiguresArray are Variant/String, and all of them are
valid Excel formulas.
Most of them look like this: (typical example)
='X:\Directory Name\[File Name.xls]Sheet Name'!$D$102

But a few of them look like this:
=OFFSET('X:\Directory Name\[File Name.xls]Sheet Name'!$S$4;MATCH(P
$12;'X:\Directory Name\[File Name.xls]Sheet Name'!$K:$K;0)-4;ROW()-$A
$8+1;1;1)

The code breaks on array items that have a string with the OFFSET
function. I found this by substituting various parts of the formula.

Now the really, really weird thing is, when I get the error 1004 and
go into debug mode.
I get the current value (formula) with
? FiguresArray(i, 1)
and I paste that in the right cell. This works!!!
So the completely stupid thing is that I can do it manually, but that
VBA just refuses to do exactly the same thing.
And only with the OFFSET function!
(Haven't tried all functions, but some frequently used ones like SUM,
AVERAGE, COUNT,... were no problem)



Please, I need a second pair of eyes.
Make an array of Variant/String, put valid formula in them, and check
if it breaks with the OFFSET function.
By the way, I didn't mention: Excel 2003sp2

TIA,

Amedee

 

[Amedee Van Gasse]

I did some more debugging.

I changed a small part of the code so that the array isn't assigned to
the range in one operation, but cell per cell.
This is much slower but it allows me to find the problem.

OLD CODE:
'           Make Figures formula
----------------------------------------------
            Dim FiguresTemplateRange As Range
            Set FiguresTemplateRange = Range(Cells(Ld_Recurrent,
C_TemplateRef), Cells(Lf_Gral, C_TemplateRef))
            Dim FiguresArray As Variant
            FiguresArray = FiguresTemplateRange
            For i = LBound(FiguresArray, 1) To UBound(FiguresArray,
1)
                FiguresArray(i, 1) = Template2Formula(CStr
(FiguresArray
(i, 1)), Excel_File_Shortname)
            Next
            rng = FiguresArray

NEW CODE:
'           Make Figures formula
----------------------------------------------
            Dim FiguresTemplateRange As Range
            Set FiguresTemplateRange = Range(Cells(Ld_Recurrent,
C_TemplateRef), Cells(Lf_Gral, C_TemplateRef))
            Dim FiguresArray As Variant
            FiguresArray = FiguresTemplateRange
            For i = LBound(FiguresArray, 1) To UBound(FiguresArray,
1)
                FiguresArray(i, 1) = Template2Formula(CStr(FiguresArray
(i, 1)), Excel_File_Shortname)
                On Error Resume Next
                rng.Cell(1, 1).Offset(i - 1, 1).Formula = FiguresArray
(i, 1)
                On Error Goto 0
            Next
            'rng = FiguresArray

All values in FiguresArray are Variant/String, and all of them are
valid Excel formulas.
Most of them look like this: (typical example)
='X:\Directory Name\[File Name.xls]Sheet Name'!$D$102

But a few of them look like this:
=OFFSET('X:\Directory Name\[File Name.xls]Sheet Name'!$S$4;MATCH(P
$12;'X:\Directory Name\[File Name.xls]Sheet Name'!$K:$K;0)-4;ROW()-$A
$8+1;1;1)

The code breaks on array items that have a string with the OFFSET
function. I found this by substituting various parts of the formula.

Now the really, really weird thing is, when I get the error 1004 and
go into debug mode.
I get the current value (formula) with
? FiguresArray(i, 1)
and I paste that in the right cell. This works!!!
So the completely stupid thing is that I can do it manually, but that
VBA just refuses to do exactly the same thing.
And only with the OFFSET function!
(Haven't tried all functions, but some frequently used ones like SUM,
AVERAGE, COUNT,... were no problem)

Please, I need a second pair of eyes.
Make an array of Variant/String, put valid formula in them, and check
if it breaks with the OFFSET function.
By the way, I didn't mention: Excel 2003sp2

TIA,

Amedee

I'm getting migrain.
Is there another way to write
=OFFSET('Template List'!$S$4;MATCH(P$12;'Template List'!$K:$K;0)-4;ROW
()-$A$8+1;1;1)
without using OFFSET?

Amedee

 

[Amedee Van Gasse]

I'm getting migrain.
Is there another way to write
=OFFSET('Template List'!$S$4;MATCH(P$12;'Template List'!$K:$K;0)-4;ROW
()-$A$8+1;1;1)
without using OFFSET?

More migrain...

=OFFSET('Template List'!$S$4;MATCH(P$12;'Template List'!$K:$K;0)-4;ROW
()-$A$8+1;1;1)
and
=VLOOKUP(P$12;'Template List'!$K:$X;10+ROW()-$A$8;FALSE)
give the same results (in the workbook I'm debugging).
The VLOOKUP is even a bit simpler.

And guess what... the VLOOKUP version *also* gives error 1004.

Voodoo...

Amedee.

 

[Dave Peterson]

If one of the values in that array is really the string:

"=VLOOKUP(P$12;'Template List'!$K:$X;10+ROW()-$A$8;FALSE)"

Then try changing the semicolons to commas. VBA is very USA centric when
applying the formula. And excel will convert the list separator to what is
specified in the windows regional settings.

I'd try a string like:
"=VLOOKUP(P$12,'Template List'!$K:$X,10+ROW()-$A$8,FALSE)"

ps. =offset() won't work with closed files. If you decide to dump the
=vlookup(), maybe you could use =index().

 

[Amedee Van Gasse]

If one of the values in that array is really the string:

"=VLOOKUP(P$12;'Template List'!$K:$X;10+ROW()-$A$8;FALSE)"

Then try changing the semicolons to commas.  VBA is very USA centric when
applying the formula.  And excel will convert the list separator to what is
specified in the windows regional settings.

I'd try a string like:
"=VLOOKUP(P$12,'Template List'!$K:$X,10+ROW()-$A$8,FALSE)"

Thank you soooo much!
I was so focused on the incredible complexity of the calculations,
that I totally missed something trivial like semicolons and commas.
Thank you again for your fresh perspective.

ps. =offset() won't work with closed files. If you decide to dump the
=vlookup(), maybe you could use =index().

I don't think it will be an issue, because in this formula only the
current file is referenced.
But I'll keep it in mind for other calculations and lookups in the
workbook.