Calling Excel WorksheetFunction_Sum function

[Michael8]

Hello:

I have used the Module Wizard to generate an Excel2016 module.  I am trying to figure out how to use the WorksheetFunction_Sum function as an example, and am having problems getting it to work.  I'm hoping someone hear can straighten me out.  I have some start-up/initialization code in the beginning, and some cleaning up code at the end.  That all seems to work fine.  The middle portion (Testing the WorksheetFunction_Sum function) is where I'm having problems.  I have attached the test program file for your reference, but here's what the middle section looks like:

 

  variant1%VT = VT_BSTR
  variant1%VU%PTR_VAL = ConvertStringToBSTR(trim("B6"))
  range = $Worksheet_GetRange(worksheet, variant1, $STATUS = status)
  status = AutoGetProperty(range, "VALUE", val)
  write(6, '(A, F10.2)') "val: ", val
  val = WorksheetFunction_Sum(worksheet, variant1, $STATUS = status)
  write(6, '(A, F10.2)') "sum: ", val

The first "write" statement correctly prints out the value 15.00, which is what is in cell B6 of my sample Excel file (also attached).  But the second "write" statement, which is trying to use WorksheetFunction_Sum to "sum" the values in cell B6 (which, obviously, should also be just 15.00) doesn't work - it just writes out a value of 0.00.

Can anyone explain what I'm doing wrong?  I've tried passing in "excelapp" instead of worksheet as the first argument, but that doesn't work.

Thanks in advance for your help.
Michael

[mfinnis]

A couple of points:

WorksheetFunction_Sum operates on a WorksheetFunction object so you need something like

  worksheet_function = $Application_GetWorksheetFunction(excelapp)

and WorksheetFunction_Sum sums numbers (variants) and as far as I can see the variants can't represent a range, however, WorksheetFunction_Subtotal will do it

  val = WorksheetFunction_Subtotal(worksheet_function, 9.0_8, range, $STATUS = status)

where 9.0_8 indicates the subtotal is a sum.

Alternatively if values is a real array you could do something like

  n_cells = Range_GetCount(range,$status = status)
  status = AUTOGetProperty(range,'VALUE',values(:n_cells))

and sum values(:n_cells). However, if the range isn't contiguous you need to work with Areas objects and extract each sub-range separately

[Michael8]

Thank you very much for your reply.  I had no idea that I actually needed to pass in a "worksheet function" (i.e., variable worksheet_function, in your example) as the first argument.  However, I still can't get it to work.  I tried using the WorksheetFunction_Subtotal, as you suggested, but it gives an "access violation" error.

You mentioned the idea of getting variants to represent a range.  I think that's the real sticking point for me.  I was experimenting with that for quite some time before even posting my original question, and I couldn't figure out how to do it.  It seems like there must be a way.  Using WorksheetFunction_Sum was just supposed to be a "simple" example I had started with.  I wanted to move on to using other functions such as WorksheetFunction_Vlookup.  That function also expects variants as arguments.

Thanks.
Michael

[Michael8]

Aha!  I think I finally got it.  You can convert the range into a variant if you set the VT type to be VT_DISPATCH, and set the VU%PTR_VAL to be the "range".  Also, I'm still not sure if the first argument to WorksheetFunction_Sum should be "excelapp", or "worksheet_function" as you had suggested.  But "worksheet" doesn't work.  So here's the code that I found to work (I am using the "excelapp" version, since it keeps it more similar to the original code I had posted).

 

  variant1%VT = VT_BSTR 
  variant1%VU%PTR_VAL = ConvertStringToBSTR(trim("B6")) 
  range = $Worksheet_GetRange(worksheet, variant1, $STATUS = status) 
  variant1%VT = VT_DISPATCH
  variant1%VU%PTR_VAL = range
  val = WorksheetFunction_Sum(excelapp, variant1, $STATUS = status) 
  write(6, '(A, F10.2)') "sum: ", val 

Also, I have taken out the first "write" statement.  With this new code, it properly prints out the value of "15.00".  And if you change the "B6" reference in the second line to include more cells (e.g., "B6:B8") it also works properly, summing up the contents.  I hope this will be helpful to others.

Thanks.
Michael