Array is not read through [debug]

Question

2.50/5 (2 votes)

See more:

The following code snippet was taken from the Visual Studio 2010 (trial) walk through.
It is noted that:
1. The excel sheet created does not include all the values declared within [values]
2. Although the first time the application runs a file named SampleWorkBooks.xls
is created, the second time round, the application prompts me for a default location for Book.xlsx, which leads me to think this is some default behavior which
the application has not overridden.

VB

Imports Excel = Microsoft.Office.Interop.Excel
Module Module1
    Sub Main()
        Dim values = {4, 6, 18, 2, 1, 76, 0, 3, 11}
        CreateWorkbook(values, &quot;C:\SampleFolder\SampleWorkbook.xls)
    End Sub
    Sub CreateWorkbook(ByVal values As Integer(), ByVal filePath As String)
        Dim excelApp As Excel.Application = Nothing
        Dim wkbk As Excel.Workbook
        Dim sheet As Excel.Worksheet
        Try
            Start Excel and create a workbook and worksheet.
            excelApp = New Excel.Application
            wkbk = excelApp.Workbooks.Add()
            sheet = CType(wkbk.Sheets.Add(), Excel.Worksheet)
            sheet.Name = &quot;Sample Worksheet&quot;
            &#39; Write a column of values.
            For i = 1 To values.Length
                sheet.Cells(Row,Column)
                sheet.Cells(i, 1) = values(i) 
               //question: shouldn't this loop iterate through the values declared
               //and write the list of values in row (i,1)?
            Next
            &#39; Suppress any alerts and save the file. Create the directory
            &#39; if it does not exist. Overwrite the file if it exists.
            excelApp.DisplayAlerts = False
            Dim folderPath = My.Computer.FileSystem.GetParentPath(filePath)
            If Not My.Computer.FileSystem.DirectoryExists(folderPath) Then
                My.Computer.FileSystem.CreateDirectory(folderPath)
            End If
            wkbk.SaveAs(filePath)
        Catch
        Finally
            sheet = Nothing
            wkbk = Nothing
            &#39; Close Excel.
            excelApp.Quit()
            excelApp = Nothing
        End Try
    End Sub
End Module

Article sourced from: http://msdn.microsoft.com/en-us/library/ee317478.aspx

Microsoft Visual Studio 2010
Version 10.0.30319.1 RTMRel
Microsoft .NET Framework
Version 4.0.30319 RTMRel
Installed Version: Professional
Microsoft Office Developer Tools   01018-169-2660007-70603
Microsoft Office Developer Tools
Microsoft Visual Basic 2010   01018-169-2660007-70603
Microsoft Visual Basic 2010
Microsoft Visual C# 2010   01018-169-2660007-70603
Microsoft Visual C# 2010
Microsoft Visual C++ 2010   01018-169-2660007-70603
Microsoft Visual C++ 2010
Microsoft Visual F# 2010   01018-169-2660007-70603
Microsoft Visual F# 2010
Microsoft Visual Studio 2010 Team Explorer   01018-169-2660007-70603
Microsoft Visual Studio 2010 Team Explorer
Microsoft Visual Web Developer 2010   01018-169-2660007-70603
Microsoft Visual Web Developer 2010
Crystal Reports Templates for Microsoft Visual Studio 2010
Crystal Reports Templates for Microsoft Visual Studio 2010
Microsoft Visual Studio 2010 SharePoint Developer Tools   10.0.30319
Microsoft Visual Studio 2010 SharePoint Developer Tools

Posted 20-Mar-11 5:53am

jon-80

Updated 20-Mar-11 6:07am

Sandeep Mewara

v2

Add a Solution

Comments

OriginalGriff 20-Mar-11 12:10pm

And is there any particular reason you are telling us this?
Did you have a question on it?

jon-80 20-Mar-11 12:22pm

Yes, because I am thinking that it might be a bug.

Richard MacCutchan 20-Mar-11 16:28pm

Well I think that you should tell Microsoft.

jon-80 20-Mar-11 17:58pm

ok

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2011-03-26T09:41:00

I see 3 reasons to bugs:
1)

Dim sheet As Excel.Worksheet

Never use variable named "sheet", becouse it is Excel name.
Excel have Worksheets and Sheets collections. Read more about differences on msdn website.

Dim oSheet As Excel.Worksheet

2)

VB

oSheet.Cells(Row,Column)

I have'nt see declarations for: "Row" and "Column"
You should always use Option Explicit keyword to enforce declaration of variables.

3)

VB

For i=1 To values.Length
    oSheet.Cells(i, 1) = values(i)
Next i

When you use arrays in For ... To ... loop, the default lower bound is 0 (zero), not 1. Use Option Base 1 keyword to enumerate arrays from 1.

And one advise:
Use Range("A" & i) object. This is better and quickest then: sSheet.Cells(1,i).

oRng = oSheet.Range("A" & i) 'direct access
oRng.Value = "Example 1"

To change column in iteration you can use Offset object. For example (using sheet object):

With oSheet
    .Range("A1").Offset(RowOffset:=0,ColumnOffset:=i)
    //other instructions
End With