Late Binding Question

[Siv]

hi,
I am converting an application that writes to an Excel spreadsheet and the
code trips the "option Strict" that I would like on because the parser says
"option Strict On disallows late binding", I am struggling to understand why
I am tripping this error.

This is the code that causes the problems:

XLApp.Goto("MonthTitleTL") 'Goes to bookmark in sheet
r = XLApp.ActiveCell.Row 'sets variable r equal
to the row of that cell
c = XLApp.ActiveCell.Column 'Sets the c variable to
the column of that cell


For n = 0 To 11 'Process the 12 months of
data held in the T7Lines(n) collection
XLApp.Cells(r, c).Formula = T7Lines(n).TextMonthNumber
XLApp.Cells(r + 3, c).formula =
Format(T7Lines(n).RiskPointsTotal, "0.0")
....
Next n

I don't get a) why this is classed as late binding? and b) what I do to stop
it other than turn off option strict which I don't want to do.

Any help appreciated.

Siv
Martley, Near Worcester, UK.

 

[Armin Zingler]

I don't get a) why this is classed as late binding?

Without seeing a variable declaration, it's impossible to see if it's
late bound execution.

and b) what I do
to stop it other than turn off option strict which I don't want to
do.

1. Project -> Add reference -> COM tab -> Add "Microsoft Excel x.y
Object Library"
2. Declare variables, for example, "As Excel.Application"

Then you are bound to the referenced Excel version on the target
machine.


Armin

 

[Siv]

Armin,
Vars as follows:

In the procedure that the code was taken from:

Dim n as integer = 0
Dim r as integer = 0

The XLApp var is declared at the top of the class as follows:
Private XLApp As XL.Application

At the start of the processing block that calls the procedure I copied here,
I test if XLApp is already open and if not open it:

If IsNothing(XLApp) Then
XLApp = New XL.Application
End If

I hope this is what you need.

Siv

 

[Siv]

Armin,
Vars as follows:

In the procedure that the code was taken from:

Dim n as integer = 0
Dim r as integer = 0

The XLApp var is declared at the top of the class as follows:
Private XLApp As XL.Application

At the start of the processing block that calls the procedure I copied here,
I test if XLApp is already open and if not open it:

If IsNothing(XLApp) Then
XLApp = New XL.Application
End If

I hope this is what you need.

Siv

 

[Siv]

Armin,

I added the Excel reference using the "Microsoft.Office.Interop.Excel"
reference in the Dot Net tab. Does adding that as a COM object work better
and if so why?

The version of Excel being referenced is 11.0.0.0 version as the client is
using Office 2003.

Siv

 

[Siv]

Armin,

I added the Excel reference using the "Microsoft.Office.Interop.Excel"
reference in the Dot Net tab. Does adding that as a COM object work better
and if so why?

The version of Excel being referenced is 11.0.0.0 version as the client is
using Office 2003.

Siv

 

[Siv]

Armin,
Sorry about the multiple posts, Windows Mail jus threw a wobbly and kept
refusing to send outbound then after a restart decided to send all the
attempts before I had chance to delete them.

I wanted to add that the XL reference comes from an "Imports" at the top of
the class:

Imports XL = Microsoft.Office.Interop.Excel

Then I do:
Private XLApp as XL.Application at class level.

Hope this makes sense?

Siv

 

[Armin Zingler]

Armin,

I added the Excel reference using the
"Microsoft.Office.Interop.Excel" reference in the Dot Net tab. Does
adding that as a COM object work better and if so why?

Did you already have that reference before asking, or did you just add
it now? Inferred from your question, I thought you did not have any
reference yet. Obviously the PIAs (primary interop assemblies) are
installed on your machine. In this case, it's the preferred way instead
of the COM reference. The PIAs are taylor-made by the manufacturer of
the COM component (Excel). Otherwise, when setting the COM reference,
the Interop assembly is created by a standardized COM-Import procedure
that possibly doesn't fit 100% perfect and is recreated in every project
and every time you set a reference to the COM component.

