All posts by xldynamic

PowerPivot & DAX & Data Explorer BlogRoll – 31st March 2013

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 31st March 2013.

 

SQLBI
Checklist for Memory Optimizations in PowerPivot and Tabular Models – bit.ly/17aAaNr

Beyond Rational
MS BI # 85 – PowerPivot #2 – Download , Install , Connect to SQL Server and Getting Started – bit.ly/14BHhyJ

Bacon Bits
Use Data Explorer to Hack into Very Hidden Sheets – bit.ly/ZIcNqk

readwrite enterprise
Microsoft’s Data Explorer: Picking Up Where Bing Leaves Off – bit.ly/16XQXDd

Matt Masson
Consuming CANSIM Datasets using Data Explorer – bit.ly/10llbL8

PowerPivotPro
Always Show Yesterday, Today, or Tomorrow’s Data – bit.ly/14KrjCL

Lightswitch For The Enterprise
Self Reporting via PowerPivot In Highly Deferred Execution Mode – bit.ly/VtkjBj

Data Explorer
Data Explorer Online Search Overview – bit.ly/10eJLfx

PowerPivotPro
New Version of Data Explorer Released – bit.ly/16il19t

Katie & Emil Newsletter
Add-in Data Explorer and Excel tutorial – conta.cc/XDYIHw

SQL blog.com
BI Beginner: Data Explorer is a must have – bit.ly/YBl7bO

Gobán Saor
M’s back – Create a PowerPivot Time Dimension using Excel’s Data Explorerbit.ly/ZqJVmq

Gobán Saor
The Model of a very modern PowerPivot – without the add-in – bit.ly/YDgRFE

PowerPivot & DAX & Data Explorer BlogRoll – 24th March 2013

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 24th March 2013.

 

Romi’s Stream
Data Explorer for Excel: An experiment with Sachin Tendulkar – bit.ly/1188Lrc

Kasper de Jonge PowerPivot Blog
NBA team spending and their results with Excel, PowerPivot and Data Explorerbit.ly/10sJuJo

SQLServerCentral.com
Stairway to PowerPivot and DAX – Level 4: The DAX BLANK() Function – bit.ly/Zllw1a

BaconBits
5 Cool Things You Can Do With Data Explorerbit.ly/Ydiorz

LessThanDot
How I used Data Explorer to create a demo – < a href=”http://bit.ly/10hIYhi”>bit.ly/10hIYhi

PowerPivotPro
ISFILTERED: A Better Way to Detect Totals – bit.ly/ZRvCon

Brent Greenwood’s BI Blog
Pareto Chart with PowerPivotbit.ly/14cfXXs

Gobán Saor
[Updated]Look’s like Star Schema is back on the menu! – bit.ly/YBcB8Z

Chris Webb’s BI Blog
UK/US Date Format Bug in PowerPivot and SSAS Tabular – bit.ly/WMd8Jy

Romi’s Stream
Data Explorer for Excel: An experiment with Sachin Tendulkar – bit.ly/1188Lrc

Machlis Musings
New Excel Data Explorer add-in bolsters data analysis – bit.ly/16Uah4g

MSSQLTips
Creating Hierarchies in PowerPivotbit.ly/X8uAZS

PowerPivot & DAX & Data Explorer BlogRoll – 17th March 2013

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 17th March 2013.

 

MS Office Blog
Learn about Data Explorer Formulas – bit.ly/118wr2d

Javier Guillén Pervasive Business Intelligence
PivotStream and Mariner join forces to deliver PowerPivot solutions – bit.ly/118uzqc

Chris Webb’s BI Blog
Finding Shakespeare’s Favourite Words With Data Explorerbit.ly/133FUcz

Erik Svensen
How to use Data Explorer for Excel to extract data from Statistics Denmark – bit.ly/YKjCag

MSDN Blog
A new build of Data Explorer, and an Auto Update feature – bit.ly/WwuR7P

SSIS Junkie
M Lives! – bit.ly/10JyB8r

