… with video directed by Kerinne Jenkins
(Click on Vimeo to visit site, or click play, then full screen button:
Dancing in Silence – The Former Love from Kerinne Jenkins on Vimeo.
Recent Comments
Getting the address of a selected range
The Excel Indirect() function allows other functions to use the text in a worksheet cell to define a range, rather than selecting the range, or entering it directly into the function. For instance, if cell A1 contained the text B1:B10, then the function =Sum(Indirect(A1)) would return the sum of the values in B1:B10.
It would be convenient if there was a built in Excel function that returned the address of a selected range, but there isn’t. It is possible to create the address using a combination of functions and text strings, but in my opinion a much simpler method is available through a very short piece of VBA:
Function RngAddress(Rng As Range) As String RngAddress = Rng.Address End Function
Pasting those three lines into a code module in the VB Editor will create a RngAddress function that will return a text string with the address of a selected range.
But having done that, wouldn’t it be nice if we could also just select the top left hand corner, and enter the number of rows and columns we wanted. Also being able to (optionally) return the worksheet name would be useful. Another few lines will do what we want:
Function RngAddress(Rng As Range, Optional NumRows As Long, Optional NumCols As Long, Optional SheetName As Boolean) As String
' If you want this function to update with every worksheet change, then un-comment the line below
' Application.Volatile
If NumRows = 0 And NumCols = 0 Then
RngAddress = Rng.Address
Else
If NumRows = 0 Then NumRows = Rng.Rows.Count
If NumCols = 0 Then NumCols = Rng.Columns.Count
RngAddress = Rng.Cells(1, 1).Address & ":" & Rng.Cells(NumRows, NumCols).Address
End If
If SheetName = True Then RngAddress = "'" & Rng.Worksheet.Name & "'" & "!" & RngAddress
End Function
The entire code for this function, together with some examples can be downloaded from: RngAddress.xlsb
And this is what it looks like:
Newton Excel Shakespeare
Another excellent link from Al Vachris:
This site is all about how to free your Excel data from your desktop and take advantage of web capabilities such as Docs, Maps, Earth , Gadgets, Visualizations and a whole bunch of other services . Along the way, you’ll see a few techniques, tips and tricks as well as fully functional sample applications in Vba and javaScript. All examples are downloadable here, and the code is unprotected and free for non commercial re-use.
Click to open site in new window
A random example from the site is the code below, which will let your computer quote Hamlet to you:
Public Sub say()
Dim TextA As Variant, i As Long
Dim sTerm As String, spv As Object
TextA = Selection.Value
Set spv = CreateObject("SAPI.SpVoice")
If IsArray(TextA) = True Then
For i = 1 To UBound(TextA)
sTerm = TextA(i, 1)
spv.Speak sTerm
Next i
Else
spv.Speak TextA
End If
Set spv = Nothing
End Sub
See http://ramblings.mcpher.com/Home/excelquirks/snippets/speak for more details, and download the sample spreadsheet below from Text2Speech.xlsb
Text2Speech.xlsb, download from the link above
Posted in Computing - general, Excel, Javascript, VBA
Tagged Excel, Excelquirks, Link, SAPI.SpVoice, Text2Speech, VBA
2 Comments
4 Year Report
As in previous years, I have downloaded the statistics for this blog for the previous year, and pasted them into a worksheet. The link to each post is preserved in the spreadsheet, so it makes a convenient index to what has been posted over the year. This year I have uploaded the spreadsheet to Skydrive, so you should be able to access the links in the window below, or open the file in your browser or Excel, or download it.
Of the 2011 posts, the most popular overall (by a wide margin) was Using LINEST for non-linear curve fitting. The most popular in the Newton category was Dancing Pendulums, and the most popular in the Bach category was Wonders of the Universe – Kate Rusby
From the “deserving but sadly neglected category” I have chosen:
Newton: Elegant Solutions – completing the square
Excel: IP2 Update, ByRef and ByVal
Bach: The Rough Island Band
Most frequent referrers to this site came from:
Referrers to Newton Excel Bach
Newcomers to the list include Yakpol.net, ColinCaprani.com, Andrewexcel.blogspot.com, and Exceltipsmonster.com. Thanks to those and all other linkers.
Strand7 API GetNode functions
As promised in this post, I have written and documented Excel User Defined Functions (UDFs) to interface with all the Strand7 API functions that return node attributes or results. These 22 functions allow node attributes and results to be read directly from an Excel spreadsheet. Examples and full open source code are included in the download file: GetNode.zip.
To use the UDFs it is necessary to have a licensed copy of Strand7 and the API installed. The spreadsheet may then be linked to up to 12 Strand7 data and results files through the Control Panel sheet:
Add files to file list
Connect to listed files with the “Open Data” button
Function input and output is documented on the “NodeFunc” sheet:
Node Function input and output (click for full size view)
Node Results output details
Further details of the input and output for each function are given in the API Manual. Manual page numbers are given in the UDF function list below:
Node Function List
The functions may be used either by entering the functions directly, in the same way as built in Excel functions, or the GetNodeFunc function may be used to call a function with the name given in a separate cell. Examples of each usage are shown in the screen shot below:
Examples of GetNodeFunc and S7NodeUCS functions
All the functions return an array of results, and must be entered as an array function. Details of the procedure for entering array functions are given here.
The function results array returns results for all the selected node numbers specified in a single column range. Additional parameters (where required) are specified in a separate single column range (with 1-3 rows). Each parameter may be specified as a single value, or as a range address. Where the range method is used the range must be the same length as the node list range. Examples of this method are shown in the screenshot below:
Function input parameter (ResultCase) specified as a range
For example purposes the number of node results has been kept to a small number, but the functions will return results from large files quickly and efficiently. For example, the two screenshots below were generated from the Strand7 sample “Suspension Bridge.st7” file, which has 3528 nodes. The first image was plotted from X,Y coordinates using the standard coordinate system. For the second a new User Coordinate System was created, and the coordinates in this system were read from Excel using the S7NodeUCS function:
UCS1 XY coordinates
UCS2 XY coordinates
The examples in the screenshots above (other than the suspension bridge) were all generated from the file Raft1.st7 included with the download file. Note that this file is for illustration purposes only, and many of the node attributes created for Load case 2 and Freedom case 2 are not appropriate to the non-linear static analysis actually carried out (which uses Load and Freedom cases 1 only). The spreadsheet may be easily linked to any other file(s), for instance links to a selection of the files in the “Tutorials/Help Topics Example Models” folder is shown below:
Link to Strand7 Example Model files
Posted in Excel, Finite Element Analysis, Newton, Strand7, UDFs, VBA
Tagged API, Excel, Node Functions, Strand7, UDF, VBA
5 Comments
Newton Excel Bach, not (just) an Excel Blog 