need help: how to multiply two array in excel 1

[Kalie]

help
i have x and y array's i want to multiply. somthing like MMULT(Array1;Array2) but this time using vba function called planck()

-------------------------------------------------
Public Function Planck(Lambda As Double, Temperature As Double) As Double

Dim C1 As Double, C2 As Double, Eee As Double

C1 = 374200000#
C2 = 14390#
Eee = 2.718281828

If Lambda * Temperature < 200 Then

Planck = 1E-16
Else
Planck = C1 / (Lambda ^ 5 * (Eee ^ (C2 / (Lambda * Temperature)) - 1#))
End If

End Function
-------------------------------------------------


xArray=B11
x_vals=(0.1,0.15,0.2,0.3,0.4,0.7,1,2,4,6,10,20,40,60,100)

and

yArray=A2:A8
y_vals=(50,100,300,800,1000,2000,5800)


I can do it with very long way but i am wondering if someone know a function to solve this so I can simply select on B28 and Ctrl+shift+enter and get the table calculated.


ps. i get the vba code from:
[faculty.virginia.edu/ribando/modules/xls/VBAPrimer.pdf]
and i use excel 2002

 

[TheTick]

The VBA function is not available for arrays?

If not, perhaps turn the VBA into an Excel addin first.

 

[IDS]

Since the number of rows in Lambda is less than the number of columns in Temperature I'm guessing that MMult() (which does a matrix multiplication) is not really what you want.

Assuming you want to return an array of all the values of Planck for every combination of Lambda and Temperature, the code below will do the job:

Public Function Planck(Lambda As Variant, Temperature As Variant) As Variant

Dim C1 As Double, C2 As Double, Eee As Double, NumRows As Long, NumCols As Long, PlanckA() As Double
Dim i As Long, j As Long

If TypeName(Lambda) = "Range" Then Lambda = Lambda.Value2
If TypeName(Temperature) = "Range" Then Temperature = Temperature.Value2

NumRows = UBound(Lambda)
NumCols = UBound(Temperature, 2)
ReDim PlanckA(1 To NumRows, 1 To NumCols)

C1 = 374200000#
C2 = 14390#
Eee = 2.718281828

For i = 1 To NumRows
For j = 1 To NumCols
If Lambda(i, 1) * Temperature(1, j) < 200 Then

        PlanckA(i, j) = 1E-16
    Else
        PlanckA(i, j) = C1 / (Lambda(i, 1) ^ 5 * (Eee ^ (C2 / (Lambda(i, 1) * Temperature(1, j))) - 1#))
    End If
Next j
Next i
Planck = PlanckA
End Function

to return the full array:
Enter the function in cell B2:
=Planck(A2:A8, B11)
Select the range A28
Press F2
Press Ctrl-Shift-Enter

Any problems, please ask

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Kalie]

IDS thanks!

This vba function is work perfectly! and works something like MMULT() in spite of unequal number of rows and columns needed to multiply the matrix.

Only this function gives quite different outcome than the original function.
(Please see excel attachment with both tables and corresponding log-log plot)

Since I am very new to vba it is not easy to trace vba error. Well the formula by itself is oky. So I think something is wrong with the vba code.

Any suggestions how to get this straight?

 

[IDS]

Kalie

I assumed that your x_vals were temperature and the y_vals were lambda, but it seems that it should have been the other way round.

Corrected function is attached.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Kalie]

IDS
great job
thank you!