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

							

		


		
	

	
			

			
Using the Strand7 API


			

				Posted on 23/09/2011 by dougaj4			


					

				
Strand7 is a general purpose Finite Element Analysis program, which has an Application Programming Interface (API), allowing external programs to interact with Strand7, including reading and writing to data files, running analyses, and reading the results.  The API is a comprehensive program, allowing control of almost all aspects of the software, but it is also very complex.  The manual consists of over 1000 pages of detailed technical information for instance.  To make the API more useable in a design environment I have written an Excel based front end, allowing the main functionality of the API to be accessed without programming, through a series of VBA routines and User Defined Functions (UDF’s).  A demonstration version of this spreadsheet may be downloaded from S7 API Tools.zip.  Unlike the other software on this site, this spreadsheet is intended for demonstration purposes only, and all the VBA code is hidden.  Future post on this blog will however cover the main elements in setting up an API application, including open source code.


The features of the spreadsheet are listed in the two screenshots below.


S7 API Tools – Introduction


Contents


The following screen shots illustrate the process of importing a Strand7 data file (using the Suspension Bridge example included in the Strand7 Bonus Models pack), modifying the properties of the main suspension cables over a range of values, running the analysis and importing deflection results into Excel:


List the data files, and import from data file 1


Node listing from imported data


Enter formulas to modify beam properties of the required range


Deflection results imported into Excel.


Strand7 Results View, showing deflections with 50x magnification.

							

	
			

													
						Posted in Excel, Finite Element Analysis, Frame Analysis, Newton, UDFs, VBA					
					|
				
								
					Tagged , , , , , 				
				|
				
				7 Comments