Custom Function To Sum Visible Cells Clashing With Macros
I have a custom function that is interferring with VBA. If I remove the function the VBA works fine, if I put it back to do what it needs to do, the VBA does absolutely nothing. Any ideas on how to make things work together? Here is the custom function and it is in a module where the VBA that isn't working is in the main Workbook page, but it is a Worksheet Change VBA affecting the same page as the custom function.
Function Sum_Visible_Cells(Cells_To_Sum As Object)
For Each Cell In Cells_To_Sum
If Cell.Rows.Hidden = False Then
If Cell.Columns.Hidden = False Then
total = total + Cell.Value
Sum_Visible_Cells = total
View Complete Thread with Replies
Related Forum Messages:
Sum Function Visible Cells Only In Excel 2003
I have a worksheet of data that has 12 columns of numbers (sourced from an external data source) that I have a subtotal for each row. I need the user to be able to hide columns that they do not want included in the subtotal. I have found references to Subtotal(109,D2:Q2) on the net that apparently does this but it doesn't make any difference whether or not I hide columns (I am not using filtering).
Does anyone know how to do this using a formula, (I would prefer to keep this a macro free worksheet)
Sum Visible Cells Only
I have a need to sum only the visible cells in a row. Certain columns are collapsed depending on the month and there is a YTD colum that I only want to pick up the expanded or visible cells. I found a custom function listed below at Microsoft's website but it only allows for a single range to be entered and I need to pick 12 individual cells, for example A1, C1, E1, etc. There is other information in the cells between and that is why the range won't work. Is there a better way to do this or can this function be modified to allow me to do this?
Function Sum_Visible_Cells(Cells_To_Sum As Object)
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
Sum_Visible_Cells = total
Sum Of Visible Cells Only
I have a large spreadsheet that I am using multiple drop downs in to sort for
different scenarios. I do not want to use the general data subtotal command
but each time I sort I want to get a total for the visible cells -
Sum Only Visible Cells
I have a table of data with a filter on the top and a sum total at the bottom. Whenever i filter the list the total at the bottom doesnt change. how do i change it so the sum only calculates the visible cells?
Sum Visible Cells With Criteria
I have a worksheet with 26 columns of financial data. ACTUAL and BUDGET for each month and year totals for ACTUAL and BUDGET. I've provided the user with the ability to hide months they don't need to see during their current session. For example, if they are working on May, they may choose to hide June through December.
A user has requested Year To Date totals for ACTUAL and BUDGET. I thought this would be relatively simple. Just add two more columns and add up the visible cells.
I got this function from the Microsoft website and it works if I don't add criteria:
Autofilter - Sum Visible Cells
I've got an AUTOFILTER and would like to add only the visible cells in a particular column (column E). As the user changes the filter, the total would change - but I'm not sure where to even start with this one. I've attached a sample file.
Sum Odd Or Even Numbers & Visible Or Filtered Cells
I need to subtotal/sum a series of columns individually (one column per month across a date range) by the odd numbered visible/ filtered rows as well as (separately) by the even numbered visible/filtered rows. Preferably the totals would recalculate like a SUBTOTAL whenever the filter is altered.
I've explored using Mod and SpecialCells(xlCellTypeVisible) but I can't get a syntactically correct combination to bring back the desired results.
Clear Cells With Custom Function
I have a User Defined Function, one section attempts to clear the contents of some cells but it doesn't? (I've commented the line in question).
Function getCommission(rng As Range) As Currency
Application. ScreenUpdating = False
If rng.Offset(0, -1).Value <> "Yes" Then
Dim numTrucks As Long
numTrucks = rng.Offset(0, -10).Value
On Error Goto zero
Custom Toolbars Visible In A Specific Workbook
I created 3 custom toolbars. I want toolbar A to be visible only in Workbook A.xls, toolbar B to be visible only in Workbook B.xls and toolbar C to be visible only in Workbook C.xls. while Workbooks A,B, and C are open at the same time. I use Workbook A to automatically open Workbooks B and C.
Problem: Since I'm openning the Workbooks automatically it only keeps the Toolbar C visible on all Woksheets.
I included the following code in the This Workbook Object on each Workbook:
For Workbook A:
Private Sub Workbook_Open()
Application.CommandBars("A").Visible = True
Application.CommandBars("B").Visible = False
Application.CommandBars("C").Visible = False
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("A").Visible = False
Application.CommandBars("B").Visible = False
Application.CommandBars("C").Visible = False..............................
SumIf Function To Sum Cells When Other Cells Begin With Certain Characters
I want to use the SumIf function to sum cells when other cells begin with certain characters.
I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.
Sharing Custom Menus & Their Macros
Our ERP system generates reports in CSV. I have created a custom excel menu, which houses a lot of buttons that launch various macros to format these reports. I have been asked to share these formatting macros with other users.
I understand that the custom menu structures are stored in an excel11.xlb file. My thought was to simply replace the users *.xlb file. This only half worked. I get the menus and the macros, but when the users try to run the macros from the menu excel can't find them because the path is pointing to my user name.
Can't Run Macros Off A Custom Toolbar Anymore
A couple years ago i made a giant spreadsheet and had 2 custom toolbars to run macros from to manipulate the sheet.
This year i re-did the sheet, meaning all my old data was replaced (it's a giant schedule basically).
However, the Macros in the custom toolbars stayed exactly the same and performed the same function and are directed to the exact same cells, rows, columns, etc..
But now when i hit the buttons on the custom toolbars to run my macros, i get the following message... all the time...
A document with the name "NHLsheet.xls" is already open. You cannot open two documents
with the same name, even if the documents are in different folders.
To open the second document, either close the document that's currently open, or rename one of the documents.
Trouble is, i have only 1 document open!
There's something in the macros that thinks i'm trying to open a new document but i'm clueless as to where or why.. they are very simple macros
that do not require the opening of any new workbooks..
All they do is point to another sheet within my open workbook, "NHLsheet", copy a particular row from that sheet, and paste it to a specific spot onto another sheet within my workbook, "NHLsheet".
Here is a sample:
Average & Sum Function With Empty Cells
I have data in Column A as follows:
where cells A2, A6 and A8 are empty.
I want to be able to AVERAGE or SUM the first four nonblank cells. I know I could manually select the cells, but I have a spreadsheet with 30 columns and 40 rows, and the data (including empty cells) in each column is different.
Is there a single formula that will find the first four nonblank cells and then perform the AVERAGE or SUM function?
Sum Only Visible Rows
I have this sheet:
2 1 1
3 2 2
4 3 3
5 2 4
6 1 5
7 2 6
8 3 7
9 1 8
10 1 9
SUM(B2:B10) gives 45
Now I add a filter so all the rows with A = 1 will be hidden. That gives me:
3 2 2
4 3 3
5 2 4
7 2 6
8 3 7
But SUM(B2:B10) still gives 45. But it should be 22.
Using Macros For Custom Filter Giving 'Or" Criteria And Paste The Data In Sheet2
When I go one column and Click custom filter and give the command one number and or another numbers ( I Have attached an excel sheet with screen shot) This filters the data, and I need to copy the same and paste in the next sheet.
I have to do like this for about 20 times for 20 sets of data). I have already done this and pasted the data in sheet2. I did everything manually. ( sample sheet is attached) I need a macro to do this work for me.
When I run the macro If get 2 text boxes I can enter the numbers. and click ok,the data has to filtered in sheet1, and result has to be pasted in the next sheet.with the header. Again I will run the macro i will give 2 numbers and the result should be pasted in sheet 2 after the 1st set of data leaveing one row as blank. ( exactly like the sample data in sheet 2). If I run the macro for 10 times giving 10 different numbers, the result should be pasted one after the other in sheet 2.
Custom Addresses Function
Imagine i have 2 columns: Open and Close, both of these got numbers like
1 ---- 2
3 ---- 1
4 ---- 10
and so on. I had to make a function which checks if some number is in between any of those Open and Close numbers and count how many, for example: im searching for number 1.3, so according to previously drawn table i would get answer of 2, because 2 is in between 1----2 and 3-----1, i achieved this by a simple function:
=IF(OR(AND(Bendras!$J$1>=Table1[[#This Row],[Open]],(Table1[[#This Row],[Close]]>=Bendras!$J$1)),AND((Bendras!$J$1<=Table1[[#This Row],[Open]]),(Table1[[#This Row],[Close]]<=Bendras!$J$1))),TRUE,FALSE)
this generated an additional column with TRUE and FALSE values which i counted with:
and got the answer.
so now then preparations are ready i need to make a function which would for example if the number i was searching was in 10th and 45th rows find the MAX/MIN values of Close column between those rows(hope i made my self clear)
this is how i was hoping to do that : first of all make a new array of all cell addresses from "Close" column which were "TRUE" from the first function i wrote and when do w/e i like with those cell addresses in other functions.
Calling A Custom Function Within VBA
I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel.
Public Function CreateFlexstring(Company As String, Cost_Center As String, _
Division As String, Geography As String)
CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography
Using The AutoFilter/Custom Filter Function
I am trying to use the AutoFilter/Custom function in Excel (it is available under the Data Menu). It offers me two conditions/criteria that I can apply using and/or. For eg:
does not begin with - 3
and / or
does not begin with - 9.
I want to add a third 'and' criteria .. is it possible, and if so, how?
The column that I am trying to filter has numbers formatted as text.
Built-in Custom Function Not Udf
I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.
Custom Function To Rank Two Ranges
i have a function ("function1") that takes a range as an input:
function1 (a As Range) As Double
i have another function ("function2") that internally creates an array "a" that I need to be the input for function1. I tried, inside function2,:
but of course (?) it does not work...
Changing Macro To Custom Function
I have a calendar I have developed that highlights the current date and opens by default to the current month. Each worksheet is a seperate month. The current date highlighting is written with a public macro and the opening to current month is a private macro. The problem comes when this workbook is shared and the other users have to agree to accept my macro because of security. I would like to bypass that requirement and just have the workbook stand alone without the permission.
Conditional Max Custom Function
I have a column of data with 2 possible values, "H" or "A". The second column contains numbers (unsorted).
How do I write the VBA code to return the Highest number, where the first column is 'H' ?
This would need to return 28756.
Custom Function Not Reading Variable
A custom function as shown below is not reading in the value of the parameter Ttorefinance which is an Excel cell value (actually a link in the spreadsheet). Eg when Ttorefinance cell value is 13, the function reads in 0.
Function MarginbyTranche(TfromIssue As Double, TtoRefinance As Double, AssetType As String, Tranche As String) As Double
Dim RmbsAaaMargin As Double
Dim RmbsAa3Margin As Double
Dim RmbsBaa2Margin As Double
Dim CmbsAaaMargin As Double
Dim CmbsAa3Margin As Double
Dim CmbsBaa2Margin As Double
Dim PsAaaMargin As Double
Dim PsAa3Margin As Double
Dim PsBaa2Margin As Double
Custom Function To Interpolate Table
Actually, I've created the user defined function (UDF) to interpolate (both linear and bilinear). It's just, I keep getting this annoying error that says "A value used in the formula is of the wrong data type." But here's the kicker...I converted the UDF into a subroutine for trouble shooting, and I was able to step through the entire code and get the correct output.
Function itcinter(efpd As Single, pwr As Single) As Variant
Dim rnge, mtrnge As Range
Dim w, x, y, z, xx, yy, b As Single
Dim scenario, a As Integer
Dim J As Variant
scenario = Worksheets("Input").Range("B1").Value
pwr = pwr / 100#
If (scenario = 1) Then
'Make table into a range for VLookUp
Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight))
Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown))
'If the given value does not match a table value exactly
On Error Resume Next.......................
Setting Range.Formula With Custom Function
I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)
The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.
For example, I want I37 on most sheets to have the same formula. So I have the following:
Macro Causing Custom Function To Fire
I have a large file, part of which amongst other things calculates life expectancy from a range of q(x) values (proportion of people that die moving from age (x) to (x+1). Life expectancy is calculated using a user-defined function (below).
My problem is that whenever I run a macro that changes the file, even parts of the file that don't affect the cells using the life function, it jumps into the life function. (An example: copying and pasting values on a different sheet). This is a hassle when stepping through other macros using F8, not to mention the time cost.
Some further possibly necessary information: one macro uses the GoalSeek application to set the target cell that contains the life function
By the way, this didn't use to happen in older versions of a similar file. When running the GoalSeek macro to change target life expectancy it did, but not for any other macro.
Here is the function:
Function life(data As Range)
' Aims to calculate Life expectancy from Qx values
' It assumes first value of Q is Qb, then Q0 to Qmax
Dim Nobs As Integer
Dim j As Integer, i As Integer
Dim q() As Double
Dim L() As Double
Dim T As Double, le As Double
Nobs = data.Rows.Count
Custom Function Preceded With Add-in Path
I have created an addin (myAddin.xla) with custom functions in it. In a given cell, my funtion would look something like "=myCustomFunction(A1, B5)".
Periodically, while I'm working with the spreadsheet, I'll do something to a cell and the formulas in all the cells which used a custom function immediately get reset to something along the lines of "=C:...Application DataMicrosoftAddInsmyAddin.xla'!myCustomFunction(A1, B5)". The reset function also causes the function not to work properly and I get a "#NAME?" error in the cell.
I haven't yet figured out what sequence of actions causes the reset (it happens infrequently, but often enough to be a major problem) but it happens while I'm working with the document (i.e., it doesn't seem to be linked to saving/closing/reopening/etc). After I notice the reset, if I go to each cell that uses a custom function and delete everything from "C:... to myAddin.xla!" in the formula bar and hit enter, the function works normally as before.
(Technically, my Windows XP Pro system uses synchronization with a server at work, so the file path is not "C:...Application DataMicrosoftAddInsmyAddin.xla'!" but rather "\myServerNamemyDirectoryDocuments and SettingsmyUserNameApplicationDataMicrosoftAddInsmyAddin.xla'!". I'm not sure if this reset error has anything to do with being connected to the server or not, but figured I'd mention it anyway.)
Forcing Recalculation After Custom Function Update
I have written a custom function which is called in lots of cells. It had an error so I modified it. But the modification did not automatically trigger recalculation in the cells where it is used. Neither did F9 (manual recalculation). The only thing that did is hitting F2 for the cell then ENTER. But what a pain to do that for every cell it's used.
Changing Cell Data By Custom Function
I understand the specification of a Function procedure does not provide for it to change the contents of cells. However, I have the need/preference to be able to change cell data by using a custom function (instead of a Sub) because of its ease of use and execution of multiple what-if scenarios - displayed jointly.
Is there a way around this specification/limitation - any way? Are there different techniques (standard or non-standard) to achieve thisr goal?
Custom Function With Auto List Members
I have created a UDF which determines what the name of the day is.
Public Function Dagnaam(daydate As Date, Optional Language As String = "Dutch")
weeknl = Array("Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag")
weeken = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
dayno = Application.WorksheetFunction.Weekday(daydate, 2) - 1
Select Case Language
Dagnaam = weeknl(dayno)
Dagnaam = weeken(dayno)
What i would like is when the user uses this function in the worksheet the field Language holds a dropdown list with predefined languages.
Pass TextBox To Custom Function (UDF)
I have a simple function defined in one of my worksheets (Sheet1):
Function AddFuel(fuel As String)
How would I be able to call this from a form button event?
Private Sub CommandButton1_Click()
Everytime I try running this code I receive the error: Run-time error '1004'; Application-defined or, Object-defined error. I've even tried Application.Run("Sheet1.AddFuel", TextBox1) but still no luck. I think this is a pretty common question but I couldn't find any answer to it on the forums.
Custom Function Returns Range And Comment
I am trying to write a UDF that will calculate a value and return it to the cell, and also create a comment in the cell showing the formula used. Here is an example of the "=calculate(A2,B2)" placed in cell A1
Function calculate(ByRef X As Range,ByRef Y As Range)
calculate.addcomment "calculate = " & X & " * " & Y
the problem commes from the add comment field. I can add a comment to any cell but the cell the formula is called from.