How to make variable inside a For Loop global or accessible within sub or another sub/form?

Question

This is a very basic question and i have googled how to "pass variables from for loop, for each" etc... and everything just seemed ambiguous or inadequate. Maybe because its not even possible?

I have an excel file that has a list of file numbers that i will work with later on in my code to search for PDF's in a directory. My code works fine but my code is filled with loops, nested loops and everything always has to be within these loops because thats where the variables are declared. Example in this code below:

Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Module Module1
    Dim aCell As Object = Nothing
    Dim eachcell As New List(Of String)

    Sub Main()

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim range As Excel.Range
        Dim aCpage = "unknown"
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("C:\Users\XBorja.RESURGENCE\Desktop\xavier.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        range = xlWorkSheet.UsedRange.SpecialCells(XlCellType.xlCellTypeConstants)
        Dim currentcell = Nothing


        For Each i In range.Value



            eachcell.Add(i)
        Next

        For Each aCell In eachcell
            currentcell = aCell.ToString
            Console.WriteLine(currentcell)
        Next

    End Sub

That works perfect, on the console it shows a list of all file numbers i had written in my excel sheet:

ZTEST01.SMITH
ZTEST14.SAMPLE05
ZTEST02.RESTAURANT
ZTEST12.SAMPLE03
ZTEST03.MCDONALD
ZTEST04.DOE
ZTEST10.SAMPLE01
ZTEST13.SAMPLE04
ZTEST11.SAMPLE02
Press any key To Continue . . 

but if i use this code:

    For Each i In range.Value

        eachcell.Add(i)
    Next

    For Each aCell In eachcell
        currentcell = aCell.ToString

    Next
    Console.WriteLine(currentcell)
End Sub

This only tells me the last value of

ZTEST.11.SAMPLE02

I know that the logic is that since the console write is within the for loop its displaying each value in excel and appending those values to the console until the loop is finished.

My question is how would i be able to call that entire list/array outside the for loop?

I know with += you can add integers to get a sum in a for loop and i tried that with this code and it just gave me back all the file numbers concatenated.

Could this for loop be turned into another sub so i can call this variable "currentcell" outside of the loop? Or could it be turned into a function So that my entire sub could always use that variable?

Answer 1

Every iteration of the loop has it's own scope, anything local to that scope is lost after each iteration.

if your looking for a specific element you need to save it too its own value, the code you have overwrites whatever you found in the last iteration, which is why currentcell only had the very last value

Dim found As Sting
For each cell in eachCell
    If [condition] Then
        found = cell.ToString()
        Exit For
    End If
Next
If Not found is Nothing Then
    'Congrats you found it
Else
    'Too bad, not in list
End If

If you want to do something with more than one element you need to do that something in the loop, or pass the element as a parameter to a seperate function

Sub Main()
    Dim myArray As List(Of String);
    myArray = [get your array of elements]

    For Each item As String In myArray
        DoThing(item)
    Next

End Sub

Sub DoThing(value As String)
    Console.WriteLine(value)
End Sub

hopefully that helps you