Questions on this blog:

Add-in Express™ Support Service
That's what is more important than anything else

Questions on this blog:
How to find the last used cell (row or column) in Excel 
Posts 1 - 10 of 14
First | Prev. | 1 2 | Next | Last 
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hi,

I am studying this blog:
How to find the last used cell (row or column) in Excel
https://www.add-in-express.com/creating-addins-blog/2013/12/06/find-last-used-cell-excel/


I am trying to set Option Strict On for AddinModule.vb
On debugging, it throws many complile errors. Some I sorted out, others I could not.


Like this sub: 
Private Sub MoveToLastCell(cellType As Excel.XlCellType, sheet As Excel.Worksheet, rangeName As String)
        Dim cells As Object = Nothing
        Dim lastCell As Excel.Range = Nothing
        Try
            cells = sheet.Range(rangeName).SpecialCells(cellType).Areas

            lastCell = cells(cells.Count)

            lastCell.Select()
        Catch
        End Try

        If lastCell IsNot Nothing Then Marshal.ReleaseComObject(lastCell)
        If cells IsNot Nothing Then Marshal.ReleaseComObject(cells)
    End Sub


The error message is:
Option Strict On disallows late binding

The error points to this line: 
lastCell = cells(cells.Count)


Two correction Options are suggested:
--> Generate method stub for 'cells' in 'LastCell.AddinModule'
--> Generate property stub for 'cells' in 'LastCell.AddinModule'

When I click the 1st suggestion, it creates this function at the bottom of the module: 

Private Function cells(ByVal p1 As Object) As Excel.Range
        Throw New NotImplementedException
 End Function

and there is this Error message:
'cells' is already declared as Private Property cells(p1 As Object) As Microsoft.Office.Interop.Excel.Range in this class.


Note: There are many Private Subs in the module where the same variable'cells' is dimmed.
I will have to correct all those subs to make them compatible with Option Strict On.

red

Thanks
Leon
Posted 19 Feb, 2019 09:53:26 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Leon,

Leon Lai Kan writes:
The error points to this line:


That occurs because cells is defined As Object.

Invoking properties/methods on an object that doesn't provide them directly is called late binding. This is opposite to early binding: you get properties/methods on an object that does provide them directly: say, you print "lastCell", then dot and the intellisense feature of the IDE shows you the properties/methods available on lastCell.

In the Excel object model (Alt+F11 to open the VBA IDE, and F2 to open the object browser), Range.Areas returns Excel.Areas; see also https://docs.microsoft.com/en-us/office/vba/api/excel.range.areas. You need to modify the declaration of cells to Excel.Areas.


Andrei Smolin
Add-in Express Team Leader
Posted 20 Feb, 2019 01:44:05 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hi, Andrei

As per your advice, I declared 'cells' as follows: 
Dim cells As Excel.Areas = Nothing

instead of: 
Dim cells As Object = Nothing

and it works. Great!
------

There is another Sub which is giving me similar worries: 

Private Sub LastEmptyButFormattedCell()
   ExcelApp.FindFormat.Font.FontStyle = "Bold"

  'Dim sheet As Excel.Worksheet = ExcelApp.ActiveSheet   'Original line
   Dim sheet As Excel.Worksheet = CType(ExcelApp.ActiveSheet, Excel.Worksheet)   'amended

   Dim Cells As Excel.Range = Nothing   ' LINE ADDED BY LEON - No DIM in original code.
   Dim found As Object = Nothing        ' ????? How to declare?
   Dim loopCount As Integer = 0
   Dim firstCellAddress As String = ""
   Dim exitLoop As Boolean = False
   Dim emptyFound As Boolean = False

        With sheet.Range("Sample")
            'Select the range...
            .Select()
            'Ensure we are in the top-right cell
            .Cells(1, 1).Activate()

            Do
                'Do we need to exit the DO...LOOP?
                If exitLoop Then Exit Do

                'Peform a search
                found = .Find("", ExcelApp.ActiveCell, Excel.XlFindLookIn.xlValues, _
                    Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, _
                    Excel.XlSearchDirection.xlPrevious, MatchCase:=False, _
                    SearchFormat:=True)

                'Check that we found a cell and activate it
                If Not found Is Nothing Then found.Activate()



Here, I get the error message 'Option Strict On disallows late binding' with the following lines: 

