Answered IS Operator does not accept operands of Type Boolean

[thebatfink]

Hi, I have run across a problem and I am struggling to understand how to fix it (the IDE doesn't seem able to auto correct the problem).

I have a little bit of code that uses the Microsoft.Office.Interop, however I suspect this is irrelevant to the problem and it is a general coding issue, so hopefully I have hit the correct sub forum

So.. as the title explains, I have hit this error. I had a working program, no problems. However today I decided to enable Option Strict and Option Explicit and set about making all the nessicary changes to my now apparent badly written code!

If PlanWorkSheet.Range("A1").HasFormula = False Then

So the IDE now complains that Option Strict disallows operands of type Object for operator '=' and now requires the use of the 'Is' operator. This has been fine until I now hit this statement trying to test for False, if I change to 'Is False', this then throws the errors as per the title.

So my question is, when something is Returning True or False as in the case of .HasFormula (MSDN Link), how do I test in my If blocks with Option Strict On?!

Any help would be much appreciated! Thanks!

EDIT: The only thing I can think of is to try and convert the .HasFormula to an Integer and then test if its 0?

If CInt(PlanWorkSheet.Range("A1").HasFormula) = 0 Then

but is this correct? Am I totally missing something?

 

[JohnH]

HasFormula property is defined as type Object, and not Boolean - while the returned value obviously is a Boolean. Use CType/DirectCast to cast the value to Boolean, then you can compare the values with = operator.
Is operator is used for object reference equality by the way, and = operator for value equality. Booleans are value types and they are compared with = operator.
It is not uncommon that the interop libraries has poor type support, requiring much type casting.

 

[thebatfink]

HasFormula property is defined as type Object, and not Boolean - while the returned value obviously is a Boolean. Use CType/DirectCast to cast the value to Boolean, then you can compare the values with = operator.
Is operator is used for object reference equality by the way, and = operator for value equality. Booleans are value types and they are compared with = operator.
It is not uncommon that the interop libraries has poor type support, requiring much type casting.

Hi, thanks for the explaination! So I would presume

If CType(PlanWorkSheet.Range("A1").HasFormula,Boolean) = False Then

is the way forward. Thanks for the help

Pressing on with the error list I have also come across another problem related to enabling Option Explicit and Option Strict. I have this piece of code which basically opens a Workbook and then deletes a module in the VBE project and imports a replacement module (I do this for mass updating many workbooks). It then saves and closes the workbook. I have ommitted some of the file path handling for the FileList array to shorten it.

Option Strict On
Option Explicit On
Imports Microsoft.Office.Interop

Module Module1

Public FileList() As String

Sub MySub()
Dim ExcelApp As Excel.Application = New Excel.Application
Dim PlanWorkBook As Excel.Workbook
Dim PlanWorkSheet As Excel.Worksheet
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim InitialPath As String
            
PlanFileName = FileList(i)
PlanWorkBook = ExcelApp.Workbooks.Open(PlanFileName)

VBProj = PlanWorkBook.VBProject
VBComp = VBProj.VBComponents.Item("Module3")
VBProj.VBComponents.Remove(VBComp)

InitialPath = "C:\Spreadsheets\"

PlanWorkBook.VBProject.VBComponents.Import(InitialPath & "Module3.bas")
 
PlanWorkBook.Save()
PlanWorkBook.Close()
PlanWorkBook = Nothing

Now the code works fine (with Strict and Explicit Off) and compiles and runs fine with the expected results. However when I have them set to On, I get some errors.

It doesn't like these line..

VBProj = CType(PlanWorkBook.VBProject, VBIDE.VBProject)
VBComp = CType(VBProj.VBComponents("Module1"), VBIDE.VBComponent)

and wants to convert them to type VBIDE.VBComponet, which I let it do as shown above, but it also doesn't like "Module1". Its complaining that I am trying to convert from String to Integer, but that can't be because the code normally runs fine and even if it was just peforming the conversion for me when Strict was set to Off, Module1 can't be converted to an integer in any case?

So I'm bascially stuck I'm guessing it must of been doing something for me automatically before which now it won't do with Strict, but I'm totally lost.

Don't suppose you might be able to give me a hint to the solution of this one too could you please?

Thanks!

EDIT: Actually, it would seem I was missing '.item' in the line of code that was causing the error (even though I managed to paste it in fine above :/)

 

[JohnH]

If CType(PlanWorkSheet.Range("A1").HasFormula,Boolean) = False Then

Instead of writing If expression=False Then you can write If Not expression Then, like this:

If Not CType(PlanWorkSheet.Range("A1").HasFormula, Boolean) Then

Similar for True expressions I would write the code If expression Then instead of If expression=True Then for better readability.
As such you could also use a variable for the expression so that it is easier to read later:

Dim hasFormula = CType(PlanWorkSheet.Range("A1").HasFormula, Boolean)
If Not hasFormula Then
    '...
End If

It doesn't like these line..

Code:
VBProj = CType(PlanWorkBook.VBProject, VBIDE.VBProject)

What does your compiler says the type is when you hover over VBProject property? (PlanWorkBook.VBProject) I would have though it said Microsoft.Vbe.Interop.VBProject.

VBComp = CType(VBProj.VBComponents("Module1"), VBIDE.VBComponent)

change to VBProj.VBComponents.Item("Module1")
Similarly I would have thought the item object was type Microsoft.Vbe.Interop.VBComponent.

 

[thebatfink]

Thanks for the lesson, I will make those modifications and try get into the habbit Using a variable is cool, never thought of that, I have that line for many different cell references though so the number of variables would become silly quickly. I think once I have it all working again OK I will remove it from the main sub and create a function with it.

What does your compiler says the type is when you hover over VBProject property? (PlanWorkBook.VBProject) I would have though it said Microsoft.Vbe.Interop.VBProject.

change to VBProj.VBComponents.Item("Module1")
Similarly I would have thought the item object was type Microsoft.Vbe.Interop.VBComponent.

I spotted the stupid missing '.Item' whilst scratching my head, must of accidently removed it somehow. But regarding the property, hovering does indeed tell me 'Microsoft.Vbe.Interop.VBProject()'.. why do you ask that?

Thanks again for the help much appreciated.

 

[JohnH]

But regarding the property, hovering does indeed tell me 'Microsoft.Vbe.Interop.VBProject()'.. why do you ask that?

Because you wanted to cast it to type VBIDE.VBProject.

 

[thebatfink]

Ahh ok I understand. The thing is, the error was obviously highlighted in the Visual Studio code window and when I view the error correction options, it offered to correct it for me. That auto-correction wanted to cast to VBIDE.VBProject so I let it. What do you think I should do?

I am curious about all this Casting that is required though since enabling the Strict option. For instance, at the beginning of my Sub I dimension the variables,

PlanWorkSheet as Excel.Worksheet
PlanWorkBook as Excel.Workbook

But when I come to assign a value to it later in the code,

PlanWorkSheet = PlanWorkBook.Sheets(WorkSheetIndex)

it wants me to Cast it to Excel.Worksheet?

PlanWorkSheet = CType(PlanWorkBook.Sheets(WorkSheetIndex), Excel.Worksheet)

Is this because PlanWorkBook.Sheets() is defined as Excel.Workbook and not Excel.Worksheet? Does it not understand what .Sheets() is? I have no problem doing this, I just don't understand why I'm casting everything left right and centre? Is it because of the lacking Interop libaries type support again or am I not defining my variables correctly?

Thanks, learning good stuff

 

[JohnH]

Ahh ok I understand. The thing is, the error was obviously highlighted in the Visual Studio code window and when I view the error correction options, it offered to correct it for me. That auto-correction wanted to cast to VBIDE.VBProject so I let it. What do you think I should do?

That was because you had already defined the variable like this:

Dim VBProj As VBIDE.VBProject

Compiler then detected you wanted to assign a Interop.VBProject object to a VBIDE.VBProject variable and suggested to CType that for you. It's all your fault for declaring the variable before it is used This is what you should have done:

Dim VBProj = PlanWorkBook.VBProject
Dim VBComp = VBProj.VBComponents.Item("Module1")

Is this because PlanWorkBook.Sheets() is defined as Excel.Workbook and not Excel.Worksheet?

You're accessing the Item property of the Sheets object, and that property is untyped (Object). Do this:

Dim PlanWorkSheet = CType(PlanWorkBook.Sheets(WorkSheetIndex), Excel.Worksheet)

 

[thebatfink]

That was because you had already defined the variable like this:

Compiler then detected you wanted to assign a Interop.VBProject object to a VBIDE.VBProject variable and suggested to CType that for you. It's all your fault for declaring the variable before it is used This is what you should have done:

Dim VBProj = PlanWorkBook.VBProject
Dim VBComp = VBProj.VBComponents.Item("Module1")

You're accessing the Item property of the Sheets object, and that property is untyped (Object). Do this:

Dim PlanWorkSheet = CType(PlanWorkBook.Sheets(WorkSheetIndex), Excel.Worksheet)

Its starting to make sense now I'm actually glad I started forcing myself to go Strict as obviously I'm learning some really bad habbits.

I thought it was good practise to declare variables at the start of the sub? I've always done that. Is that not correct programming standard! More habbits to get out of!

 

[JohnH]

I thought it was good practise to declare variables at the start of the sub? I've always done that. Is that not correct programming standard! More habbits to get out of!

No, that is probably something that comes from a prehistoric VB version where one had to do that. It clutters up the code, you get two lines of code where only one is needed and you separate those two lines so that it is hard to follow. By doing that you can't take advantage of type inference, which means that compiler figures out the type for you from the assigned expression. It is also easier to make subtle mistakes and not assign it a value/object, where you end up with null-reference exceptions at runtime. Good OOP practise is to use closest scope, for example it makes no sense to declare a variable outside the scope where it is used, that also goes for block scope like If-blocks and loop blocks. A common misconception in this context is that declaring a variable within a loop is wasting more memory, that is not the case and compiler only allocates its storage space once.

 

[thebatfink]

No, that is probably something that comes from a prehistoric VB version where one had to do that. It clutters up the code, you get two lines of code where only one is needed and you separate those two lines so that it is hard to follow. By doing that you can't take advantage of type inference, which means that compiler figures out the type for you from the assigned expression. It is also easier to make subtle mistakes and not assign it a value/object, where you end up with null-reference exceptions at runtime. Good OOP practise is to use closest scope, for example it makes no sense to declare a variable outside the scope where it is used, that also goes for block scope like If-blocks and loop blocks. A common misconception in this context is that declaring a variable within a loop is wasting more memory, that is not the case and compiler only allocates its storage space once.

Yes, I cut my teeth with VBA so I must of gotten that from there.

I have now edited my code and dimensioned all the variables at the point of use and now they have the type infered, I no longer have the ide going nuts about casting types Whilst I have been going through and re-writing this little spreadsheet modifying application, I've started actually adding some error handling (read that as encasing everything in Try Catch statements - but only really in this case because for almost any error I want to end the application so I'm not actually handling any exceptions in reality and bogging down performance) in which was completely no existant also. In a few instances I noticed that variables were first used inside these Try / Catch wrappers and I noticed they have block level variable scope also. I'll be honest, I never even realised there was variable scope inside these types of blocks!! I assume in these cases when the variable is needed else where in the sub, then it is fine / good practise to dimension them just prior to begining the block?

I really feel like I've learnt something significant that will help me not only code cleaner, but better understand why I am supposed to be doing the things I am and for that I really thank you

 

[JohnH]

I assume in these cases when the variable is needed else where in the sub, then it is fine / good practise to dimension them just prior to begining the block?

If the scope is the method then that is where you declare them, just don't declare until you need to assign if possible.

read that as encasing everything in Try Catch statements

You really shouldn't use Try-Catch unless you're prepared to handle the exception, meaning the error condition can be 'treated' and execution can continue. If you want to log and/or give a nicer feedback about exceptions to user before closing the app do that using the applications UnhandledException event handler.