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

PostScriptStudent

New Member
Joined
Nov 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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.
VBA Code:
    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
VBA Code:
     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
VBA Code:
    Range("A21") = Application.WorksheetFunction.Sum(Range("A2:A20"))

Hope that helps
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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

VBA Code:
    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

New Member
Joined
Nov 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
    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

New Member
Joined
Nov 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
    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
VBA Code:
     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
VBA Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top