Excel worksheetfunction.subtotal method throws error in C#

Question

0.00/5 (No votes)

See more:

C#

//Referencing
    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.WorksheetFunction xlWsf;

    //declaring a range

    xlRangeDB = sheet.get_Range("A1", "A1000");

    //declaring a double variable
    double numVal = 0;

    //using subtoal function but throws error!!!
    numVal = xlWsf.Subtotal(3,xlRangeDB);

What I have tried:

Searched google, codeproject website.

Posted 19-Jul-16 23:28pm

NOOSL

Updated 20-Jul-16 2:57am

Add a Solution

Comments

ZurdoDev 20-Jul-16 8:22am

There are over 42,000,000 possible errors. Please provide the exact error so we can try to help.

NOOSL 20-Jul-16 8:41am

@RyanDev, Thanks, The error I got is
System.NullReferenceException was unhandled
HResult=-2147467261
Message=Object reference not set to an instance of an object.

I suspect I am not filling all parameters of the subtotal function that may be a possible reason.
On Microsoft website the the subtotal function is written as:
https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheetfunction.subtotal.aspx

double Subtotal(
double Arg1,
Range Arg2,
Object Arg3,
Object Arg4,
Object Arg5,
Object Arg6,
Object Arg7,
Object Arg8,
Object Arg9,
Object Arg10,
Object Arg11,
Object Arg12,
Object Arg13,
Object Arg14,
Object Arg15,
Object Arg16,
Object Arg17,
Object Arg18,
Object Arg19,
Object Arg20,
Object Arg21,
Object Arg22,
Object Arg23,
Object Arg24,
Object Arg25,
Object Arg26,
Object Arg27,
Object Arg28,
Object Arg29,
Object Arg30
)

ZurdoDev 20-Jul-16 8:46am

The error says that something is null. From the code you posted, that would be xlWsf.

NOOSL 20-Jul-16 8:48am

You mean declaration to use (xlWsf) xlWorksheetfunction
Microsoft.Office.Interop.Excel.WorksheetFunction xlWsf;

ZurdoDev 20-Jul-16 8:49am

Yes, you only declared it, you did not set it to anything so it is still null.

NOOSL 20-Jul-16 8:50am

I didn't know how to set. Can you please help me.

ZurdoDev 20-Jul-16 8:51am

I haven't used this code before so just look for examples online. I can only tell you why you got that error.

NOOSL 20-Jul-16 8:52am

Oh I think like this.
Microsoft.Office.Interop.Excel.WorksheetFunction xlWsf = xlApp.WorksheetFunction;

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

madhav_jain · Accepted Answer · 2016-07-20T02:35:00

Try below code to calculate the sum

xlRangeDB = sheet.get_Range("A1", "A1000");
double numVal = 0;
int rCnt = 0;
int cCnt = 0;
for (rCnt = 1; rCnt <= xlRangeDB.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= xlRangeDB.Columns.Count; cCnt++)
{
numVal = numVal + (double)(xlRangeDB.Cells[rCnt, cCnt] as Excel.Range).Value2;

}
}

NOOSL · Accepted Answer · 2016-07-20T02:57:00

Solution 2

C#

Microsoft.Office.Interop.Excel.WorksheetFunction xlWsf = xlApp.WorksheetFunction;

The only problem was not initiating variable.
I am really grateful to you guys.
Thanks genius Ryan :-)
Thanks Madhav :-) your alternate solution also working.

Posted 20-Jul-16 2:57am