I haven't done anything that big in excel.

You can create arrays in vba that don't have the column limit.  You just can't send the full matrix to an excel sheet (unless you break it up first).

There may be some limits on size of matrices that can be handled by Mmult and Minverse (my excel 2000 has problems when you get up above 50x50), but those limits don't exist if you use the Alglib routines - some hints at Doug's website.

I would say, working with matrix algebra in vba takes some getting used to.  You sort of have to build the tools or at least spend a lot of time getting the tools to do what you want.  Not as easy as Matlab to build matrix equations... but certainly can be done.

=====================================
(2B)+(2B)'  ?

By the way, what kind of F.E. are you looking to do?  I'm sure members might be able to suggest some alternatives to excel.

=====================================
(2B)+(2B)'  ?

The problem would be a 2D in plane model, orthotropic material, and I would probably use a 3-noded tri or 4-noded quad.

I can think of many "better" ways to do it than in Excel to get an answer.  But I am looking to create software that can be redistributed and easily used by anyone.  This really limits the playing field.  It would be part of a suite of tools that I am developing for composite structural analysis.

Excel is not very friendly for programming these types of the things.  But the final product is "better" due to the user interface and the fact that almost everyone has Excel.  Compared to a mathematical software or FEM software, I think most people would rather have an Excel file where they just data in some cells and get a result.  The other options put a lot more burden on the end user.

Brian
www.espcomposites.com

P.S. I am concerned about the ability of MINVERSE to work on something as large as 2k x 2k, as you mentioned.  It won't be very densely populated and would have a small bandwidth though, so perhaps that would help?  Direct matrix inversion should be possible still...as opposed to the need for an iterative solver.

Brian
www.espcomposites.com

Brian - I have an Excel based frame analysis package which can be downloaded from my blog:

http://newtonexcelbach.wordpress.com/2009/05/11/frame-analysis-with-excel-%e2%80%93-7-shear-deflections-and-support-displacements/

The spreadsheet in the link does the matrix work in VBA, but earlier versions have "on spreadsheet" solutions, if you want to follow it through from the basics.

It's beams elements only, but it should be fairly easy to convert to plate elements.

You can also find Excel versions of some ALGLIB matrix routines, both VBA and compiled versions, which should be much more efficient than the routine I have used in the frame program.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

You wouldn't use columns of data to store the values but internal arrays. In the same way, you wouldn't use matrix inversion but use the fact that the global stiffness matrix will be banded. This will reduce the size of the arrays and also reduce the run time. You might need to renumber the nodes in order to optimise the mesh and reduce the bandwidth of the equations.

Another aspect of using FE is never to use 3 noded elements as the results will be useless. If you can get an automatic mesher then use 4 noded or 6 noded (quadrilateral) elements.

By the way, excellemt spreadsheet Doug. The only suggestion I would make is to draw the deflected shape using the calculated rotations,  as well as the translational displacements, to draw a cubic between the two nodes of an element. I've done it for 1D beams but not for 2D beams, yet.  

Tata  

Tata,

We are on different pages here.  You are talking about developing a robust FEM code.  That is not my objective.  I am looking for a simple solution and run times will be relatively short so optimization is not needed.  As I mentioned early, I am familiar with the concept of bandwidth, etc. but again that is simply not necessary and would ultimately be more time consuming that it is worth.  You are talking about something that would be done by FEM developer.  Before I go that route, I would just pass the Excel mesh to a 3rd party solver and read the data back in.

As far as 3 noded tri's go, they are not "useless", provided you understand the pitfalls of such an element.  This seems to be a myth that I have seen perpetuated for many years.  Compared to other choices in advanced FEM software, a 3-noded tri might not be the best choice, but is is a viable element for the problem I am trying to solve.  The problem with the 3-noded tri is that people may use it without understanding it's shortcomings and then blame the element when it fails to produce an acceptable result.  In actually, it is just being used outside of it's acceptable domain.  Anyway, that is getting off topic.
 

Brian
www.espcomposites.com

Quote:

The only suggestion I would make is to draw the deflected shape using the calculated rotations,  as well as the translational displacements, to draw a cubic between the two nodes of an element. I've done it for 1D beams but not for 2D beams, yet.   

It's on the "to do" list :)

I have a splinebeam function that analyses continuous beams by fitting a cubic spline to a unit deflection at each loaded point, then scaling and adding the results so I will probably adapt that.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

Doug,
I'm trying to follow your code but only being at the basic level of excel VBA am having trouble. Things like "selectranges reselect" and other terms are beyond me. The help section in excel is useless when you do a search for such stuff and google doesn't give much help from the other forums around. Do you recommend any books that describe these things that seem specific to excel rather than Visual Basic in general?

Tata  

Corus - I find John Walkenbach's books strike a good balance at explaining advanced techniques without assuming a high level of prior knowledge, but without becoming tedious by spelling out every last little detail.

If you have time, I'd appreciate it if you could list where you found things that didn't make any sense to you, as I'd like to make the explanations comprehensible to people like yourself, who have a good knowledge of Excel, but not a detailed knowledge of VBA.

I might perhaps do a short series of posts on the best way to transfer data from the spreadsheet into VBA and back again.  That's really the main area where my programs differ from straight VB, since I make little use of forms and other Excel specific objects.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

Corus - I found the routine that you were referring to.  I can see how its purpose might not be blindingly obvious to someone who hadn't written it :).  Essentially what it does is either re-use ranges that have been saved from a previous run, or prompt the user to select four new ranges (as listed in the PrompText array).

I'll put it on my to-do list to write a post going through that routine step by step (but my to-do list is fairly full, so it might be a few days before it appears :))

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/