Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 30
  • Last Modified:

Arrays in UDFs for VBA Excel

Hello Experts-

I have a UDF that pulls variables to calculate a customer's annual utility bill. The UDF references 12 months of usage data, each month is an individual cell reference which means 12 different variables in the UDF (plus the other ones needed). I've tried to name them as a range and simplify the UDF but have had no success. So, the question is, how can I pull in the 12 months of data as an array, have it cycle through the calculations and then spit out one annual number? Do I have name the range in Excel and reference that in the UDF cell equation?I have a copy of the spreadsheet attached.

Also, for some reason, I am unable to show more than one subroutine at a time in each module (I have to use the drop down box where is says declarations.) Is there a way to change this back?

Regards,
Todd
0
TCristiano
Asked:
TCristiano
2 Solutions
 
Jeff DarlingDeveloper AnalystCommented:
One way to do that would be to build a delimited string with your parameters and then use the split function to create an array.

Here is a test function that passes the delimited string.

Public Function DoMyCalc(myData As String, strName)

Dim myRetVal
Dim ArrMyData

ArrMyData = Split(myData, "|")

myRetVal = 0

For i = 0 To UBound(ArrMyData)
  myRetVal = myRetVal + ArrMyData(i)
Next

DoMyCalc = strName & " " & myRetVal

End Function

Open in new window


Sample excel file attached
0
 
Jacques Bourgeois (James Burger)Commented:
I can't help you with UDF, I have never used them, doing everything in VBA.

But for the modules, there are 2 toggle buttons in the lower left corner of the editor that lets you switch between the 2 different views.
0
 
Jeff DarlingDeveloper AnalystCommented:
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Jeff DarlingDeveloper AnalystCommented:
buttons that Jacques referred to.
0
 
aikimarkCommented:
If you reference a range object, you can assign a Variant data type variable = rangeobject.value

This will populate the Variant variable with a 2D array.
0
 
Jeff DarlingDeveloper AnalystCommented:
Note that the 2D Array isn't zero based, it starts with 1.

Here is a sample based on aikimark's suggestion

Public Function foo(MyRange As Range, strName)

Dim myRetVal
Dim ArrMyRange

ArrMyRange = MyRange.Value
myRetVal = 0

For i = 1 To UBound(ArrMyRange,2) 
  myRetVal = myRetVal + ArrMyRange(1, i)
Next

foo = strName & " " & myRetVal

End Function

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now