Gobán Saor
DAX’s back – Create DAX Measures in Excel 2013 without PowerPivot Add-in – bit.ly/XjZZn4

Kasper de Jonge PowerPivot Blog
What is eating up my memory the PowerPivot / Excel edition – bit.ly/113zmpl

Some Random Thoughts
Unpivoting Data in Data Explorer- bit.ly/118xIGy

Javier Guillén Pervasive Business Intelligence
Configuring “drill to details” behavior in PowerPivot: Part 1 – bit.ly/YlDZbE

PowerPivotPro
PowerPivot on Airplanes and NBC – bit.ly/142RIen

PowerPivot & DAX & Data Explorer BlogRoll – 10th March 2013

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 10th March 2013.

 

Extended Results
Calendar Analytics – bit.ly/VYvfLU

PowerPivotPro
PowerPivot Discovers a New Form of Communication in the Animal World, Makes Headlines in Science! – bit.ly/YhOPhN

Matt Masson
Using Data Explorer with Power View – bit.ly/XYGt2H

Customer Feedback SQL Server Content Samples
Data Explorer: An introduction to table relationships and the Expand operation – bit.ly/XWWtlN

PowerPivotPro
“Introducing” Data Explorerbit.ly/Z4BSdv

Chris Webb’s BI Blog
Data Explorer–Where Does The Real Work Get Done? – bit.ly/VOkkVc

Dynamics NAV Financials
PowerPivot to the People – bit.ly/YtSzzK

PowerPivotPro
A funny thing happened in Redmond… – bit.ly/12rBmfH

Gerhard Brueckl’s BI Blog
Another Post about Calculating New and Returning Customers – Part 2 – bit.ly/YKRMHL

Chris Webb’s BI Blog
Calling A Web Service From Data Explorer, Part 2 – bit.ly/Y9gdi1

Vignesh C
Consume csv with Data Explorerbit.ly/14lWZcN

Matt Masson
Dynamic Lookups With Data Explorerbit.ly/Vw6Zkg

Some Random Thoughts
Introduction to Data Explorer Preview for Excel – bit.ly/ZaWrDt

Dan English’s BI Blog
Installing Data Explorer Preview & Demo with IMDB Data – bit.ly/W0vvJe

Chris Webb’s BI Blog
Calling A Web Service From Data Explorer, Part 1 – bit.ly/WErC9X

If One Is Not Enough, …

It is generally accepted that it is a good programming practice to not use monolithic code, but to break functional units of code into separate subs or functions and call these from a control procedure. Functions are very useful for passing values back to the calling procedure, but functions return a single value so what if you want to get more than one value returned to you calling procedure?

I have often seen this topic raised in the Excel forums, a statement that a function cannot return multiple values, or a question on how to return multiple values. There are a number of ways to return multiple values, here I will show 3 such ways, with 2 variations on the last one.

The ways that I will cover are:

  • Classes
  • ByRefArguments
  • UDTs
    • Returning a UDT
    • UDT as an Argument

Some of the codeI includes basic error handling to illustrate the point made earlier.

 

Classes

The first example here is using classes. Classes have properties that can be read, write, or read-write, they can have as many as you need, and they have methods which are essentially functions within the class. So it should be clear that by using a class, our control procedure can call a procedure that returns multiple values; any of the properties or functions could set any number of other properties; a call to a class can then read those properties to get multiple return values. An example class and calling procedure is shown in Figure 1.

'---Control procedure
Public Sub MultiplesClass()
Dim cls As clsPerson

    On Error GoTo errhnd
    Set cls = New clsPerson
    cls.Name = "Jack"
    cls.DoB = DateSerial(1976, 12, 3)
    MsgBox cls.Name & " aged " & cls.Age & " years"

teardown:
    Set cls = Nothing
    Exit Sub
    
errhnd:
    MsgBox Err.Number & "," & Err.Description
    Resume teardown
End Sub

'---Class code
Private mAge As Long
Private mDoB As Date

