CODEWITHSUNDEEP
vbaexcel
Tsnorthern
Tsnorthern

Reputation: 27

VBA for Excel: Recording and Processing Variable Data

I am taking an excel spreadsheet as an input. Within this spreadsheet there are a series of variables (in my case, nodes, but the distinction is irrelevant). Each node has a series of sub-variables associated with them (essentially, a series of cases), and each case has three float variables associated with them (X, Y, and Z coordinates). It can be visualized like so:

 NODE 1    CASE 1    CASE 1 X
                     CASE 1 Y
                     CASE 1 Z
           CASE 2    CASE 2 X
                     CASE 2 Y
                     CASE 2 Z

 NODE 2    CASE 1    CASE 1 X
                     CASE 1 Y
                     CASE 1 Z
           CASE 2    CASE 2 X
                     CASE 2 Y
                     CASE 2 Z

See the image below for the format of my input data. Note that there can be potentially hundreds of individual points.

This image shows how the data is initially formatted

Now here is where i'm stuck:

I need to read this data in, point by point, and process it. The various variables will be processed and outputted onto a new sheet, but i can't come up with an elegant way of reading in the data to be processed. If this were Python, i'd establish a class for the various points and associate methods with that class, but i don't know an analogous version for VBA.

What is the most efficient way to iterate through each point, read in the data associated with each case for each point, process it, and output the end result to a new sheet?

Thanks in advance for any help. This one really has me stuck.

Upvotes: 0

Views: 46

Answers (1)

DisplayName
DisplayName

Reputation: 13386

Try this (change “myInputSheetName” and “myOutputSheetName” with your actual inout and output sheet name):

Sub ReadDataIn()
    Dim data As Variant

    Data = Worksheets(“myInputSheetName”).UsedRange.Value 

    ‘ and now you have a 2D array with as many rows and columns as excel sheet rows and columns with data
    Dim iRow As Long, jCol As Long
    For iRow = 1 To Ubound(data,1)
        For jCol = 1 To Ubound(data,2)
            MsgBox data(iRow, jCol)
            ‘Process your data
        Next
    Next

‘Write processed data into your output sheet

 Worksheets(“myOutputSheetName”).Range(“A1”).Resize(Ubound(data, 1), Ubound(data, 2)).Value = data

    End With

End Sub

Upvotes: 0

Related Questions