New to VBA, need help with Functions and calling them in a sub procedure

[PostScriptStudent]

Procedures to perform are:
* Populate a 1D range of general size n - done
* Add two 1D ranges - I have the ranges but I can't get the sum of them (code below)

Private Sub CommandButton1_Click()

'Clearing worksheet
Range("A:Z").Clear

'Declarations
Dim range1, range2 As Range, array1() As Double, SumRange As Double

SumRange = 0

'Initialization
Set range1 = populateRange(InputBox("enter # of rows of range 1"), 1, "A1")
Set range2 = populateRange(InputBox("enter # of rows of range 2"), 1, "C1")



Range("E1").Value = SumRange

End Sub

Here are my Functions:
'Function populates a 2D range starting at any cell in the worksheet using InputBox
Function populateRange(m As Integer, n As Integer, RangeDef As String)

'Function Documentation
'm is the number of rows of the range
'n is the number of colums of the range
'RangeDef is where the range will start being stored on the worksheet

'Declarations
Dim i, j As Integer
Dim range1 As Range

'Initialization
Set range1 = Range(RangeDef)

For i = 1 To m

For j = 1 To n

range1.Offset(i - 1, j - 1).Value = InputBox("Enter " & i & ", " & j & " element of array ")

Next j

Next i

Set range1 = Range(Range(RangeDef), Range(RangeDef).End(xlDown))

Set populateRange = range1


End Function


'Function converting a given 1D range to an array
Function Range2Array(range1 As Range) As Double()


'Declarations
Dim length, i As Integer, array1() As Double

length = range1.Rows.Count

ReDim array1(length)

For i = 1 To length

array1(i) = range1(i, 1).Value

Next i

Range2Array = array1

End Function

I would love some help with this
Thank you
PSStudent

 

[sijpie]

Hi PSStudent,

first of all. welcome to the board. 2nd: when you post code, please post it between code tags (in the post window there are little icons at the top. Click on the VBA one. Then past your code.)

You want to use arrays, which is great because they are very fast. That means you can do away with a lot of the stuff that you have coded. You need to understand why: reading from, but particularly writing to the sheet is a relatively slow business.

For starters: your Range2Array function. This can be shortened immensely or left out all together.

    Dim vArray as variant

    vArray = Range("A1:G500").Value

This creates a 2D array vArray with the contents of A1:G500 . You can specify the range anyway you need. It requires just one read, no looping. So it is blindingly fast. In your code it would have required 3500 reads.


Your function PopulateRange is OK, it has a lot of writes, but the human input here is by far the slowest article, so it wouldn't matter. But y9ou need to ask yourself: is this the best way to get the input? Why not directly on the sheet? That way the user has full view of the array, can use arrow keys or Enter to move about. But that is your decision.

What I understand is that you first ask the user to input all the numbers for the 2 1D ranges. Then you want to get the sum for each of the two columns.

There are two ways: summing in VBA or using a worksheet function.

Way 1) summing in arrays. You need to understand that even if you assign an array (using the method above) to a single column, you will still get a 2D array (of one column). You can address the elements of the array by row and column element. In the code below the function UBound(array,dimension) gives the number of elements held in the array in that dimension

     Dim vArr as variant
     Dim lR as Long
     Dim dSum as Double

    vArr = Range("A2:A20")
    For lR = 1 to Ubound(vArr,1)    'let lR run from 1st to last row of the array
         dSum = dSum + vArr(lR, 1)  'Add each element of the first column of vArr
    Next lR

    Range("A21") = dSum

Way 2) using the Excel worksheet functions

    Range("A21") = Application.WorksheetFunction.Sum(Range("A2:A20"))

Hope that helps

 

[sijpie]

By the way: If you have an array in memory and want to write the contents to the sheet, than the fastest method is to just set a range (with the right size) to the array

    Dim vArr As Variant
    Dim lR As Long
   
    ' Read current range into array
    vArr = Range("A1:G" & Cells(1, Rows).End(xlUp).Row).Value
    For lR = 1 To UBound(vArr, 1)
        'do something
    Next lR
   
    'then dump vArr to sheet
   
    Range("J1").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value

Only one read and one write, blindingly fast

 

[PostScriptStudent]

By the way: If you have an array in memory and want to write the contents to the sheet, than the fastest method is to just set a range (with the right size) to the array

    Dim vArr As Variant
    Dim lR As Long
  
    ' Read current range into array
    vArr = Range("A1:G" & Cells(1, Rows).End(xlUp).Row).Value
    For lR = 1 To UBound(vArr, 1)
        'do something
    Next lR
  
    'then dump vArr to sheet
  
    Range("J1").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value

Only one read and one write, blindingly fast

Thank you very much, I will try it out! You were very informative and helpful.

 

[PostScriptStudent]

Hi PSStudent,

first of all. welcome to the board. 2nd: when you post code, please post it between code tags (in the post window there are little icons at the top. Click on the VBA one. Then past your code.)

You want to use arrays, which is great because they are very fast. That means you can do away with a lot of the stuff that you have coded. You need to understand why: reading from, but particularly writing to the sheet is a relatively slow business.

For starters: your Range2Array function. This can be shortened immensely or left out all together.

    Dim vArray as variant

    vArray = Range("A1:G500").Value

This creates a 2D array vArray with the contents of A1:G500 . You can specify the range anyway you need. It requires just one read, no looping. So it is blindingly fast. In your code it would have required 3500 reads.


Your function PopulateRange is OK, it has a lot of writes, but the human input here is by far the slowest article, so it wouldn't matter. But y9ou need to ask yourself: is this the best way to get the input? Why not directly on the sheet? That way the user has full view of the array, can use arrow keys or Enter to move about. But that is your decision.

What I understand is that you first ask the user to input all the numbers for the 2 1D ranges. Then you want to get the sum for each of the two columns.

There are two ways: summing in VBA or using a worksheet function.

Way 1) summing in arrays. You need to understand that even if you assign an array (using the method above) to a single column, you will still get a 2D array (of one column). You can address the elements of the array by row and column element. In the code below the function UBound(array,dimension) gives the number of elements held in the array in that dimension

     Dim vArr as variant
     Dim lR as Long
     Dim dSum as Double

    vArr = Range("A2:A20")
    For lR = 1 to Ubound(vArr,1)    'let lR run from 1st to last row of the array
         dSum = dSum + vArr(lR, 1)  'Add each element of the first column of vArr
    Next lR

    Range("A21") = dSum

Way 2) using the Excel worksheet functions

    Range("A21") = Application.WorksheetFunction.Sum(Range("A2:A20"))

Hope that helps

Thank you for the welcome, I enjoy coding but I really struggle with the language. I will remember to post properly in the future and I thank you for the correction. I will try both of the suggestions to see which works best for me.
Thanks again