Public Name As String

Public Property Let DoB(Value As Date)
    mDoB = Value
    mAge = Year(Date) - Year(mDoB)
    If DateSerial(Year(Date), Month(mDoB), Day(mDoB)) >= Date Then
    
        mAge = mAge - 1
    End If
End Property

Public Property Get Age() As Long
    Age = mAge
End Property

Figure 1 – Using a Class to Return Multiple Values

Byref Arguments

Perhaps the easiest way to return multiple values is to use arguments of the function to return amended or changed values. This method can use the return value to pass the function success status with the arguments returning the business variables. The one thing to note is that arguments that will be used to return values must be passed ByRef.

 

Figure 2 shows an example of returning multiple values using function arguments, also demonstrating the error handling.

 

Public Function MultiplesArgsCaller()
Dim myName As String
Dim myAge As Long
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    myName = "John"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman
    
    myName = "Jane"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman
    
    myName = "Damien"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = myName & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesArgsCalled( _
    ByVal Name As String, _
    ByRef GenderType As String, _
    ByRef Age As Long) As Boolean

    MultiplesArgsCalled = True
    Select Case Name
        Case "John"
            GenderType = "bloke"
            Age = 45
        Case "Jane"
            GenderType = "woman"
            Age = 36
        Case Else
            MultiplesArgsCalled = False
    End Select
End Function

Figure 2 – Using a Function Arguments to Return Multiple Values

Returning a UDT

The third and fourth methods use user defined types (UDTs).

UDTs can be seen as mini, very basic, classes. They are a set of data elements within an encompassing object, any instance of a UDT will inherit all of those data elements. The elements within a type can be strongly type with the uual data types, or default to Variant. Interestingly, an elament within a UDT can be defined as a previously defined UDT, types within types, but that is another topic.

As with function arguments, the elements within a UDT can be used as read, write or read-write data elements, the values that are passed to the function can be separated from the values returned, or a value could be passed to the function, amended ther, and the changed value returned in the same UDT element.

The first of these two methods uses a UDT for the function type. Figure 3 shows an example of returning multiple values using a UDT as a function type.

Public Type Person
    Gender As String
    Age As Long
    Success As Boolean
End Type

Public Function MultiplesUDTCaller()
Dim myName As String
Dim thisPerson As Person
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    myName = "John"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender
    
    myName = "Jane"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

    myName = "Damien"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = myName & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesUDTCalled( _
    ByVal Name As String) As Person

    MultiplesUDTCalled.Success = True
    Select Case Name
        Case "John"
            MultiplesUDTCalled.Gender = "bloke"
            MultiplesUDTCalled.Age = 45
        Case "Jane"
            MultiplesUDTCalled.Gender = "woman"
            MultiplesUDTCalled.Age = 36
        Case Else
            MultiplesUDTCalled.Success = False
    End Select
End Function

Figure 3 – Using a UDT as a Function Type to Return Multiple Values

Passing UDT as an Argument

The fourth and final method uses a UDT as a function argument. The same considerations apply as when using a UDT as a function type. Figure 4 shows an example of returning multiple values using a UDT as a function argument.

 

Public Type Person
    Name As String
    Gender As String
    Age As Long
    Success As Boolean
End Type

Public Function MultiplesUDTCaller()
Dim myName As String
Dim thisPerson As Person
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    thisPerson.Name = "John"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender
    
    thisPerson.Name = "Jane"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

    thisPerson.Name = "Damien"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = thisPerson.Name & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesUDTCalled( _
    ByRef Them As Person) As Boolean

    MultiplesUDTCalled = True
    Select Case Them.Name
        Case "John"
            Them.Gender = "bloke"
            Them.Age = 45
        Case "Jane"
            Them.Gender = "woman"
            Them.Age = 36
        Case Else
            MultiplesUDTCalled = False
    End Select
End Function

Figure 4 – Using a UDT as a Function Argument to Return Multiple Values

Summary

