Spreadsheet Solutions For Structural Engineering

Posted on 07/10/2011 by dougaj4

Another site where the tasks considered suitable for programming in Excel and VBA are anything but “boring and repetitive” is Yakpol’s Spreadsheet Solutions For Structural Engineering:

“This web site contains a collection of engineering spreadsheets for structural analysis and design of concrete and steel structures. The programs have been developed by practicing professional engineers with emphasis on productivity, dependability, and clarity. To insure quality of the spreadsheets we have comprehensively compared analysis results to leading commercial software products and found them to be accurate.”

Those of you not into structural engineering may like to have a look at a few non-engineering applications, including:

Yakpol's Travelling Salesman Spreadsheet

Posted in Beam Bending, Excel, Newton, VBA | Tagged , , , | 2 Comments

More on VBA

Posted on 06/10/2011 by dougaj4

Andy Pope posted the two links below in a comment to my previous post:

A while back they added a VBA developer center to the msdn Office site.
http://msdn.microsoft.com/en-gb/office/ff688774.aspx

And this article on the Excel developer road map is interesting.
http://msdn.microsoft.com/en-gb/office/ff458124.aspx

There seem to be some worthwhile resources there, and the fact that this is a fairly new addition seems to indicate that VBA isn’t quite dead yet.  It’s just a shame it isn’t better integrated with the main MSDN site.

On the other hand, news of other vendors including VBA in their products is not so good.  VBA has been included in Autocad for many years, and also their low cost competitor, Intellicad.  The latest versions of both products come with Visual Studio Tools for Applications (VSTA).  VBA for Autocad can be downloaded as a separate package, but as far as I can see the latest version of Intellicad did not support VBA at all.

I’d be interested to know how many people out there are using VBA/VSTA with these drafting applications.  Is there any backward compatibility with the latest Intellicad?  Is VSTA a good alternative?  Is it a complete integrated system, or do you need Visual Studio as well?

Finally, on the alternative Office front; there is activity at Open Office:

http://wiki.services.openoffice.org/wiki/VBA

VBA interoperability project is a joint Novell & Sun incubator project whose aim is to deliver a way to run Excel and Word macros natively in imported documents.

But

NOTE:  Be aware the idl is not stable, (nor won’t be for the forseeable future be ‘published’) it WILL change, so if you are using a language that has a binary dependency on the interface (like c++ & java) then consider yourselves warned.

Gnumeric also has a VBA project (and has had for several years), but there is little sign of progress:

