Search:

# MS Office Forum

Ask Question   UnAnswered
RSS Feeds

# VBA Function in Excel 2000

Asked By: Kevin    Date: Sep 05    Category: MS Office    Views: 2384

I try to write simple code to solve simple frame structure using 6 degrees of
freedom bar elements.

I use Excel functions to assemble stiffness matrix. Inputs are columns of
element properties.
Sheet works OK on Excel 2003 and later. My friends can not run it on Excel 2000.
Problem might be in variable declaration of function k_global_sum input/output.
Inputs are columns of numbers and output is matrix.

Option Base 1 ' Set default array subscripts to 1.

Function k(E As Double, A As Double, i As Double, L As Double)
Dim KK(1 To 6, 1 To 6) As Double
KK(1, 1) = E * A / L
KK(1, 2) = 0
KK(1, 3) = 0
KK(1, 4) = -E * A / L
KK(1, 5) = 0
KK(1, 6) = 0
KK(2, 1) = 0
KK(2, 2) = 12 * E * i / L ^ 3
KK(2, 3) = 6 * E * i / L ^ 2
KK(2, 4) = 0
KK(2, 5) = -12 * E * i / L ^ 3
KK(2, 6) = 6 * E * i / L ^ 2
KK(3, 1) = 0
KK(3, 2) = 6 * E * i / L ^ 2
KK(3, 3) = 4 * E * i / L
KK(3, 4) = 0
KK(3, 5) = -6 * E * i / L ^ 2
KK(3, 6) = 2 * E * i / L
KK(4, 1) = -E * A / L
KK(4, 2) = 0
KK(4, 3) = 0
KK(4, 4) = E * A / L
KK(4, 5) = 0
KK(4, 6) = 0
KK(5, 1) = 0
KK(5, 2) = -12 * E * i / L ^ 3
KK(5, 3) = -6 * E * i / L ^ 2
KK(5, 4) = 0
KK(5, 5) = 12 * E * i / L ^ 3
KK(5, 6) = -6 * E * i / L ^ 2
KK(6, 1) = 0
KK(6, 2) = 6 * E * i / L ^ 2
KK(6, 3) = 2 * E * i / L
KK(6, 4) = 0
KK(6, 5) = -6 * E * i / L ^ 2
KK(6, 6) = 4 * E * i / L
k = KK
End Function

Function t_matrix(c As Double, s As Double)
Dim T(1 To 6, 1 To 6) As Double
For m = 1 To 6
For n = 1 To 6
T(m, n) = 0
Next n
Next m
T(1, 1) = c
T(1, 2) = s
T(2, 1) = -s
T(2, 2) = c
T(3, 3) = 1
T(4, 4) = c
T(4, 5) = s
T(5, 4) = -s
T(5, 5) = c
T(6, 6) = 1
t_matrix = T
End Function

Function k_global_sum(E, A, i, L, n1, n2, c, s)
'Dim ke(1 To 6, 1 To 6) As Double

'Dim ke_global(1 To 6, 1 To 6) As Double
Dim ke_global As Variant
Dim ke As Variant
Dim kk_global() As Double
'Dim T(1 To 6, 1 To 6) As Double
Dim T As Variant
Dim k_size As Integer
Dim el_no As Integer
Dim n_1 As Variant
Dim E_u As Double, A_u As Double, I_u As Double, L_u As Double
Dim c_u As Double, s_u As Double
n_1 = n1

k_size = Application.Max(n1, n2) * 3
el_no = UBound(n_1, 1)
ReDim kk_global(k_size, k_size)
For m = 1 To k_size
For n = 1 To k_size
kk_global(m, n) = 0
Next n
Next m
For u = 1 To el_no
'local stiffnes matrix see Pg.: 266
E_u = E(u, 1)
A_u = A(u, 1)
I_u = i(u, 1)
L_u = L(u, 1)
ke = k(E_u, A_u, I_u, L_u)

'transformation of local to global stifness matrix
c_u = c(u, 1)
s_u = s(u, 1)
T = t_matrix(c_u, s_u)
ke_global = Application.MMult(Application.MMult(Application.Transpose(T), ke),
T)

'index of elemnt DOF in global coord
index_global = Array(3 * n1(u, 1) - 2, 3 * n1(u, 1) - 1, 3 * n1(u, 1), 3 * n2(u,
1) - 2, 3 * n2(u, 1) - 1, 3 * n2(u, 1))
For m = 1 To 6
For n = 1 To 6
kk_global(index_global(m), index_global(n)) = kk_global(index_global(m),
index_global(n)) + ke_global(m, n)
Next n
Next m
Next u
k_global_sum = kk_global

End Function

Share:

### 3 Answers Found

Answer #1    Answered By: Kristin Johnston     Answered On: Sep 05

What error do your friends get? Which line is it on?

Answer #2    Answered By: Beatriz Silva     Answered On: Sep 05

As he opens the excel  sheet it does not execute function  that is
referred on cell b160:dj160.
I says #name?
There are some other functions in sheet that work.
Even this function works, if simplified, but the last assignment not.

Answer #3    Answered By: Yvonne Watkins     Answered On: Sep 05

What function  that is referred to on B160:DJ160? I cannot see any mention
of column B in your previous message, and there is certainly no mention og
DJ or of 160.

You have quoted a huge lump of code. Is that relevant to the question? If
so, how? If not, please ask your question again from scratch and provide
the information that is needed for us to try to work out what is wrong.

Didn't find what you were looking for? Find more on VBA Function in Excel 2000 Or get search suggestion and latest updates.

Tagged: