Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
SolvedPrivate

Translate the VBA code into English meaniningful words

Posted on 2015-02-13
4
Medium Priority
?
39 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 1200 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 50

Assisted Solution

by:Martin Liss
Martin Liss earned 800 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 50

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

581 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