Excel VBA - Working with arrays

Posted on 2003-02-28
Medium Priority
Last Modified: 2011-09-20

I have used the code below to open a file, read the first line of a file into an array called headings(1)
The headings wil be in the format first name, last name, stud num, test1, test 2, test3, (Right up to test n),testn.  Each heading is separated by a comma in the array.
How would I go about extracting each heading into a separate variable or array, bearing in mind that there could be five headings or 150? I know that I need to look for each comma and when it finds the comma store each heading separately.

Sub ReadFirstLineFile()
Dim Headings(1) As String

Open "A:\Implementation\inputfile.csv" For Input As #1
Line Input #1, Headings(1)
MsgBox "First Line " & "in input file reads:" & Chr(13) & Headings(1)
Close #1

End Sub

The reason I need to do this is that the user needs to choose which headings to include in the analysis, so i need to extract each heading name and present it to the user to choose yes or no.  Once I separate each heading name, would it be possible to pass this to a user form which could present it as a list of choices ?

Many thanks in advance

natalie vance

Question by:NatalieVance
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 8045820
Hi Natalie!

Well i think what you could do is:

Add a ListBox control to your form. Set the "Style" property of the ListBox to "1" (checkboxes).

' Assuming you have a form called frmMy and
' a listbox called lsbMy, and that your CSV-File
' values are limited by a semicolon: ";"

Sub ReadFirstLineFile()

 Dim strHeadings, strContent() As String
 Dim iCtr as Integer

 Open "A:\Implementation\inputfile.csv" For Input As #1
  Line Input #1, strHeadings
 Close #1

 strContent = Split(strHeadings, ";")
 For iCtr = 0 to ubound(strContent)
  frmMy.lsbMy.AddItem strContent(iCtr)

End Sub

I did not test it, so i do not know if it works. But it should. If you have further questions, feel free to ask.

Accepted Solution

spiritwithin earned 80 total points
ID: 8045839
Btw.: please note that the function will produce a runtime error if the heading string does not contain what you assume.

In other words:

If strContent is not a correctly filled array after calling Split(), then UBound(strContent) will produce a runtime error.

If you want to avoid this go like that:

On Error Resume Next
For iCtr = 0 to ubound(strContent)
 frmMy.lsbMy.AddItem strContent(iCtr)
On Error Goto 0

This disables runtime error checking for the code between the two statements.


Author Comment

ID: 8060531
Many thanks for your help and suggestions.


Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Introduction to Processes

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question