Excel VBA using GetPivotdata is returning #VALUE

[Mad!Dog]

Hi Experts<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have setting up a customer Excel function in VBA that gets data from a Pivot table.<o></o>
The purpose of the function is to return the value from a pivot table like Excel GETPIVOTDATA function but with a special condition if the return value is #VALUE. If I do this with Excel only I have to nest the function into an IF statement that check is the return value is and error (e.g #VALUE). If it is then the return value should be Zero.<o></o>
This is what the Excel function looks like:<o></o>
=IF(ISERROR(GETPIVOTDATA("_YTD",Pivot!$A$3,"Account","PL1000","Period","1012","Actuality","AC","Company","00031","Currency","NZD"))= True, 0, GETPIVOTDATA("_YTD",Pivot!$A$3,"Account","PL1000","Period","1012","Actuality","AC","Company","00031","Currency","NZD"))<o></o>
I have am trying to include in the function the IF statement logic so the users don’t have to build a long and complex function. <o></o>
The problem I have is that the return result is #VALUE if the data is not in the Pivot. I cannot get the function to see this error and return 0.<o></o>
The function is successful if all the parameters are met.<o></o>
This is my function code:<o></o>
Private Function ccufGetValx(Period As String, Optional Par2 As String, Optional YTD_Mnth As String, Optional Actuality As String, Optional CompCode As String, Optional Structure As String, Optional Par4 As String, Optional ParCurrency As String, Optional AccCode As String, Optional Par5 As String, Optional Par6 As String, Optional Par7 As String, Optional Par8 As String, Optional Par9 As String, Optional CloVer As String, Optional Par10 As String, Optional ConVer As String) As Double
Dim b As Double<o></o>
Dim PT As PivotTable
Set PT = Worksheets("Pivot").PivotTables(1)
'Get the value from the pivot table<o></o>
If IsError((PT.GetPivotData("_YTD", "Account", AccCode, "Period", Period, "Actuality", Actuality, "Company", CompCode, "Currency", ParCurrency).Value)) = True Then<o></o>
b = 0<o></o>
Else<o></o>
b = (PT.GetPivotData("_YTD", "Account", AccCode, "Period", Period, "Actuality", Actuality, "Company", CompCode, "Currency", ParCurrency).Value)<o></o>
End If
ccufGetValx = b
End Function

 

[taigovinda]

Welcome to the board!

Sounds like you are doing a lot of work to achieve a pretty limited task... What version of Excel do you have? If 2007 or above, try using the much more flexible IfError() function which also allows you to avoid repeating your getpivotdata() function.

Or, for a user defined function that operates just like iferror(), see here:http://www.excelforum.com/excel-general/693584-iferror-for-2003-a.html

Hope that helps.
Tai

 

[Mad!Dog]

Thanks for the help.

I struggled to get the IFERROr function to work, but while I was reading the article you directed me to, I worked it out.

This is how I changed my code and solved the problem:

Private Function ccufGetValx(Period As String, Optional Par2 As String, Optional YTD_Mnth As String, Optional Actuality As String, Optional CompCode As String, Optional Structure As String, Optional Par4 As String, Optional ParCurrency As String, Optional AccCode As String, Optional Par5 As String, Optional Par6 As String, Optional Par7 As String, Optional Par8 As String, Optional Par9 As String, Optional CloVer As String, Optional Par10 As String, Optional ConVer As String) As Double

Dim b As Double
Dim PT As PivotTable
Set PT = Worksheets("Pivot").PivotTables(1)

'Get the value from the pivot table

ccufGetValx = 0

On Error GoTo ErrorHandler

ccufGetValx = PT.GetPivotData("_YTD", "Account", AccCode, "Period", Period, "Actuality", Actuality, "Company", CompCode, "Currency", ParCurrency).Value

ErrorHandler:
Resume Next

End Function

It works weel and returns Zero to Excel if the the Pivottable does not have data for the combination of data element (parameters in my function)

Could my code be made more efficient?

Thanks
Mark

 

[taigovinda]

