How to Sum an Array in VBA

In this episode, we're taking a look at how to sum up values in a VBA array. On this channel we have done some work with 1d and 2d arrays, mostly for speed in our applications. However, sometimes we need to do some math on our arrays and summing up all of the numbers in an array is high on the list for useful things to do when you're doing high-speed in-memory processing. We'll build an array of integers and then examine and sum up the values using two different methods, examining the benefits of each along the way.
Block spammers from harassing your Remote Desktop, filling your Windows security logs with failed RDP attempts, and burning up your bandwidth with my BZ RDP Cloaker:
buy.billzone.c...
Check out my easy time entry system for your business:
system.billzon...
Join me on Patreon!
/ mackenziedataengineering
Check out some tech (and support my channel!):
www.amazon.ca/...
Want the code from this video?
mackenziemacken...
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a RUclips Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#msaccess #vba

[@JoeyMagallanes-sc6rm]

I love the way you split the career to basic and advanced skills. I’ve been keep doing that to become the data engineer. Thanks for sharing!

[@seanmackenziedataengineering]

You're very welcome! Good luck on your data engineering journey 🛠

[@tuffley2175]

Wow
I got > 20 text boxes in a user form = number type , double
How can I assign those into the array please
[is it need to have a variable for each]
Field names are bit long and bit messy when using in a query!!

[@seanmackenziedataengineering]

If you numbered them like txtMyNumber0, txtMyNumber1, txtMyNumber2 etc then you can load them in one loop, something like:
Dim intItem as Integer
Dim arr(26)
For intItem = 0 to 26 'say you have 27 text boxes
arr(intItem) = Forms!MyForm("txtMyNumber" & intItem)
Next
Array is loaded!
If you don't have a numbered design on your form then you can manually load:
arr(0) = Forms!MyForm!txtThisField
arr(1) = Forms!MyForm!txtThatField
etc.

[@tuffley2175]

2nd option suits me
I will try that out
1st option is cleaner and simpler if new this on designing stage
Bravo 🫡

[@tuffley2175]

Will you kind enough to add to your “Download’ please
Better to keep it in my collection 🤞

[@seanmackenziedataengineering]

@@tuffley2175 added.

[@serdip]

Very informative and helpful video. Thanks for sharing.
Since VBA lacks augmented assignment operators (among other things), I created a subroutine that somewhat simplifies incrementing or decrementing a variable. I have used it hundreds of times since then and for me at least, it's pretty handy:
Public Sub Incr(ByRef InputValue As Variant, ByVal Amount As Variant)
'---------------------------------------------------------------------------------------
' Procedure : Incr
' Purpose : Increments/decrements referenced variable
' :
' :
'---------------------------------------------------------------------------------------
10 On Error GoTo ErrProc
20 vntValue = vntValue + vntAmount
30 Exit Sub
ErrProc:
40 MsgBox "Error " & Err.Number & " (" & Err.Description & ") at line " _
& CStr(Erl) & " in procedure Incr of Module " & m_MODULE_NAME
Usage:
Dim x As Integer
x = 5
Incr x, 10
Debug.Print x '15
Incr x, -10
Debug.Print x '5
Thank you kindly.

[@seanmackenziedataengineering]

Cool!