Massimo Scola
asked on
Problem with WorksheetFunction in Worksheet Module
Hi
I noticed that two of my WorksheetFunction formulae do not work if they are in a worksheet module. Is there a reason why I cannot create code with WorksheetFunction in such modules or is it a bug?
Massimo
I noticed that two of my WorksheetFunction formulae do not work if they are in a worksheet module. Is there a reason why I cannot create code with WorksheetFunction in such modules or is it a bug?
Massimo
Option Explicit
Private Sub Worksheet_Activate()
UpdateValues
'UpdateStatisticsValues
End Sub
Sub UpdateValues()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Statistics")
Dim shpTotalOrders As ShapeRange
Dim shpTotalCustomers As ShapeRange
Set shpTotalOrders = ws.Shapes.Range(Array("TotalOrders"))
Set shpTotalCustomers = ws.Shapes.Range(Array("TotalCustomers"))
Dim lngOrders As Long
Dim intCustomers As Integer
'Doesn't work?
On Error GoTo Hell
lngOrders = Application.WorksheetFunction.Count(Range("Orders[ID]"))
intCustomers = Application.WorksheetFunction.CountIf(Range("Customers[Orders]"), ">0")
shpTotalOrders.TextFrame2.TextRange.Characters.Text = lngOrders
shpTotalCustomers.TextFrame2.TextRange.Characters.Text = intCustomers
Set ws = Nothing
Set shpTotalOrders = Nothing
Set shpTotalCustomers = Nothing
Hell:
MsgBox "There was a problem."
End Sub
ExampleWorksheet.xlsm
ASKER
I tried it with Public Sub but it doesn't work.
What do you mean when you say "it doesn't work"? Do you get an error? Does nothing happen?
Is the problem that you always get the "There was a problem message"? If so add Exit Sub following line 37.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did my last post help you?
ASKER
Hello Martin
Yes, it worked very well.
Thanks a lot
Massimo
Yes, it worked very well.
Thanks a lot
Massimo
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2013
Marty - MVP 2009 to 2013
Public Sub UpdateValues()