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 VariantDim f_Data As VariantDim iC As IntegerDim iR As LongDim rCoun As LongDim UCol As IntegerrCoun = 1sData = Range("A1").CurrentRegion.ValueUCol = 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 NextNext iRRange("J1").Resize(UBound(f_Data), 1) = f_DataEnd Sub

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.
(CODE)

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 variablesData = Range("A1").CurrentRegion.Value 'Assign worksheet range (contiguous block of cells with content starting from A1) values to the variableUCol = 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 sdataFor 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 columnNext iR 'next rowRange("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 arrayEnd Sub

Sub SubbyWays()Dim sData As VariantDim f_Data As VariantDim iC As IntegerDim iR As LongDim rCoun As Long ' row counterDim 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 1rCoun = 1' Capture the data (not including the heading) of the table that contains cell A1sData = 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 rowsUCol = 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 usedReDim f_Data(1 To UBound(sData, 1) * UCol, 1 To 1)' Step through the rows of the array. in other words from 1 to 15For 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 NextNext iR' Display the data on the sheetRange("J1").Resize(UBound(f_Data), 1) = f_DataEnd Sub

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 LongDim lngCol As LongDim lngNextRow As Long' Initialize the destination row numberlngNextRow = 1' Step through the table rowsFor 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 NextNextEnd Sub

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 …