• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 44
  • Last Modified:

Translate the VBA code into English meaniningful words

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
Flora
Asked:
Flora
  • 2
2 Solutions
 
SimonCommented:
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
 
Martin LissOlder than dirtCommented:
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
 
FloraAuthor Commented:
Wonderful!

Thank you Simon . Thank you Martin
0
 
Martin LissOlder than dirtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now