Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User define function that reply array
Hi, I am trying to design a formula that returns a kind of score of
your input. I have attached this spread sheet for ilustration http://spreadsheets.google.com/pub?k...EVrvj9aEv9xmCA the user formula has 2 array inputs (B3:B11, C3:C11) i need it to answer the stuff in yellow. so far I have been a able to load up 2 arrays with 9 layers of information like arr(0) grape and arr1(0) 38 arr(1) grape and arr1(1) 45 arr(2) kiwi and arr1(2) 58 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User define function that reply array
If a function is to return an array of values to more than one cell,
the function must be entered into multiple cells as an array formula, using CTRL SHIFT ENTER. There is no way around this. See http://www.cpearson.com/Excel/Return...ysFromVBA.aspx for more information and examples about returning arrays from user defined functions written in VB/VBA. One change I would make to your code is to take the ReDim Preserve arrV(c) lines out of the loops. Preserve is an expensive operation and can cause performance issues. Instead of using Preserve inside the loops, ReDim the array to the maximum possible size before the loop, fill the elements within the loop, and then use Preserve after the loop to shrink the array down to the actual used size. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 1 Dec 2008 05:20:50 -0800 (PST), PauloD wrote: Hi, I am trying to design a formula that returns a kind of score of your input. I have attached this spread sheet for ilustration http://spreadsheets.google.com/pub?k...EVrvj9aEv9xmCA the user formula has 2 array inputs (B3:B11, C3:C11) i need it to answer the stuff in yellow. so far I have been a able to load up 2 arrays with 9 layers of information like arr(0) grape and arr1(0) 38 arr(1) grape and arr1(1) 45 arr(2) kiwi and arr1(2) 58 . arr(9) banana and arr1(9) 75 I need help on doing the sumIF on the values across the arrays and later on sorting and deleting the repeated data and at last but not least. if there is a way wher the UDF could return the range without or automating the Crtl+Shift+ENTER here is the code that I have right now. Function test(a As Range, b As Range) As String() Dim Cell As Range Dim arrN() As String Dim arrV() As Long Dim arrF() As String Dim c As Integer Dim newC As Integer Dim Hcnt As Integer Hcnt = a.Count - 1 c = 0 For Each Cell In a ReDim Preserve arrN(c) arrN(c) = Cell.Value c = c + 1 Next c = 0 For c = 0 To Hcnt Step 1 c = 0 For Each Cell In b ReDim Preserve arrV(c) arrV(c) = Cell.Value c = c + 1 Next c = 0 For c = 0 To Hcnt Step 1 ReDim Preserve arrF(Hcnt) arrF(c) = arrN(c) & " - " & arrV(c) Next test = arrF I would apretiate any help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User define function that reply array
chip, thank you for helping out.
sure, I understand the CTRL SHIFT ENTER issue, i also have fixed the array redim outside the loops. thanks for highlighting that. how do i call excel functions inside my UDF function test(a as range, b as range) as string ' i have a loop that loads the arrays from the imput range arrN() - for names - (mouse, webcam, printers, ...) ArrV() - for values -ex. amount- (22, 34, 12, ...) result = Application.WorksheetFunction.SumIf(arrN(), arrN(c), arrV ()) ???? ' make new loop to load results in new array test = new array end function I also am having trouble @ re positioning values aroun the array. something like this 1)I only whant one name of each 2) my imput array goes like this (mouse, mouse, webcam, printers,mouse, ...) 3)I was able to erase the data but now my array looks like this (1: mouse, 2: " ", 3: webcam, 4: printers, 5: " ", ...) 4) how do I colaps the array so it looks like (1: mouse, 2: webcam, 3: printers, ...) Paulo On 1 dez, 14:28, Chip Pearson wrote: If a function is to return an array of values to more than one cell, the function must be entered into multiple cells as an array formula, using CTRL SHIFT ENTER. There is no way around this. Seehttp://www.cpearson.com/Excel/ReturningArraysFromVBA.aspxfor more information and examples about returning arrays from user defined functions written in VB/VBA. One change I would make to your code is to take the * * * * ReDim Preserve arrV(c) lines out of the loops. Preserve is an expensive operation and can cause performance issues. Instead of using Preserve inside the loops, ReDim the array to the maximum possible size before the loop, fill the elements within the loop, and then use Preserve after the loop to shrink the array down to the actual used size. Cordially, Chip Pearson Microsoft MVP * * Excel Product Group Pearson Software Consulting, LLCwww.cpearson.com (email on web site) On Mon, 1 Dec 2008 05:20:50 -0800 (PST), PauloD wrote: Hi, I am trying to design a formula that returns a kind of score of your input. I have attached this spread sheet for ilustration http://spreadsheets.google.com/pub?k...EVrvj9aEv9xmCA the user formula has 2 array inputs (B3:B11, C3:C11) i need it to answer the stuff in yellow. so far I have been a able to load up 2 arrays with 9 layers of information *like arr(0) grape * * * *and *arr1(0) 38 arr(1) grape * * * *and *arr1(1) 45 arr(2) kiwi * * * * and *arr1(2) 58 * * . arr(9) banana * * * and *arr1(9) 75 I need help on doing the sumIF on the values across the arrays and later on sorting and deleting the repeated data and at last but not least. if there is a way wher the UDF could return the range without or automating the Crtl+Shift+ENTER here is the code that I have right now. Function test(a As Range, b As Range) As String() * *Dim Cell As Range * *Dim arrN() As String * *Dim arrV() As Long * *Dim arrF() As String * *Dim c As Integer * *Dim newC As Integer * *Dim Hcnt As Integer * *Hcnt = a.Count - 1 * *c = 0 * *For Each Cell In a * * * *ReDim Preserve arrN(c) * * * *arrN(c) = Cell.Value * * * *c = c + 1 * *Next * *c = 0 * *For c = 0 To Hcnt Step 1 * *c = 0 * *For Each Cell In b * * * *ReDim Preserve arrV(c) * * * *arrV(c) = Cell.Value * * * *c = c + 1 * *Next * *c = 0 * *For c = 0 To Hcnt Step 1 * * * *ReDim Preserve arrF(Hcnt) * * * *arrF(c) = arrN(c) & " - " & arrV(c) * *Next * *test = arrF I would apretiate any help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User define function that reply array
http://spreadsheets.google.com/ccc?k...EVrvj9aEv9xmCA
Function paulo(a As Range, b As Range) As String() Dim Cell As Range Dim arrN(), arrF() As String Dim arrV() As Long Dim result As Long Dim c, Hcnta, Hcntb As Integer ' logic test Hcnta = (a.Count) Hcntb = (b.Count) If Hcntb < Hcnta Then MsgBox "Range de entrada Inconsistente" Exit Function End If ' load arry a c = 0 ReDim Preserve arrN(Hcnta - 1) For Each Cell In a arrN(c) = Cell.Value c = c + 1 Next ' load arry b c = 0 ReDim Preserve arrV(Hcntb - 1) For Each Cell In b arrV(c) = Cell.Value If c = 0 Then result = Application.WorksheetFunction.SumIf(arrN, arrN(c), arrV) '? i get mismach error i think is from here MsgBox result End If c = c + 1 Next c = 0 For c = 0 To Hcnta Step 1 ReDim Preserve arrF(Hcnta) If c 0 Then If c = Hcnta Then Exit For If arrN(c) = arrN(c - 1) Then ' Erase arrN(c) Else arrF(c) = arrN(c) & " - " & arrV(c) End If Else arrF(c) = arrN(c) & " - " & arrV(c) End If Next paulo = arrF End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Array Function | Excel Programming | |||
User define function | Excel Discussion (Misc queries) | |||
How to define table array in Vlookup function using VBA? | Excel Programming | |||
User define function and dialog | Excel Programming | |||
create multi-field, with layout, user questionnaire to save reply. | Excel Programming |