Assuming that you have a reference to the assembly, the problem
is probably because the type of the default property of a range object
is "Object". Not every object has a property "Formula". Therefore the
error message. You'll have to cast it to the expected type. I don't know
it by heart; something like

Directcast(XLApp.Cells(r, c), DestinationType).Formula ....


Armin

 

[Siv]

Armin,
No the reference was already there, it was added recently because I am
creating a new project from an old one and had to create the project from
scratch add in the files from the existing version's folder into the new
project folder. I wanted to do it this way so that the project files were
imported with the option explicit turned on before they were imported rather
than just copying the folder and then renaming things afterwards. I am
never sure if that works properly especially when assemblies use GUIs that
might get copied between two projects causing some grief for Dot Net to keep
track of it all??

So the references are the same as the old project but they have only just
been added, but this had occurred before the question was raised.

Another issue that seems to back up your comments below is this line:

rng = XLApp.Cells(SheetRow, SheetCol)

Which gives an error because XLApp.Cells(SheetRow, SheetCol) is an object
where XLApp.Cells() is a range.

I will have to get my head around the directcast it's not something I have
used before.

Thanks
Will report back when I have sorted it, or have further questions.

Siv

 

[Siv]

Armin,
I changed the line:

rng = XLapp.Cells(SheetRow, SheetCol)

to

rng = DirectCast(XLApp.Cells(SheetRow, SheetCol), XL.Range)

Which the parser is happy with; whether it works when I run it is another
question!

I will now try and figure how I apply that to the

XLApp.Cells(r + 3, c).formula = Format(T7Lines(n).RiskPointsTotal, "0.0")

Lines??

I have temporarily changed the late binding error as a warning and when I do
that the green wavy line appears under the left side of the argument. I
still don't understand why the compiler thinks this is late bound?? As far
as I can see all the variables are determined so that r+3 and c are known
values? I never did get what all the fuss about late binding is?

Thanks,

Siv

 

[Siv]

Armin,

This seems to pacify the compiler:
DirectCast(XLApp.Cells(r, c), XL.Range).Formula = T7Lines(n).TextMonthNumber

I will see if the program runs as expected after I have corrected all
sections that need amending.

I tried

Siv

 

[Armin Zingler]

Armin,
I changed the line:

rng = XLapp.Cells(SheetRow, SheetCol)

to

rng = DirectCast(XLApp.Cells(SheetRow, SheetCol), XL.Range)

Which the parser is happy with; whether it works when I run it is
another question!

You have to try it because using Directcast means taking the
responsibility of the validity of the cast. The compiler does not know
if the cast will be valid at run time.

(Of course, before, you should know the object model of the COM
component in order to be able to do the cast correctly)

I will now try and figure how I apply that to the

XLApp.Cells(r + 3, c).formula = Format(T7Lines(n).RiskPointsTotal,
"0.0")

Lines??

I have temporarily changed the late binding error as a warning and
when I do that the green wavy line appears under the left side of
the argument. I still don't understand why the compiler thinks this
is late bound?? As far as I can see all the variables are determined
so that r+3 and c are known values?

XLApp.Cells(r + 3, c).Formula

is actually

XLApp.Cells.Default(r + 3, c).Formula

The type of the Default property is Object. The type Object does not
have a property named 'Formula'. The Cells property is not indexed. The
Default property of the Range object returned by the Cells property is
indexed.

I never did get what all the
fuss about late binding is?

The short story (sorry if you know it already):
Among other tasks, compiling means resolving names/identifiers, which is
replacing names with memory addresses/offsets. The compiler knows all
members of a type and therefore knows how to do the resolution. That's
early binding.

