Excel VBA Pass Cell Array into Function

Question

I'm trying to write a simple VBA Function like this:

Public Function ReturnMMult(Arr1() As Variant, Arr2() As Variant) As Variant

    ReturnMMult = WorksheetFunction.MMult(Arr1,Arr2)

End Function

But it always gives me #VALUE! I've tried changing the Arr's to Ranges, but that doesn't work either. I basically want to be able to write functions that can take ranges like $A1:$A10 or something like that. Looked in a lot of places, and can't figure it out. What am I doing wrong?

Why do you want a function that replicates a worksheet function? — SierraOscar
– SierraOscar, Commented May 13, 2016 at 16:07
If I can solve this, I can solve my real problem. Just need to know how to do this in general. — user3685285
– user3685285, Commented May 13, 2016 at 16:10
It looks like MMult returns an array, try something like Public Function ReturnMMult(a As Excel.Range, b As Excel.Range) As Variant() — Nathan_Sav
– Nathan_Sav, Commented May 13, 2016 at 16:11

Nathan_Sav · Accepted Answer · 2016-05-13 16:24:23Z

3

something like

Public Function M(a As Excel.Range, b As Excel.Range) As Variant()
    Dim a1() As Variant
    Dim a2() As Variant
    a1 = a.value
    a2 = b.value
    M = Application.WorksheetFunction.MMult(a1, a2)
End Function

edited May 13, 2016 at 16:24

answered May 13, 2016 at 16:18

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nathan_Sav Over a year ago

You will need to add some checks that the columns in a is equal to the rows in b, and also, this error's, so you'll need to error trap to be safe. And also scott's post :)

Scott Craner · Accepted Answer · 2016-05-13 16:19:58Z

1

You need to bring them in as ranges then shift them to arrays:

Public Function ReturnMMult(Arr1rng As Range, Arr2rng As Range) As Variant
Dim Arr1() As Variant: Arr1 = Arr1rng.Value
Dim Arr2() As Variant: Arr2 = Arr2rng.Value
ReturnMMult = WorksheetFunction.MMult(Arr1, Arr2)

End Function

answered May 13, 2016 at 16:19

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Comments

skkakkar · Accepted Answer · 2016-05-13 16:21:00Z

0

This is an example of usage of MMult worksheet function. Please adopt it to your situation.

Sub test() 

    Dim xArray As Variant, yArray As Variant, zArray As Variant 
    Dim Fn As Object 
    Set Fn = Application.WorksheetFunction 

    xArray = Range("A1:B2").Value 
    yArray = Range("D1:E2").Value 
    zArray = Fn.MMult(xArray, yArray) 

    ActiveCell.Resize(2, 2).Value = zArray 

End Sub

MMult returns the #VALUE! error when: Any cells are empty or contain text. The number of columns in array1 is different from the number of rows in array2. The size of the resulting array is equal to or greater than a total of 5,461 cells. Reference WorksheetFunction.MMult method

answered May 13, 2016 at 16:21

skkakkar

2,8482 gold badges20 silver badges32 bronze badges

1 Comment

user3685285 Over a year ago

I'm trying to do this with the arrays as parameters.

Collectives™ on Stack Overflow

Excel VBA Pass Cell Array into Function

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related