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]

"Siv" <[e-mail]

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

"Armin Zingler" <az*******@freenet.dewrote in message
news:uQ**************@TK2MSFTNGP05.phx.gbl...

"Siv" <[e-mail]

>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

"Armin Zingler" <az*******@freenet.dewrote in message
news:uQ**************@TK2MSFTNGP05.phx.gbl...

"Siv" <[e-mail]

>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,

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

"Armin Zingler" <az*******@freenet.dewrote in message
news:uQ**************@TK2MSFTNGP05.phx.gbl...

"Siv" <[e-mail]

>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,

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

"Armin Zingler" <az*******@freenet.dewrote in message
news:uQ**************@TK2MSFTNGP05.phx.gbl...

"Siv" <[e-mail]

>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,
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" <az*******@freenet.dewrote in message
news:uQ**************@TK2MSFTNGP05.phx.gbl...

"Siv" <[e-mail]

>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

[Armin Zingler]

"Siv" <[e-mail]

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


"Armin Zingler" <az*******@freenet.dewrote in message
news:ew**************@TK2MSFTNGP02.phx.gbl...

"Siv" <[e-mail]

>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,
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
"Armin Zingler" <az*******@freenet.dewrote in message
news:ew**************@TK2MSFTNGP02.phx.gbl...

"Siv" <[e-mail]

>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,

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

"Siv" <[e-mail] in message
news:C7**********************************@microsof t.com...

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
"Armin Zingler" <az*******@freenet.dewrote in message
news:ew**************@TK2MSFTNGP02.phx.gbl...

>"Siv" <[e-mail]

>>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

[Lloyd Sheen]

"Siv" <[e-mail] in message
news:B3**********************************@microsof t.com...

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

"Siv" <[e-mail] in message
news:C7**********************************@microsof t.com...

>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
"Armin Zingler" <az*******@freenet.dewrote in message
news:ew**************@TK2MSFTNGP02.phx.gbl...

>>"Siv" <[e-mail]
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

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

"Lloyd Sheen" <[e-mail] in message
news:eD**************@TK2MSFTNGP02.phx.gbl...

>
"Siv" <[e-mail] in message
news:B3**********************************@microsof t.com...

>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

"Siv" <[e-mail] in message
news:C7**********************************@microso ft.com...

>>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
"Armin Zingler" <az*******@freenet.dewrote in message
news:ew**************@TK2MSFTNGP02.phx.gbl...
"Siv" <[e-mail]
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

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

[Armin Zingler]

"Siv" <[e-mail]

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/GBSpre...htm#Automation.

Mario
GemBox Software
--
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV)
or export to HTML files from your .NET apps. See
http://www.gemboxsoftware.com/GBSpreadsheet.htm
--
On May 19, 12:19 am, "Armin Zingler" <[e-mail]:

"Siv" <[e-mail]

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