Monthly Archives: December 2013

List of links to my Internet Stuff

Leave a reply

For posterity, here is a list of links to articles/files I have published at various Internet sites. Be aware that these links may not be permanent.

Archive of Excel Experts E-letter:

http://www.j-walk.com/ss/excel/eee/index.htm ‘closed down – link no longer good

List of my PowerPivot articles at:

http://powerpivot-info.com/authors/David-Hager

See my entry at Steve Bullen’s Excel MVP web page:

http://www.oaltd.co.uk/MVP/Default.htm

Download my Excel games at:

http://www.cpearson.com/excel/games.htm

“New” articles at Daily Dose of Excel

http://dailydoseofexcel.com/archives/2013/08/07/calculating-the-number-of-unique-items-in-a-delimited-string/

http://dailydoseofexcel.com/archives/2005/04/25/automating-mappoint/

Using Functions in Sheet Modules in Excel

Leave a reply

In Excel, worksheet modules can contain sub procedures that will run just like any other VBA procedure. The same is not true for function procedures. If you add the following function to the sheet module of Sheet1 and attempt to use it as a function in Sheet1, it will not be recognized (#NAME error).

Function Mult(a, b)

    Mult = a * b 

End Function

Indeed, it will not even show up in Excel’s intellisense when you try to type the function name in a cell.

However, the sheet level function can be recognized by a sub or function procedure that resides in a general module. So, for example, if the function shown above is placed in the sheet modules for Sheet1, Sheet2 and Sheet4 and cells A1 & A2 on those sheets contained values of (1,2), (3,4) and (5,6) respectively, the following function in a general module

 

Function MultResult(m, n)

On Error Resume Next

For Each wks In ThisWorkbook.Worksheets

      MultResult = MultResult + wks.Mult(wks.Range(m), wks.Range(n))

 Next

End Function

will return the value 44 when entered in a cell as =MultResult(“A1”,”A2”).

A potential advantage can be gained by using this methodology since the sheets containing the functions in their sheet modules do not have to be in any particular position in the workbook for this technique to work. This is just a basic example of what can be done to take advantage of this Excel “quirk” in a productive way.

DAX measure for WTD calculation

Leave a reply

The following DAX measure will return the week-to-date total from a PowerPivot table containing only a Date and SUM column. No calendar table or other calculated fields are needed with this measure.

WTD:=CALCULATE(SUM([Sum]),DATESINPERIOD(Table1[Date],LastDate(Table1[Date]),-WEEKDAY(LastDate(Table1[Date])),DAY))