Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vba problem - function calls from cells
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? --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vba problem - function calls from cells
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 "internerdj " wrote in message ... 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? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vba problem - function calls from cells
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vba problem - function calls from cells
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 "internerdj " wrote in message ... Nope thats not it. All of my calls from the spreadsheet are directly to functions. Since the calls ignore the lines about other cells, what can I do to force these functions to actually execute when a filter is performed? What is going on is that I need a chart that will give me a bar graph showing the count of each unique equipment type in a column, and I need it to update automatically to chart only those values that are shown by the filters. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vba problem - function calls from cells
I'll do my charting function since it is smaller:
Thats essentially what I'm doing... =IF(H40,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 Whe=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).ScaleW idth 1.965 msoFalse, msoScaleFromTopLeft ActiveSheet.Shapes(ActiveChart.Parent.Name).Increm entLeft -201 ActiveSheet.Shapes(ActiveChart.Parent.Name).Increm entTop -147# ActiveSheet.Shapes(ActiveChart.Parent.Name).Increm entTop 730 Worksheets("Calculations").Visible = 2 exit2: ChartObject = "" End Function - *This one does do something everytime a filter changes:* =IF(H40,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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vba problem - function calls from cells
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 "internerdj " wrote in message ... I'll do my charting function since it is smaller: Thats essentially what I'm doing... =IF(H40,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 Whe=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).ScaleW idth 1.965, msoFalse, msoScaleFromTopLeft ActiveSheet.Shapes(ActiveChart.Parent.Name).Increm entLeft -201 ActiveSheet.Shapes(ActiveChart.Parent.Name).Increm entTop -147# ActiveSheet.Shapes(ActiveChart.Parent.Name).Increm entTop 730 Worksheets("Calculations").Visible = 2 exit2: ChartObject = "" End Function - *This one does do something everytime a filter changes:* =IF(H40,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 - --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vba problem - function calls from cells
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using strings in vlookup function calls | Excel Worksheet Functions | |||
Excel calls for Save | Excel Discussion (Misc queries) | |||
worksheet function calls between worksheets | Excel Programming | |||
identifying which cell calls my VBA function. | Excel Programming | |||
function calls | Excel Programming |