Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to late binding (for compatibility)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to late binding (for compatibility)
Take a look at this article http://xldynamic.com/source/xld.EarlyLate.html
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to late binding (for compatibility)
Thanks Bob,
I checked this link out but I'm not sure if I understand how to apply its methods in Excel. My guess would be something like this: Sub ApplyCommonFormat(MyRange As String, Optional IgnoreBottomRow As Boolean) Dim objSheet as Object Set objSheet = CreateObject("Excel.Worksheet") With objSheet.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 Would this be right? Tristan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to late binding (for compatibility)
Late binding only makes sense in automation. If you are trying to access
Excel from within Excel (VBA) then late binding is pointless, you are already bound to Excel. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... Thanks Bob, I checked this link out but I'm not sure if I understand how to apply its methods in Excel. My guess would be something like this: Sub ApplyCommonFormat(MyRange As String, Optional IgnoreBottomRow As Boolean) Dim objSheet as Object Set objSheet = CreateObject("Excel.Worksheet") With objSheet.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 Would this be right? Tristan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to late binding (for compatibility)
Bob,
Ah - see, this is what I was trying to get my head around. So if I was only referencing the Excel type library, there would be no need to rework the spreadsheet for backwards compatibility? Best regards, Tristan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to late binding (for compatibility)
Not if you are already working in Excel.
Late binding is used when accessing an application from within another application. For instance, say you wanted to run some code that gets data from a spreadsheet, and wants to write some of it to a Word document. You would need to either start Word, or connect to a running instance of Word, from within your (Excel) VBA code. You have the choice of using early binding here, which means that you set a reference to the Word object library from within your Excel VBA code. By connecting early, the code has the advantage of being able to provide intellisense on any Word objects that you may use in the code, and can resolve those references at compile time. The problem comes if you have a reference to say Word 2003, and someone else has Word 2000 on their machine. They open your Excel workbook with the code, and it fails. Late binding can overcome this as by not setting the reference, the type library is looked up at run time (it looks in the registry to see what version is available). This means that you don't get Word intelli-sense, and it is much less efficient, and as every time it comes across a Word object, property, method, it has to go to the type library and check that it is valid, and the usage is valid. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... Bob, Ah - see, this is what I was trying to get my head around. So if I was only referencing the Excel type library, there would be no need to rework the spreadsheet for backwards compatibility? Best regards, Tristan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
late binding | Excel Programming | |||
Late Binding to a dll | Excel Programming | |||
Help converting code to late binding | Excel Programming | |||
Late Binding examples of binding excel application | Excel Programming | |||
EARLY binding or LATE binding ? | Excel Programming |