Help with Index was outside of the bounds of the array error

Hello Everyone,

I have this group to be very knowledgeable and helpful and I’m hoping I can tap into some of the knowledge to get help with this error?

I have a button that I am using to open an excel workbook and populate 4 data elements in to columns A, B C and D. For the most part it seems to work but I am getting and error saying Index was outside of the bounds of the array error.

My code looks like this…

Dim xlApp As Object = CreateObject(“Excel.Application”)

 If xlApp Is Nothing Then MsgBox("It appears that Excel is not available at this time." & vbCrLf & _ "You may need to close all instances of Excel and try again." & vbCrLf & _ "If that doesn't work, you may need to reboot your computer." & vbCrLf & _ "If that doesn't work, there may be a problem with Excel") End If Dim xlWorkBook As Object Dim xlWorkSheet As Object Dim _USERID_HDR As Integer = 1 Dim _ASSOCROLE_HDR As Integer = 2 Dim _EVENT_HDR As Integer = 3 Dim _DT_HDR As Integer = 4 ' Start Data at _Row Dim _Row As Integer = 4 Dim _Delimiter() As String = {","} xlApp.DisplayAlerts = False xlApp.Visible = True xlApp.UserControl = True xlWorkBook = xlApp.Workbooks.Add() xlWorkSheet = xlWorkBook.ActiveSheet() ' Add table headers going cell by cell. xlWorkSheet.Cells((_Row - 3), 1).Value = String.Format("UserTouchesFX{0} by MortgageTech{1}", Chr(0153), Chr(0169)) xlWorkSheet.Cells((_Row - 2), 1).Value = DateTime.Now.ToString("MMMM dd, yyyy @ hh:mm tt") xlWorkSheet.Cells((_Row - 1), _USERID_HDR).Value = "UserID" xlWorkSheet.Cells((_Row - 1), _ASSOCROLE_HDR).Value = "Role" xlWorkSheet.Cells((_Row - 1), _EVENT_HDR).Value = "Event" xlWorkSheet.Cells((_Row - 1), _DT_HDR).Value = "DateTime" ' Assign Log Items to specific Excel cells Try For Each _LogEntry As String in (FX_AU_Sessions_Log.Text.Split(New Char() { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries)) Dim _ANY_DT As DateTime = Nothing 

‘ Dim _LogParts() As String = _LogEntry.Split( _Delimiter, StringSplitOptions.None) Dim _LogParts() As String = _LogEntry.Split( _Delimiter, StringSplitOptions.None) DateTime.TryParse(_LogParts(3), _ANY_DT)

 xlWorkSheet.Cells(_Row, _USERID_HDR) = _LogParts(0) 'User ID xlWorkSheet.Cells(_Row, _ASSOCROLE_HDR) = _LogParts(1) 'User Role xlWorkSheet.Cells(_Row, _EVENT_HDR) = _LogParts(2) 'Event xlWorkSheet.Cells(_Row, _DT_HDR) = _ANY_DT 'Time Stamp _Row = _Row + 1 

‘ MsgBox ( _ ‘ “UserID = ” & _LogParts(0) & vbCrLf & _ ‘ “Role = ” & _LogParts(1) & vbCrLf & _ ‘ “Event = ” & _LogParts(2) & vbCrLf & _ ‘ “TimeStamp = ” & _ANY_DT & vbCrLf )

 Next _LogEntry ' Format Settings: ' ColorIndex values: 0 Auto, 1 Black, 2 White, 3 Red, 5 Blue, 6 Yellow, '10 Green, 40 Tan, 34 Light Turquoise, 35 Light Green, 36 Light Yellow, '37 Pale Blue ' Set first row to Bold Font Size 16 - Top Company Name Row | ' Vertical Alignment: Top = -4160, Center = -4108, Bottom = -4107 ' Horizont Alignment: Left = -4131, Center = -4108, Right = -4152 With xlWorkSheet.Range("A1", "D1") .Interior.ColorIndex = 35 .Merge("A1", "D1") .Font.Size = 16 .Font.Bold = True .VerticalAlignment = -4107 .HorizontalAlignment = -4108 End With ' Set second row to Regular Font Size 12 - Date Now Row With xlWorkSheet.Range("A2", "D2") .Interior.ColorIndex = 34 .Merge("A2", "D2") .Font.Size = 12 .Font.Bold = False .VerticalAlignment = -4107 .HorizontalAlignment = -4108 End With ' Set third row to Bold Font Size 12 - Column Header Row With xlWorkSheet.Range("A3", "D3") .Interior.ColorIndex = 15 .Font.Size = 11 .Font.Bold = True .VerticalAlignment = -4107 .HorizontalAlignment = -4108 ' .EntireColumn.AutoFit End With ' Set column widths With xlWorkSheet .Columns("A").ColumnWidth = 40 .Columns("B").ColumnWidth = 20 .Columns("C").ColumnWidth = 30 .Columns("D").ColumnWidth = 40 .Rows.WrapText = False End With ' Set column formatting With xlWorkSheet .Columns("A").EntireColumn.HorizontalAlignment = -4131 ' Left .Columns("B").EntireColumn.HorizontalAlignment = -4108 ' Center .Columns("C").EntireColumn.HorizontalAlignment = -4108 ' Center .Columns("D").EntireColumn.HorizontalAlignment = -4152 ' Right .Columns("D").NumberFormat = "yyyy-mm-dd hh:mm AM/PM" End With Catch Ex As Exception Macro.Alert(String.Format("There is problem with xlWorkBook: {0}", Ex.Message )) End Try 

submitted by /u/robert-springer
[link] [comments]

Leave a Reply