excel vba problem - function calls from cells

I

internerdj

I have a vba function that I'm calling from a cell. It is supposed to
unhide a sheet copy some information to that sheet, do an advanced
filter, and fill some cells with a formula to count the number of
occurances of each value. I have some autofilters on my sheet and a
cell that changes when a filter is done. When I load the data into my
sheet the function works. However when a filter is performed it
doesn't run any of the commands in the function. I can trace it
walking through the commands, but it does not perform any of them.

I have another function that performs with the same result. I have 12
other functions that work just fine and are called in the exact same
manner and work just fine all the time.

Anyone know what could be going on?
 
B

Bernie Deitrick

internerdj,

My guess is that the ones that work aren't functions, but are subs, and the
one that doesn't work is a function. Functions can only return a value to
the cell that contains the function call - all other code acting on other
cells, filtering, etc. is ignored, though you can step through the code
fine.

HTH,
Bernie
MS Excel MVP
 
I

internerdj

Nope thats not it. All of my calls from the spreadsheet are directly t
functions. Since the calls ignore the lines about other cells, wha
can I do to force these functions to actually execute when a filter i
performed?

What is going on is that I need a chart that will give me a bar grap
showing the count of each unique equipment type in a column, and I nee
it to update automatically to chart only those values that are shown b
the filters
 
B

Bernie Deitrick

One way to force a User-Defined-Function to calc when a filter is updated is
to include a SUBTOTAL function call, but one that doesn't actually do
anything, along the lines of:

For numeric values:
=UserDefinedFunction(A1:A100) +IF(SUBTOTAL(9,A1:A100)>0,0,0)

For string values:
=UserDefinedFunction(A1:A100) & IF(SUBTOTAL(9,A1:A100)>0,"","")

HTH,
Bernie
MS Excel MVP
 
I

internerdj

I'll do my charting function since it is smaller:

Thats essentially what I'm doing...
=IF(H4>0,ChartObject(),"")
Where
H4=SUBTOTAL(3,B8:B5008)

THIS ONE DOESN'T DO ANYTHING WHEN A FILTER CHANGES

-Function ChartObject()

Dim i As Integer, stringer As String, stringer2 As String

Worksheets("Charts").Activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
Worksheets("Calculations").Visible = -1
ActiveChart.SetSourceData Source:=Sheets("Charts").Range("H10")
i = 1


While i < 255
stringer = Str(1 + i)
If (i + 1 < 10) Then
stringer = Right(stringer, 1)
Else
If (i + 1 < 100) Then
stringer = Right(stringer, 2)
Else
If (i + 1 < 1000) Then
stringer = Right(stringer, 3)
End If
End If
End If

On Error GoTo done

If Worksheets("Calculations").Range("H" & stringer).Value = "
Then GoTo done
If Worksheets("Calculations").Range("H" & stringer).Value = "
Then GoTo done

stringer2 = "=Calculations!R" & stringer & "C6"
stringer = "=Calculations!R" & stringer & "C8"

ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(i).Values = stringer
ActiveChart.SeriesCollection(i).Name = stringer2
i = i + 1
Wend
done:
ActiveChart.SeriesCollection(1).XValues = "=Charts!R7C3"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Hardware Count by Type"
.Axes(xlCategory, xlPrimary).HasTitle = False
' .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text
"Frequency"
End With
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue
LegendKey:=False

ActiveChart.ChartTitle.Font.Name = "Arial"
ActiveChart.ChartTitle.Font.FontStyle = "Bold"
ActiveChart.ChartTitle.Font.Size = 12
ActiveChart.ChartTitle.Font.Strikethrough = False
ActiveChart.ChartTitle.Font.Superscript = False
ActiveChart.ChartTitle.Font.Subscript = False
ActiveChart.ChartTitle.Font.OutlineFont = False
ActiveChart.ChartTitle.Font.Shadow = False
ActiveChart.ChartTitle.Font.Underline = xlUnderlineStyleNone
ActiveChart.ChartTitle.Font.ColorIndex = xlAutomatic
ActiveChart.ChartTitle.Font.Background = xlAutomatic

ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 1.965
msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementLeft -201
ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementTop -147#

ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementTop 730

Worksheets("Calculations").Visible = 2
exit2:
ChartObject = ""

End Function
-

*This one does do something everytime a filter changes:*
=IF(H4>0,VALUE(E1SUMG()),0)


-Function E1SUMG() As String

i = 8
j = 0

Worksheets("Inventory").Select
x = ActiveSheet.Range("Q1012").Value
While i <= x
s = LTrim(Str(i - 7))
s2 = LTrim(Str(i))
If (Worksheets("Inventory").Range("Q" & s2).Value = "E1") Then
b = Worksheets("Inventory").Range("G"
s2).EntireRow.Hidden
If (Not b) Then
j = j + Worksheets("Inventory").Range("G" & s2).Value
End If
End If
i = i + 1
Wend
E1SUMG = j

End Function
 
B

Bernie Deitrick

Internerdj,

Acutally, I would be more interested in the functions that you think
work.... The rules of VBA functions are thus (for functions called from
cells):

Function MyFunc(myStr As String) As String
MyFunc = "This is a legal return."
Range("A1").Value = "This line causes an error, causing #VALUE! to be
returned."
End Function

Function MyFunc2(myStr As String) As String
On Error Resume Next
MyFunc = "This is a legal return."
Range("A1").Value = "This line is ignored because of the On Error Resume
Next."
End Function

HTH,
Bernie
MS Excel MVP
 
I

internerdj

E1SUMG does work, but it only retrieves data from cells in the shee
until it returns.

Is there a way to do what I need when an autofilter is changed
 
Top