Creating a Function to Compute the Dot Product in VBA

alyb

New Member
Joined
Mar 28, 2020
Messages
2
Office Version
365
Platform
Windows
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
 

Some videos you may like

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
Joined
Mar 29, 2006
Messages
1,707
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
Joined
Mar 28, 2020
Messages
2
Office Version
365
Platform
Windows
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
Joined
Mar 29, 2006
Messages
1,707
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,044
Messages
5,466,213
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top