so there we have it, 4 ways to get multiple values from a procedure. ALl work, so it is a matter of preference or suitability as o which to use. Personally, I don’t use UDTs, my preference is for multiple arguments within a function. Of curse I use classes, but not for this being able to return multiple values, but in situations where a class suits my application design.

As an aside, I don’t use subs, I always use functions. All procedures should always return at least one value, a success or failure value to pass back to the error handler. Using the function return value to pass back a succes indicator does mean that where you do need that function to return some value determined within the function, just think of any type of calculator, the function return value has been used up, so you will necessarily need to pass back multiple values.

PowerPivot & DAX & Data Explorer BlogRoll – 3rd March 2013

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 3rd March 2013.

 

Chris Webb’s BI Blog
Importing Data From Multiple Log Files Using Data Explorerbit.ly/YWPTvA

SSISJunkie
Traversing the Facebook Graph using Data Explorerbit.ly/VWYY6J

Matt Masson
Access the Windows Azure Marketplace from Data Explorerbit.ly/YHwRFq

Altius Consulting
Best Oscar winning Film? My first Data Explorer adventure… – bit.ly/VWYC01

Ian’s SharePoint Blog
SharePoint OData and the Excel Data Explorerbit.ly/160EaiL

Chris Webb’s BI Blog
Public Preview of Data Explorerbit.ly/XPmXlK

Microsoft
Microsoft “Data Explorer” Preview for Excel – bit.ly/Y6WY8M

PowerPivotPro
Toggling Conditional Formatting On/Off via Slicer – bit.ly/XLISNW

SQLBI – Marco Russo
Update to the PowerPivot for Excel 2013 licensing – bit.ly/XLILlB

TechCrunch
Microsoft Launches Updated Office 365 For Business, Adds ProPlus With Full Office Apps And New Small And Medium Business Versions – http://tcrn.ch/YHyaEb

MSSQLTips
Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel – bit.ly/WAQ2kD

PowerPivotPro
How to buy PowerPivot 2013, and the $30 Volume Licensing Workaround – bit.ly/ZYLrzc

MSSQLTips
Combining Data from Multiple Relational Data Sources into One Table in PowerPivot for Excel – bit.ly/13dFUFY

PowerPivot (& DAX) BlogRoll – 24th February 2013

PowerPivot & DAX activity announced on Twitter for the week ending 24th February 2013.

 

InfoWorld
More Office 2013 bait-and-switch revelations – bit.ly/XWHF5d

RAMBLES.IO
A Response To Where Did My PowerPivot Go? – bit.ly/W5RRep

Erik Svensen
Create a dynamic table from PowerPivot data in Excel 2013 without VBA – bit.ly/11Uwsre

SQLBI – Marco Russo
DAX Time Intelligence for 4-4-5 Calendar, ISO Calendar and other custom ones – bit.ly/UDm1nM

PowerPivotPro
Revolutionaries in Redmond! PowerPivotPro Readers Quoted in ZDNet! – bit.ly/YvMxxd

DynamicAccounting.Net
Microsoft peed on my PowerPivot – bit.ly/152Km8G

ZDNet
Can Microsoft bring BI to the masses if the Excel 2013 masses can’t get BI? – zd.net/ZcFt9G

SQLBI – Marco Russo
The Right Version of Excel 2013 for using PowerPivotbit.ly/151oieu

Gobán Saor
Look’s like Star Schema is back on the menu! – bit.ly/Y931eM

PowerPivot (& DAX) BlogRoll – 17th February 2013

PowerPivot & DAX activity announced on Twitter for the week ending 17th February 2013.

 

The Data Specialist
Equivalent of VLOOKUP in DAX–Part I – bit.ly/Xk0FKL

PowerPivot Pro
Hey, Who Moved My (PowerPivot 2013) Cheese? – bit.ly/11OZdWp

PowerPivot Pro
New Beginning – bit.ly/15fNsXV

PowerPivot Pro
Tomorrow is the Last Day for Chandoo Sign-Up! – bit.ly/VUlOOD