Scripting. After some vacillation trying to decide between wrapping  Gnumeric’s object model, and providing something that is VBA compatible, the  latter won.  We’ll export an api in C that is compatible with the widely used  VBA interface, and wrap it in several languages (Python, VB.NET, and C# are likely  candidates).

Sounds good, but the heading is “Wish List (long range plans).

And finally finally, with Google Docs and tablet apps it seems that Javascript is the flavour of the month.  Is this the way of the future?  What do you think?

Posted in Computing - general, Excel, VBA | Tagged , , , , , , , | Leave a comment

XLDennis, the MSDN Library, and VBA rant

Posted on 04/10/2011 by dougaj4

Dennis Wallentin, aka XLDennis, is a long time contributor to the on-line Excel community and co-author of the latest version of Professional Excel Development.  In recent years he has concentrated on the use of the VSTO and .NET products in conjunction with Excel, which you can read all about at his blog: VSTO & .NET & Excel, which has now been added to my blog list on the right.

Browsing the blog led me to the Microsoft All-In One Code Framework, which led me on to the MSDN Library.

Now don’t get me wrong, these are excellent resources, and well worth exploring, but what immediately struck me at the Developer’s Library was the invisibility of VBA.  Not surprising, perhaps, that there should be nothing under “Development Tools and Languages”, but surely the most widely used development tool for Office Applications should have a heading of its own under Office Development?   But no, to find a VBA related article we need to go down another three levels to MSDN Library: Office Development: Microsoft 2010: Excel 2010: Technical Articles: where we find:

 Getting Started with VBA in Excel 2010

So what does it have to say?

Microsoft Excel 2010 is an extremely powerful tool that you can use to manipulate, analyze, and present data. Sometimes though, despite the rich set of features in the standard Excel user interface (UI), you might want to find an easier way to perform a mundane, repetitive task, or to perform some task that the UI does not seem to address. Fortunately, Office applications like Excel have Visual Basic for Applications (VBA), a programming language that gives you the ability to extend those applications.

So that’s how Microsoft see VBA these days, “an easier way to perform a mundane, repetitive task, or to perform some task that the UI does not seem to address”.  To be fair, later on they do say:

VBA is not just for repetitive tasks though. You can also use VBA to build new capabilities into Excel (for example, you could develop new algorithms to analyze your data, then use the charting capabilities in Excel to display the results), and to perform tasks that integrate Excel with other Office applications such as Microsoft Access 2010. In fact, of all the Office applications, Excel is the one most used as something that resembles a general development platform. In addition to all the obvious tasks that involve lists and accounting, developers use Excel in a range of tasks from data visualization to software prototyping.

But even here it seems that Excel plus VBA is only something that “resembles a general development platform”.  With this level of lack of excitement from Microsoft is it any wonder that VBA is so often seen as a toy solution that no professional developer would waste their time on?  Now I’m not saying that VBA is the answer to everything, far from it, but neither is it, as often claimed, the second best solution to everything.  For rapid development of convenient and efficient tools for a wide range of numerical analysis problems, in engineering and scientific applications as well as finance, I don’t know of any tool that comes close to a spreadsheet with an integrated programming language; and as the de-facto standard application Excel with VBA is the obvious tool to fill that role.

Nearly forgot; what do you think?  The comment box is just down there.

Posted in Computing - general, Excel, VBA | Tagged , , , | 13 Comments

Inserting a list of names

Posted on 28/09/2011 by dougaj4

Another function that I don’t use much, and that got hidden by the Ribbon, is the command to insert a list of named ranges together with the addresses they refer to.

Here’s where they hid it:

Formulas Tab; "Use in formula" button; down the bottom; - or just press F3

On the Formulas Tab, in the Defined Names box, is the “Use in Formula” button, and if you click that you get a list of named ranges, with “Paste Names…” right at the bottom, and if you click that you get the dialog box shown, which if you click the “Paste List” button will paste a list of all named ranges and their addresses.

Or alternatively just press F3, which brings up the Paste Name dialog, with the paste list button.

Posted in Excel | Tagged , | 1 Comment

More on writing arrays to the worksheet

Posted on 28/09/2011 by dougaj4

Writing a large VBA array to the worksheet in a single operation, rather than cell by cell, can result in a huge improvement of the speed of many macros.  The basic technique was described here.  I recently wanted to modify this technique to write a number of arrays from a loop into adjacent ranges on the same sheet, then adjust the range name to cover all the arrays, so they could be cleared in one operation.  This is the code I came up with:

' Clear all old data
Range("DfileRes").ClearContents
' Reset column offset and maximum number of rows counters, and number of columns per array
DatOff = 0
Maxrows = 0
NumCols = 11

For i = 1 to NumOut
' Fill array "Stressa"
' ...
' Get size of output array; Numrows and reset Maxrows if necessary
' ...
If Numrows > Maxrows then Maxrows = Numrows

' Write array to spreadsheet
Range("DfileRes").Offset(0, DatOff).Resize(NumRows, NumCols).Value = Stressa

' Recalculate column offset value, "DatOff"
DatOff = DatOff + NumCols + 1  ' 1 blank column inserted between each output array

Next i

' Resize output range to cover all data
Range("DfileRes").Resize(Maxrows, DatOff).Name = "DfileRes"

The line that does all the work is:

  • Range(“DfileRes”).Offset(0, DatOff).Resize(NumRows, NumCols).Value = Stressa

This generates a range offset from the top left cell of the range “DFileRes” by zero rows and DatOff columns, with a size of NumRows x NumCols, and writes the contents of the array “StressA” into that range.  Note that a range named “DFileRes” must be created in the spreadsheet  for the macro to work.


							

	
			

													
						Posted in Arrays, Excel, VBA					
					|
				
								
					Tagged , , 				
				|
				
				3 Comments