A
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.)
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.)