.Cells(1, 1).Activate()

 If Not found Is Nothing Then found.Activate()


I tried to declare 'Cells' as: 
Dim Cells As Excel.Areas = Nothing

and also as: 
Dim Cells As Excel.Range = Nothing

Both don't work.

red

----

red 
Dim found As Object = Nothing



Thanks
Leon
Posted 20 Feb, 2019 02:34:46 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Leon Lai Kan writes:
Dim Cells As Excel.Range = Nothing ' LINE ADDED BY LEON - No DIM in original code.


I assume this line isn't required. To check this, right-click Cells and choose Find all references. I expect, this will show that that variable is only used in that declaration.

Leon Lai Kan writes:
Dim found As Object = Nothing' ????? How to declare?


In the object browser, find the Find method, press F1 and check what that method returns. I've used this sequence to find the following citation at https://docs.microsoft.com/en-us/office/vba/api/excel.range.find:

Return value
A Range object that represents the first cell where that information is found.

In other words, you should declare found as Excel.Range.

Leon Lai Kan writes:
I get the error message 'Option Strict On disallows late binding' with the following lines:
.Cells(1, 1).Activate()


Here your actual call is this: sheet.Range("Sample").Cells(1, 1).Activate()

Let's decipher it.
1. sheet.Range("sample) is a call of this property: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.range; it returns an object of the Excel.Range type.
2. The call [the Excel.Range type].Cells(1,1) should be split as follows:
2.1. [Excel.Range returned by sheet.Range("sample)].Cells - https://docs.microsoft.com/en-us/office/vba/api/excel.range.cells; it returns Excel.Range.
2.2. [Excel.Range returned by Cells].Item(1, 1) - https://docs.microsoft.com/en-us/office/vba/api/excel.range.item; it returns an Excel.Range but the return value is typed as Object; this is where you need to add a cast.
3. Finally, you call [cast the value returned by Item(1, 1) to Excel.Range].Activate().


Andrei Smolin
Add-in Express Team Leader
Posted 20 Feb, 2019 05:12:45 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Andrei,

Thanks a lot for all your insightful explanations.

My! How complicated can COM programming be!

With your help, I have been able to modify practically all lines so that they don't throw a compile error when Option Strict is On.

There is only one line left to amend.
This: 
.Cells(1, 1).Activate()



I am not sure I understand your explanations.
red
When I see it work, then your explanations will be clearer. 

Private Sub LastEmptyButFormattedCell()
        ExcelApp.FindFormat.Font.FontStyle = "Bold"

        'Dim sheet As Excel.Worksheet = ExcelApp.ActiveSheet                       'Original
        Dim sheet As Excel.Worksheet = CType(ExcelApp.ActiveSheet, Excel.Worksheet) 'Option Strict On

        'Dim found As Object = Nothing             'original
        Dim found As Excel.Range = Nothing         'Option Strict On

        Dim loopCount As Integer = 0
        Dim firstCellAddress As String = ""
        Dim exitLoop As Boolean = False
        Dim emptyFound As Boolean = False

        With sheet.Range("Sample")
            ''Select the range...
            .Select()
            'Ensure we are in the top-right cell
            '.Cells(1, 1).Activate()                           'Original
              ????????????????                                 'Amended


            Do
                'Do we need to exit the DO...LOOP?
                If exitLoop Then Exit Do




Thanks
Leon
Posted 20 Feb, 2019 10:56:36 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Leon,

Leon Lai Kan writes:
Can you modify the following code for me?


Try this code line:

CType(.Cells(1, 1)).Activate()


Andrei Smolin
Add-in Express Team Leader
Posted 21 Feb, 2019 02:02:40 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Andrei,

Try this code line:
CType(.Cells(1, 1)).Activate()


I get this compile error:
Syntax error in cast operator;
two arguments separated by comma are required


Leon
Posted 21 Feb, 2019 06:31:39 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Ah!!!

Of course.

CType(.Cells(1, 1), Excel.Range).Activate()


Andrei Smolin
Add-in Express Team Leader
Posted 21 Feb, 2019 06:36:07 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Andrei,

Thanks a lot.
The code works!

Leon
Posted 21 Feb, 2019 08:07:06 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Welcome!


Andrei Smolin
Add-in Express Team Leader
Posted 21 Feb, 2019 09:49:19 Top

Posts 1 - 10 of 14
First | Prev. | 1 2 | Next | Last