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

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

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

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