Hi Mark,

I guess I still don't see why you don't just use the built-in iferror() (if you have 2007 and up) or the udf one (if you don't). Essentially, you've built a shell for getpivotdata()...

Maybe my brain is just a little too feeble to manually construct getpivotdata() arguments, but when I use that function I like to do it by typing "=" and then clicking on a pivot table cell, then replacing the desired parts of the autogenerated getpivotdata() with cell references.

Anyhow, your code might be ever so slightly more efficient if you eliminated variables like this:

Private Function ccufGetValx(Period As String, Optional Par2 As String, Optional YTD_Mnth As String, Optional Actuality As String, Optional CompCode As String, Optional Structure As String, Optional Par4 As String, Optional ParCurrency As String, Optional AccCode As String, Optional Par5 As String, Optional Par6 As String, Optional Par7 As String, Optional Par8 As String, Optional Par9 As String, Optional CloVer As String, Optional Par10 As String, Optional ConVer As String) As Double
 
'Get the value from the pivot table
 
ccufGetValx = 0  'technically this line might not be necessary - the default value for a double should be zero...  but any performance cost to make sure is certainly more than worth it
 
On Error Resume Next  'not sure this aids performance but you don't need an errorhandler since you just want to run through all the code in any case
 
ccufGetValx = Worksheets("Pivot").PivotTables(1)
.GetPivotData("_YTD", "Account", AccCode, "Period", Period, "Actuality", Actuality, "Company", CompCode, "Currency", ParCurrency).Value
 
End Function

 

[Mad!Dog]

Thanks for your reply.

I agree with you about how you do the GetPivotdata using the '='. I do the same myself normally. The reason I can't do it in this case is because of a deeper need I have.

This is part of a grander plan I have.

Our company uses software that connects Excel to a SQL corporate database using a specialised query connection tool. This software has an Excel add-in that has a set of custom Excel function which fetch data from the database using their software custom formula. The parameters of the function are what the user needs to use to get get data from the server's database.

The problem: The user must be connected to the corporate network for the these funtions to be able to reteive data and populate Excel. We have many mobile staff that cannot be connected all the time.
My solution: My idea is for the users to refresh an SQL query into the pivot table in the spreadsheet before they disconnect from the server. Then I have a VBA macro that converts all their Corporate Software Funtions into my functions. The macro simply renames the corporate function into my function and therefore repoints their function to my Pivot table, which now hold the SQL for their branch in the Pivottable data.

My functions are exactly the same the Server's funtions except I have changed 1 letter in the funtion. (e.g Server funtion = cc.fGetVal and My function = ccufGetVal.) The paremeter are the same, which already point to references in the sheets.

This approach also helps for users in areas that have bad VPN/Web connection as the Corporate function is extremely slow. My Excel SQL refresh take max 30 seconds vs the corporate refresh that takes 2-6 minutes per sheet in excel each time the F9 key is pressed to refresh data. Very annoying for users.

I hope that helps explain the background.

I have tried your suggested changes but it still seems to return a #Value in Excel if data is not found in the Pivot table. I need it to return a Zero in that case. The iferro function seems to not work and the cell still haa a #value. This is how my code looks now and it seems to work.

What I would like in addition to this is for the function to return an error code if the Company or Account parameter does not exist in the pivot list. I have used vlookup to check this against a master list of companies and accounts from the database that is now stored in Excel tables. In Excel it returns a #NA if the code does not exist. I have tried to include this the function but I have not succeded.

I want the function to first check if the Account or Company is valid and if not, return a text result "Invalid Company" or "Invalid Account". and then stop looking in the pivot table as we know that the value wull not be found. I have tried using On error logic but I can't work it out.

Can you give me some guidance on this:

The code should run as:

1. look at the company list and if the company exists continue, else return error and Exit funtion
2.look at the account list and if the account exists continue, else return error and Exit funtion
3. look in pivottable for the value and if the value it is in the pivot table return the value Else return 0 exit

Thanks so much for helping me with this. (My brain hurts)