none
Error: TypeInitiliazationException was unhandled by user code RRS feed

  • Question

  • ‘ThisWorkbook

    Public Class ThisWorkbook

     

        Private Sub ThisWorkbook_Startup(ByVal sender As Object, _

         ByVal e As System.EventArgs) Handles Me.Startup

            CType(Me.Sheets(1), Excel.Worksheet).Name = "BB"

            CType(Me.Sheets(2), Excel.Worksheet).Name = "CC"

            CType(Me.Sheets(3), Excel.Worksheet).Name = "AA"

            If MsgBox("Sort the sheets?", MsgBoxStyle.YesNo) = _

                    MsgBoxResult.Yes Then MoveSheets()

     

    End Sub

     

    ‘Module1.vb

    Module myModule

     

        Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook, Excel.Workbook)

     

        Sub MoveSheets()

            Dim AP As Excel.Application = CType(thisWB.Application,  _

                    Excel.Application)

            AP.ScreenUpdating = False

     

            Dim I, j As Integer, n As Integer = thisWB.Worksheets.Count - 1

            Dim arr(n) As String, temp As String

            Dim AW As Excel.Worksheet = CType(thisWB.ActiveSheet,  _

                    Excel.Worksheet)

            For I = 0 To n

                Dim WS As Excel.Worksheet = _

                        CType(thisWB.Worksheets(I + 1), Excel.Worksheet)

                arr(i) = LCase(WS.Name)

            Next

            For I = 0 To UBound(arr) - 1

                For j = I + 1 To arr.GetUpperBound(0)

                    If arr(I) > arr(j) Then temp = arr(I) : arr(I) = arr(j) : arr(j) = temp

                Next

            Next

            For I = 0 To Ubound(arr)

                Dim WS1 As Excel.Worksheet = _

                        CType(thisWB.Worksheets(I + 1), Excel.Worksheet)

                Dim WS2 As Excel.Worksheet = _

                        CType(thisWB.Worksheets(arr(i)), Excel.Worksheet)

                WS2.Move(WS1)

            Next

            AW.Activate()

            AP.ScreenUpdating = True

        End Sub

     

    End Module

     

     

     

    I’m getting a green squiggley line under this:

    Globals.ThisWorkbook

     

    The warning message is this:

    Runtime errors might occur when converting 'ExcelWorkbook9.ThisWorkbook' to 'Microsoft.Office.Interop.Excel.Workbook'.

     

    When I hit F5, the code fires and the sheets are named, but when I click ‘Yes’ to sort the sheets, I get an error message here:

    MoveSheets()

     

    TypeInitiliazationException was unhandled by user code

     

    I did some Google searching on this, and tried a few things, but I still can’t get this working.  I thought this was the key:

    http://www.mrexcel.com/forum/showthread.php?t=341867

     

    I tried to implement that concept; nothing I tried worked.  How can I get this to run???

     

    Tuesday, November 22, 2011 8:53 AM

Answers

  • Hi ryguy72,

     

    Thanks for posting in the MSDN Forum.

     

    This line is incorrect “Dim thisWB as Excel.Workbook = CType(Globals.ThisWorkbook,Excel.Workbook)” please use this sentence “Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook.Application.ActiveWorkbook, Excel.Workbook)”. All will work fine.

     

    I hope what I said can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by ryguy72 Thursday, November 24, 2011 6:49 AM
    Thursday, November 24, 2011 3:01 AM
    Moderator

All replies

  • Hi ryguy72,

     

    Thanks for posting in the MSDN Forum.

     

    This line is incorrect “Dim thisWB as Excel.Workbook = CType(Globals.ThisWorkbook,Excel.Workbook)” please use this sentence “Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook.Application.ActiveWorkbook, Excel.Workbook)”. All will work fine.

     

    I hope what I said can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by ryguy72 Thursday, November 24, 2011 6:49 AM
    Thursday, November 24, 2011 3:01 AM
    Moderator
  • Perfect!  Thanks so much!!

    BTW, what was the root cause of the problem?  I think that sample code was developed in VS 2008; I am using VS 2010.  I guess in VS 2010, you need to fully qualify the object.  Is that right?

    Thursday, November 24, 2011 6:49 AM
  • Hi ryguy72,

     

    In my opinion that Globals.ThisWorkbook is different than Excel.Workbook. The Globals.ThisWookbook is the instance of the document-level application, it is the parent instance for your workbook. So you can’t cast it’s type to Excel.Workbook. I’m not sure whether Visual Studio 2008 can pass Dim thisWB as Excel.Workbook = CType(Globals.ThisWorkbook,Excel.Workbook) before compile. In theory, I guess this sentence will not pass check error process before compile..

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Friday, November 25, 2011 5:50 AM
    Moderator