Amazing DAVERAGE

 
Q: How can I average values in a column for different sub-set rows of data dependant on values in other columns?

DAVERAGE is a very powerful Excel function. It uses capability very much like the advanced autofilter to elect which rows of a "database" (just data in rows and columns with headings) to average. The call looks like answer=DAVERAGE(DataRange, ColumnNumber(or name) to average, Critera range). The criteria range can be a range containing complex "and'ing or'ing, or relational operators (>, < etc). When called from within VBA (answer=application.worksheetfunction.DAVERAGE(DataRange, ColumnNumber(or name) to average, Critera range), this function provides a powerful sub-setting and averaging method. I've recently used it to provide all the interaction levels for a Tagucci L16 experiment.

Multi-Fill: Did you know?

  
Q: How can I fill multilpe cells at a time with a constant or an equation?

One can add equations (or constants) to multiple cells at the same time by highlighting the cells into which the equations will be placed, entering the equation in the formula bar,
and pressing < cntl > < enter > 

Playing together: Excel and XML

Q: What is XML?

XML (extensible markup language) provides an environment in which data objects ("tags") can be a priori defined to have specific characteristics, and relationship to each other. It is comparable to defining a database of variables. The definition file (myXMLdefinitionfile.xsd) is called a "schema" definition file (.xsd stands for XML schema definition file). Schema is just another name for a model or template.

Q: What does this have to do with Excel?

Excel 2003 and later versions have built-in capability to operate with .xml files (edit, import, export and the like). The .xml file can be "mapped" to an Excel spreadsheet. This "mapping" allows an Excel model, or spreadsheet calculation to operate on data from arbitrary sources - as long as the schema for the data exists. This approach was used in a recent application in which the same data was in different places in different versions of an Excel data template.

Recurse Me

Q: What is recursion, and how can it be used?

Recursion occurs when a subroutine or function "calls itself". This is most useful for situations where the same code must be applied multiple times to a number of different data objects.

The classic example is to solve the problem y=n! (read y equals n factorial). The solution is to mutliply n first by (n-1), then multiply the result by (n-2) and so on until only "1" is left as a factor. One can readily see that the first factor is to be multiplied by (n-1)!  This can be accomplished with recursive code outlined below:

Sub TryNfactorial()
n = 3
y = Nfactorial(n)
MsgBox (y)
End Sub

Function Nfactorial(n)
If n > 1 Then Nfactorial = n * Nfactorial(n - 1) Else Nfactorial = 1 ' while n > 1 - keep going deeper
End Function

A powerful additional use of recursion is to create a recursive VBA function which deals with stored files in multiple folders in a folder hierarchy. For example - say we wanted to add a plot to all .xls files which had "DataToPlot" in the filename. Below is an outline of this code.

1) Use MsofilePicker to ask the user for the first node of the structure of interest
2) Use the filescripting object to create the folder, sub-folder and files objects for the current node
3) If there are more folders at this node - call the same (now recursive) routine again to shift the node to a new (deeper) location
4) When there are no more folders (only files) - process the files - check if they are .xls and have "DataToPlot" in the name (with the "Like" statement) - if so open, create a plot, and save (and return)

We have applied this approach to several different Windows-based file modifications successfully. See our website for more information:

http://www.TechnicalAutomationServices.com/

Multi-page format changes

Dear MacroGenie - "How can I change the format of the same range of cells on several selected sheets?"

MacroGenie: The formatting of spreadsheet cells, the zoom level, and many other characteristics can be modified at once. Select the cells of interest on the first worksheet, then select additional worksheets with on the sheet names. Make the desired change, and all sheets will be updated.

MacroGenie Leaves The Lamp

Welcome master,

I have joined your world to assist you with your Excel bidding. In the coming years I shall convey useful Excel tidbits to make your life easier. Ask of me what you wish....

MacroGenie