SolvedPrivate

Translate the VBA code into English meaniningful words

Posted on 2015-02-13
4
29 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:
SimonAdept 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 45

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 5

Author Closing Comment

by:Flora
ID: 40608769
Wonderful!

Thank you Simon . Thank you Martin
0
 
LVL 45

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now