R
relative_virtue
Some bright souls suggested that my many-user spreadsheet might
benefit from late bound references rather than early bound, in order
to be compatible with the myriad versions of Excel being used by my
colleagues. I am beginning to see their point, but I'm still rather
perplexed about how to implement their solution and would appreciate
some help.
For a start, to what extent do I need to do carry out this exercise?
I have around 2,000 lines of code, much of it referencing early bound
Excel objects, such as Worksheet or Range. Do all of these need to be
redeclared? And if so, do I then need to use
CreateObject("Excel.Range") every time I want to make a cell
reference?
I'm a quick learner, but would appreciate a starter for 10 on this one
- can anyone show me how this sub should look with late bound
references?
'START QUOTE
Sub ApplyCommonFormat(MyRange As String, Optional IgnoreBottomRow As
Boolean)
With ActiveSheet.Range(MyRange)
With .Borders(xlEdgeLeft)
.Color = 0
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.Color = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.Color = 0
.Weight = xlThin
End With
.HorizontalAlignment = xlCenter
End With
If IgnoreBottomRow = False Then
With ActiveSheet.Range(MyRange).Offset(rowoffset:=1)
With .Borders(xlEdgeTop)
.Color = 0
.Weight = xlThick
End With
End With
End If
End Sub
'END QUOTE
Would be ever so grateful!
Best regards,
Tristan
benefit from late bound references rather than early bound, in order
to be compatible with the myriad versions of Excel being used by my
colleagues. I am beginning to see their point, but I'm still rather
perplexed about how to implement their solution and would appreciate
some help.
For a start, to what extent do I need to do carry out this exercise?
I have around 2,000 lines of code, much of it referencing early bound
Excel objects, such as Worksheet or Range. Do all of these need to be
redeclared? And if so, do I then need to use
CreateObject("Excel.Range") every time I want to make a cell
reference?
I'm a quick learner, but would appreciate a starter for 10 on this one
- can anyone show me how this sub should look with late bound
references?
'START QUOTE
Sub ApplyCommonFormat(MyRange As String, Optional IgnoreBottomRow As
Boolean)
With ActiveSheet.Range(MyRange)
With .Borders(xlEdgeLeft)
.Color = 0
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.Color = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.Color = 0
.Weight = xlThin
End With
.HorizontalAlignment = xlCenter
End With
If IgnoreBottomRow = False Then
With ActiveSheet.Range(MyRange).Offset(rowoffset:=1)
With .Borders(xlEdgeTop)
.Color = 0
.Weight = xlThick
End With
End With
End If
End Sub
'END QUOTE
Would be ever so grateful!
Best regards,
Tristan