SumIF error 1004

[Khaki]

Hi,
I am having a horrible time at tracking this down, but basically SumI
is not working within VB6.

I get Run-time error 1004
Unable to get the SumIF property of the WorksheetFunction Class
Code:

If sID = "050" Then
sTemp = "O" & iTop & ":O" & iBottom
dTemp = excel.WorksheetFunction.SumIf(wDataSource.Range(sTemp)
">0") ' Line that causes Runtime error 1004
.Cells(iCurrentRow, CI_StartColumnTotal).Value = dTem
'WorksheetFunction.SumIf(wDataSource.Range(sTemp), ">0")
Else
.Cells(iCurrentRow, CI_StartColumnTotal).Value
WorksheetFunction.Sum(wDataSource.Range("O" & iTop & ":O" & iBottom))
'Sum works fine
End If


I am using VB6, and Excel 2K, and this is writing code inside a CO
add-in for Excel.
Any help would be appreciated.

 

[Bob Phillips]

In Excel VBA it works fine for me. What do you have in ITop, iBottom,
wDataSource?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Khaki]

Hi Bob,
In VBA sumif works fine.
It is when I am trying to write a COM add-in for Excel in VB6, that i
when it fails.

Create a VB6 Add-in project.
Set the Connect object to be Excel

My Project References are as follow and in same order:
================
VB for Applications
VB runtime objects and procedures
VB objects and procedures
OLE Automation
MS Office 10.0 object Library
MS Add-in Designer
MS VB 6.0 Extensibity
MS Excel 10.0 object library

These are the only code in the project and they are in Connect.dsr

Option Explicit

Dim oXL As Excel.Application
Dim WithEvents MyButton As Office.CommandBarButton


Private Sub AddinInstance_OnConnection(ByVal Application As Object
_
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom() As Variant)
On Error Resume Next

Set oXL = Application

Do Whil
oXL.CommandBars("Standard").Controls.Item(oXL.CommandBars("Standard").Controls.Count).Captio
= "CashFlow 1" '"My Custom Button"

oXL.CommandBars("Standard").Controls.Item(oXL.CommandBars("Standard").Controls.Count).Delete
Loop

oXL.dis
Set MyButton = oXL.CommandBars("Standard").Controls.Add(1)
With MyButton
.Caption = "CashFlow 1" '"My Custom Button"
.Style = msoButtonCaption
.Tag = "CashFlow 1" '"My Custom Button"

.OnAction = "!<" & AddInInst.ProgId & ">"

.Visible = True
End With

End Sub

Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
On Error Resume Next

MyButton.Delete
Set MyButton = Nothing
Set oXL = Nothing
End Sub

Private Sub MyButton_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
oXL.Worksheets("Monthly CF1").Cells(1, 1).Value
Application.WorksheetFunction.Sum(oXL.Worksheets("Monthl
CF1").Range("H7:H17"), ">0")

End Sub


Where it fails is at this line
oXL.Worksheets("Monthly CF1").Cells(1, 1).Value
Application.WorksheetFunction.Sum(oXL.Worksheets("Monthl
CF1").Range("H7:H17"), ">0")

Can you see if it works for your? You can subsituate "Monthly CF1
with any worksheet. Change range to your fitting.

thanks

 

[Khaki]

Please remove extra code

oXL.di

 

[Khaki]

solve