SolvedPrivate

Translate the VBA code into English meaniningful words

Posted on 2015-02-13
4
33 Views
Last Modified: 2016-02-10
Hello,

I am VBA learner and with very basic knowledge like a starter.  i found the below code and i am trying to understand what are these Ubound and Lbound and redim etc  i would appreciate if someone can translate each and everyone of these lines into meaniningful english words so that i understand what this code is really doing and what each jargon really mean.


Sub SubbyWays()
Dim sData   As Variant
Dim f_Data  As Variant
Dim iC      As Integer
Dim iR      As Long
Dim rCoun   As Long
Dim UCol As Integer
rCoun = 1
sData = Range("A1").CurrentRegion.Value
UCol = UBound(sData, 2)
ReDim f_Data(1 To UBound(sData, 1) * UCol, 1 To 1)
For iR = LBound(sData, 1) To UBound(sData, 1)
    For iC = LBound(sData, 2) To UBound(sData, 2)
        f_Data(rCoun, 1) = sData(Int((iC - 1) / UCol) + iR, 1 + ((iC - 1) Mod UCol))
        rCoun = rCoun + 1
    Next
Next iR
Range("J1").Resize(UBound(f_Data), 1) = f_Data
End Sub

Open in new window

0
Comment
Question by:Flora
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 300 total points
ID: 40608354
Posted as code. Will display best in a code module so the comments are aligned and coloured.

Note that this macro will fail if you don't have any data in cell A1.

Sub SubbyWays()
Dim sData   As Variant  'declare a variable to store variant data (any datatype)
Dim f_Data  As Variant  'declare a variable to store variant data (any datatype)
Dim iC      As Integer  'declare a variable to store integer data (whole numbers)
Dim iR      As Long     'declare a variable to store long integer data (large whole numbers)
Dim rCoun   As Long     'declare a variable to store long integer data (large whole numbers)
Dim UCol As Integer     'declare a variable to store integer data (whole numbers)
rCoun = 1               'assign the value 1 to the variable
sData = Range("A1").CurrentRegion.Value 'Assign worksheet range (contiguous block of cells with content starting from A1) values to the variable
UCol = UBound(sData, 2) 'Get the upper bound (the upper limit) of the second dimension of the variable (columns)
ReDim f_Data(1 To UBound(sData, 1) * UCol, 1 To 1) 'Create new single column array with enough rows to contain all the data from the sdata
For iR = LBound(sData, 1) To UBound(sData, 1) 'Loop through each row in sData
    For iC = LBound(sData, 2) To UBound(sData, 2) 'loop through each column of the current row
        f_Data(rCoun, 1) = sData(Int((iC - 1) / UCol) + iR, 1 + ((iC - 1) Mod UCol)) 'fill the element of the new array from the original one
        rCoun = rCoun + 1 'increment the counter
    Next    'next column
Next iR     'next row
Range("J1").Resize(UBound(f_Data), 1) = f_Data 'resize the single cell range J1 to the size of the new array and fill it with the data from the array
End Sub

Open in new window

0
 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 200 total points
ID: 40608392
I see Simon beat me to it, but I'll post anyhow.

Sub SubbyWays()
Dim sData   As Variant
Dim f_Data  As Variant
Dim iC      As Integer
Dim iR      As Long
Dim rCoun   As Long ' row counter
Dim UCol As Integer

' The purpose of this code seems to be to list the contents of a multi-column
' table in one column on the sheet

' Initialize rCoun to 1
rCoun = 1
' Capture the data (not including the heading) of the table that contains cell A1
sData = Range("A1").CurrentRegion.Value

' sData is being used as an array and UBound (upper boundry) is the
' upper dimension of the array. All the comments below assume that
' the table has 3 columns and 15 rows of data (ignoring the heading)

' Store the column number of the last column. The "2" refers to the columns
' while "1" would refer to the rows
UCol = UBound(sData, 2)

' f_data is being used as a two dimensional array and the redim statement
' is defining the size of the array. This statement sets the lower boundry
' of the 1st dimension to 1 and the upper boundry to UBound(sData, 1) * UCol which is 3  x 15 = 45
' The second dimension's upper and lower bounds are set to 1 by the "1 to 1".
' Note that the second dimension of the table does not seem to be used
ReDim f_Data(1 To UBound(sData, 1) * UCol, 1 To 1)

' Step through the rows of the array. in other words from 1 to 15
For iR = LBound(sData, 1) To UBound(sData, 1)
    ' Step through the columns of the array, in other words 1 to 3
    For iC = LBound(sData, 2) To UBound(sData, 2)
        ' Set the row values of the array
        f_Data(rCoun, 1) = sData(Int((iC - 1) / UCol) + iR, 1 + ((iC - 1) Mod UCol))
        ' Increment rCoun by 1
        rCoun = rCoun + 1
    Next
Next iR
' Display the data on the sheet
Range("J1").Resize(UBound(f_Data), 1) = f_Data
End Sub

Open in new window

0
 
LVL 6

Author Closing Comment

by:Flora
ID: 40608769
Wonderful!

Thank you Simon . Thank you Martin
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40609069
If all you want to do is to list the table's data in column J, the following is much simpler.

Sub SubbyWays2()
Dim lngRow As Long
Dim lngCol As Long
Dim lngNextRow As Long

' Initialize the destination row number
lngNextRow = 1

' Step through the table rows
For lngRow = 1 To Range("A1").CurrentRegion.Rows.Count
    ' Step through the table columns
    For lngCol = 1 To Range("A1").CurrentRegion.Columns.Count
        ' Set the cell in column J equal to the table cell with
        ' a row number of lngRow and a column number of lngCol
        Cells(lngNextRow, "J") = Cells(lngRow, lngCol)
        ' Increment the destination row
        lngNextRow = lngNextRow + 1
    Next
Next

End Sub

Open in new window


In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

685 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