Late binding means turning off name resolution at compile time for those
members that can not be found. Instead, it is done at run time. This is
only possible because meta data is
stored in the managed assemblies. Every time a late bound access takes
place (maybe optimized internally), the runtime has to check whether the
name of the accessed member exists by searching for the member name,
which is a String, and, if found, the access can be done (after checking
arguments etc...). This technique is a part of "reflection". This takes
a lot of time, and there is the danger that a name not even exists,
which will lead to an exception.

Therefore, as a basic rule, early binding should be used because
excluding errors has a very high priority (or should have). Using Excel
Interop is one of the few exceptions. If you don't want to be tied to a
specific Excel version (AFAIK; never used on my own, yet), you have to
use early binding.

What do I say... search for "reflection" and you'll get 1001 (better)
explanations.


Armin

 

[Lloyd Sheen]

Armin,

This seems to pacify the compiler:
DirectCast(XLApp.Cells(r, c), XL.Range).Formula =
T7Lines(n).TextMonthNumber

I will see if the program runs as expected after I have corrected all
sections that need amending.

I tried

Siv

You were asking about why late binding is "bad". When you late bind there
is a perfomance penalty to pay for each call. Since the compiler does not
know what type of object is being used until runtime it must each time
determine whether or not the property, function or subroutine is valid for
the target object at runtime. This takes time.

There is also the possiblity that the object at runtime will not support the
call. This will result in an exception which can be trapped using try -
catch but again this is very costly in comparison to an early binding call.

So really two reason not to use late binding if you can. One performance
and second program integrity.

LS

 

[Siv]

Lloyd,

Thanks for the clarification. I wish I understood more about the internal
workings of compilers as I would not find this stuff so confusing.
One day, I'll make some time and try and get my head around them. What you
say makes absolute sense and that is really why I am modifying this code
from its original version. I just hadn't equated that particular error as
being caused by the same things I was looking to correct in the code by
turning on option explicit.

I can see why C++ programmers think VBers are less capable than them as we
have been shielded from this stuff in the past, and still are to an extent
when we don't turn on option explicit. At the end of the day, I am going
through some pain understanding why this stuff matters and hopefully as a
result am becoming a better programmer and producing faster less error prone
code as a result.

Long way to go though I suspect!

Cheers for your help and advice.

Siv

 

[Siv]

Armin,

I feel like I am getting somewhere. Your reply along with the reply from
Lloyd makes me feel I am beginning to get my head around the issues with
types and compilers. As you say if it improves speed and reduces the
likelihood of errors then that is great.

I have been a VB developer since VB1 and can see now that I have been
shielded from the truth about programming to a certain extent. I am not
upset with this, to be honest when I was starting out, if it had been this
complicated I would probably have given up, so the gentle lead in to
programming with VB was just what I needed. I just have to learn that there
was a lot of detail that I have been shielded from and could still be
shielded from if I was to leave "Option Explicit" turned off.

Thanks again for your help,

Siv

 

[Armin Zingler]

Armin,

I feel like I am getting somewhere. Your reply along with the reply
from Lloyd makes me feel I am beginning to get my head around the
issues with types and compilers. As you say if it improves speed
and reduces the likelihood of errors then that is great.

I have been a VB developer since VB1 and can see now that I have
been shielded from the truth about programming to a certain extent.
I am not upset with this, to be honest when I was starting out, if
it had been this complicated I would probably have given up, so the
gentle lead in to programming with VB was just what I needed. I
just have to learn that there was a lot of detail that I have been
shielded from and could still be shielded from if I was to leave
"Option Explicit" turned off.

Thanks again for your help,

Finally, many roads lead to Rome.


Armin

 

[mario]

Hi,

I don't know did you resolve your issue as you expected, but i would
recommend you to use some third party .NET components for Importing/
Exporting Excel files. I recommend you to use GemBox.Spreadsheet --
you can see why it is better than Excel Automation:
http://www.gemboxsoftware.com/GBSpreadsheet.htm#Automation.

Mario
GemBox Software