SolvedPrivate

Arrays in UDFs for VBA Excel

Posted on 2015-02-16
8
20 Views
Last Modified: 2016-02-10
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
Comment
Question by:TCristiano
8 Comments
 
LVL 12

Accepted Solution

by:
Jeff Darling earned 250 total points
ID: 40613064
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
 
LVL 40
ID: 40613065
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
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40613066
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40613077
buttons that Jacques referred to.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 250 total points
ID: 40613089
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
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40613119
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40667527
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now