The Data Specialist
Bug in PowerPivot 2012 for Excel 2010 – bit.ly/WLhoIh

PowerPivotPro
“Announcing” the Business Analytics Conference – bit.ly/XPDerQ

BaconBits
PowerPivot and PowerView Confusion in Office 2013 – bit.ly/Vpfqws

Technitrain
[Course]Advanced DAX – bit.ly/Vpfqws

Gerhard Brueckl’s BI Blog
Fiscal Periods, Tabular Models and Time-Intelligence – bit.ly/W3SiAH

SQL Chick
Checklist for Knowing Your PowerPivot Model is Complete – bit.ly/YzWEQh

PowerPivot (& DAX) BlogRoll – 10th February 2013

PowerPivot & DAX activity announced on Twitter for the week ending 10th February 2013.

 

PowerPivotPro
Guest Post from Ken Puls: Determine Effective Tax Rate – bit.ly/U2vdlc

PowerPivotPro
When to Use Measures vs. Calc Columns – bit.ly/VQSm8E

SQLBI
First DAX Advanced Workshop in London, May 2013 DAX Tabular SSAS – bit.ly/Y3sx4Y

Excel Hero Blog
Master Excel PowerPivot Now – bit.ly/X0vnXa

Javier Guillén Pervasive Business Intelligence
Developing child-aware KPIs in PowerPivot and BISM Tabular – bit.ly/VDtCyI

Some Random Thoughts
UNION Operation in DAX Queries – bit.ly/XTZa33

ProLogika Forums
PowerPivot Data Refresh in Excel 2013 – bit.ly/XU0wuK

PowerPivot (& DAX) BlogRoll – 3rd February 2013

PowerPivot & DAX activity announced on Twitter for the week ending 3rd February 2013.

 

Chandoo.org
Introduction to DAX Formulas & Measures for PowerPivotbit.ly/126dmx9

Cindy Gross
Hurricane Sandy Mash-Up: Hive, SQL Server, PowerPivot & Power View – bit.ly/WLYQan

Chris Webb’s BI Blog
Office 2013, Office 365 Editions and BI Features – bit.ly/14zvxdt

MSSQLTips

PowerPivot
with Excel 2013 – bit.ly/XenvCk

PowerPivotPro
Stars and Snowflakes and Bears, Oh My! – bit.ly/WrDNdG

SQLBI
Execute a DAX Query on SSAS Tabular in Excel – bit.ly/Y7zHDP

SQLBI
Import Data from Tabular Model in Excel Using a DAX Query – bit.ly/UsB0Sx

SQLServerCentral.com
Stairway to PowerPivot and DAX – Level 2: The DAX COUNTROWS() and FILTER() Functions – bit.ly/TQbVRb

Project Botticelli
[Pay Site]New Video Tutorial by Marco: Querying with DAX – bit.ly/11FJzLY

PowerPivotPro
Mini Post #3 of 3: Are You Using Excel 2013 Yet? – bit.ly/XelcPO

PowerPivotPro
Mini Post #2 of 3: Supporting/Example Files from the Book Available for Free Download – bit.ly/11FHPCz

PowerPivotPro
Mini Post #1 of 3: Interview at SoftwareAdvice.com – bit.ly/11OzlUX

Pyramid Analytics
Pyramid Analytics announces deeper Integration with PowerPivot and SQL Server Tabular Analysis- bit.ly/WXSbcV

Technet
Tutorial: Optimize a Sample PowerPivot Model for Power View Reports – bit.ly/UssbZ1

BurningSuit
What is PowerPivotconta.cc/VGpoMe

Salvo(z)
Currency Conversion in Tabular Model Using DAX – http://bit.ly/118W7LG

PowerPivot-info.com
PASS Business Analytics conference – Chicago, IL – April 10-12, 2013 – bit.ly/WpEGT8

Gerhard Brueckl’s BI Blog
Dynamic ABC Analysis in PowerPivot using DAX – bit.ly/T3PBSv