# Creating a Function to Compute the Dot Product in VBA

#### alyb

##### New Member
I have been trying for hours to create a function that will find the dot product of two independent columns. I have very limited knowledge in VBA and my professor is no help. This is what I have right now, which is heavily based on what I learned in class. Does anybody have suggestions of what could be wrong or an easier way to execute this?

Function Dot(y As Range, x As Range) As Variant
Dim A() As Double
Dim i As Integer, n As Integer, nr As Integer, nc As Integer 'where the matrix dimensions of y are (i, n)
Dim j As Integer, m As Integer, ns As Integer, nd As Integer 'where the matrix dimensions of x are (j, m)
nr = y.Rows.Count
nc = y.Columns.Count
ns = x.Rows.Count
nd = x.Columns.Count

If nr <> ns Then
MsgBox ("vectors are not of same length")
Exit Function
End If

ReDim A(1 To 1, 1 To nc, 1 To 1, 1 To nd) As Double
For n = 1 To nc
For m = 1 To nd
A(1, n, 1, m) = 0
For i = 1 To 1
For j = 1 To 1
A(1, n, 1, m) = A(1, n, 1, m) + (y(i, n) * x(j, m))

Next j
Next i
Next m
Next n

Dot = A
End Function

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Anthony47

##### Well-known Member
Is the "dot product" (DP) the sum of the product of each pair of information?
That is DP of range("A1:A10") and range("B1:B10") is calculated as
DP(A1:A10; B1:B10) = A1*B1 + A2*B2 + A3*B3 + . . . . + A10*B10)

If Yes, then you already have the function, its Sumproduct, so you can simply use
`=SUMPRODUCT(A1:A10,B1:B10)`

If Not, then... forget my message Bye

#### alyb

##### New Member
Is the "dot product" (DP) the sum of the product of each pair of information?
That is DP of range("A1:A10") and range("B1:B10") is calculated as
DP(A1:A10; B1:B10) = A1*B1 + A2*B2 + A3*B3 + . . . . + A10*B10)

If Yes, then you already have the function, its Sumproduct, so you can simply use
`=SUMPRODUCT(A1:A10,B1:B10)`

If Not, then... forget my message Bye
Yes, that is the dot product. I believe my assignment wanted me to create my own function without using the worksheet functions but I might still be able to use this, thank you!

#### Anthony47

##### Well-known Member
A useless DP function:
Code:
``````Function myDP(ByRef ARan As Range, ByRef BRan As Range) As Variant
Dim I As Long, J As Long, Interm As Double
'
If ARan.Rows.Count = BRan.Rows.Count And ARan.Columns.Count = BRan.Columns.Count Then
For I = 1 To ARan.Rows.Count
For J = 1 To ARan.Columns.Count
Interm = Interm + ARan.Cells(I, J) * BRan.Cells(I, J)
Next J
Next I
myDP = Interm
Else
myDP = CVErr(2015)
End If
End Function``````