Custom Function To Sum Visible Cells Clashing With Macros

Oct 23, 2007

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)
Application.Volatile
For Each Cell In Cells_To_Sum
If Cell.Rows.Hidden = False Then
If Cell.Columns.Hidden = False Then
total = total + Cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function

View 8 Replies


ADVERTISEMENT

Sum Function Visible Cells Only In Excel 2003

Apr 28, 2009

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)

View 7 Replies View Related

Clear Cells With Custom Function

Jan 10, 2007

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.Volatile

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

View 5 Replies View Related

Custom Toolbars Visible In A Specific Workbook

Dec 30, 2008

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

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.CommandBars("A").Visible = False
Application.CommandBars("B").Visible = False
Application.CommandBars("C").Visible = False..............................

View 9 Replies View Related

Can't Run Macros Off A Custom Toolbar Anymore

Oct 6, 2009

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:

View 14 Replies View Related

Sharing Custom Menus & Their Macros

Aug 26, 2008

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.

View 3 Replies View Related

Creating Custom Tool Bar For Macros In Workbook

Jan 6, 2013

I want to create a custom tool bar for a work book but when I right click in the unused part of the tool bar area the only options I get are customize Quick access tool bar and customize the ribbon.

View 3 Replies View Related

Excel 2010 :: How To Assign RELATIVE Macros To Custom Ribbon

Aug 19, 2014

I currently have an excel workbook (2010) in which I created a custom ribbon and assigned several different macros to the buttons.

Long story short, I will be saving several variations of this workbook every so often when I get new data.

The problem is that if I change the name of the file (Save as or Rename), the custom ribbon buttons no longer work. When I assigned the macros to the ribbon buttons, the macros were absolutely assigned.

Is it possible to assign relative macros to custom ribbons?

From research I have done, it appears that custom ribbons cannot be created using VBA. Is this still true?

View 6 Replies View Related

Using Macros For Custom Filter Giving 'Or&quot; Criteria And Paste The Data In Sheet2

Sep 25, 2008

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.

View 3 Replies View Related

How To Copy From Visible Cells To Visible Cells

Apr 16, 2014

Every time i do a filter and copy a data. Keep the filter on and past in another column. The data does not copy just into the visible cells. It pretend the filter is not there.

I have tried selecting only vible cells when copying and pasting into cells by selecting only visible cells but does not seem to work. Get the error message "command cannot be used on multiple section". There must be a solution but looked on the web and really cannot find one.

Table below so i filter out the "a" so i just have numbers then want to past into vible cells.

CopyPaste ResultsResults wanted
111
a2
232
a4
33
a
44

View 2 Replies View Related

Macros Not Visible: End User To Be Able To Run The Report Multiple Times By Choosing The Name Of The Macro From The Macro Menu

Jul 16, 2007

I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???

View 2 Replies View Related

How To Use COUNTIFS Function Only For Visible Data After Applying Filter

May 27, 2014

How to use COUNTIFS Function only for the visible Data after applying filter.

Ex:=COUNTIFS(A:A,"Ret",P:P,"M")-COUNTIFS(A:A,"Ret",P:P,"M",B:B,"") it gives d result including hidden data, but i want it only for visible data after applying filter.

View 1 Replies View Related

Custom Function To Run A Macro

Aug 11, 2004

I am wanting to create a custom function that i can enter into a cell to run a macro (MyMacro). I do not know how to write a function, but so far i have:

Public Function Run(MacroName As String)
Application.Run MyMacro
End Function

View 6 Replies View Related

Custom Average Function

Dec 11, 2009

Custom average function. can this be done with Worksheet functions:

View 4 Replies View Related

Custom Function For An Algorithm

Jan 28, 2009

This just a shot in the dark, but does anyone have a custom function that calculates the check digit for a cusip? http://en.wikipedia.org/wiki/CUSIP

The algorithm is listed above.

View 3 Replies View Related

Custom Addresses Function

Jun 14, 2009

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:

=COUNTIF(Table1[T/F],TRUE)

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.

View 12 Replies View Related

Custom Function With IF Statements?

Nov 21, 2011

My co-worker created a function that would calculate the standard deviation according to whatever range is selected by the user.

I was hoping to add an if statement to the formula but am getting a #value! error.

coworker's function:

Function volatility(r As Range, Optional scl As Double = 252) As Double
Dim lr() As Double
rws = r.Rows.Count
ReDim lr(1 To (rws - 1)) As Double
Dim variance As Double

[code]....

What I'd like to do with the function:

=volatility(IF((YEAR(A8:A1036)>=K3)*(YEAR(A8:A1036)

View 3 Replies View Related

Calling A Custom Function Within VBA

Sep 9, 2009

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

End Function

View 9 Replies View Related

Conditional Max Custom Function

Jul 10, 2009

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' ?

Eg.

Location Attendance
H 25365
A 17436
A 47252
H 15494
A 37578
H 17549
H 28756
A 59756

This would need to return 28756.

View 5 Replies View Related

Built-in Custom Function Not Udf

Feb 27, 2007

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.

View 9 Replies View Related

Changing Macro To Custom Function

Nov 19, 2008

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.

View 5 Replies View Related

Using An Array As Input To A Custom Function

Apr 22, 2009

Does anyone know why you can't specify an array, like an array of Doubles, as an argument or input to a custom function? For example:

View 14 Replies View Related

Custom Function Select Case

Nov 5, 2012

I have a custom function that will tell me which quarter it is based on a date entered into a cell. It works except if the cell is empty is still returns the last Case but not my Case else. If the cell is blank I wanted the function to not return anything.

Function QuarterMonth(InputDate As Date)
Dim MonthNumber As Integer
MonthNumber = Month(InputDate)
Select Case MonthNumber
Case 1
QuarterMonth = "Q1 - 13"

[Code] ...........

View 9 Replies View Related

How To Refer To Cell In Which Custom Function Is Used

Feb 8, 2013

Basically i want to use a UDF to count cells in a range of a certain colour.

But i want to make it easy for the user to be able to change the colour the function counts, so i thought i could ask them to colour the cell in which the function is written.

Is there a way to tell the function to pick up the Interior.ColorIndex of the cell it is written in?

Use Application.caller.interior.colorindex

View 2 Replies View Related

Referencing Cell Above In Custom Function

Apr 27, 2008

I’m trying to write a custom function that always references the cell above it but I can’t figure out the proper syntax to do so i.e.

Function Multiple_Cell_Above()

Multiple_Cell_Above = cellabove * 10

End function

View 9 Replies View Related

Using The AutoFilter/Custom Filter Function

Feb 3, 2010

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:

Filter:

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.

View 9 Replies View Related

Custom Interpolate/Extrapolate Function

Aug 22, 2008

I was using the macro provided by shg in post#6 of this thread: Interpolate Two-dimensional Array? ...

View 6 Replies View Related

Custom Function Not Reading Variable

Oct 18, 2006

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

View 3 Replies View Related

Custom Function Preceded With Add-in Path

Aug 6, 2007

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.)

View 4 Replies View Related

Custom Function To Rank Two Ranges

Oct 4, 2007

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,:

...
function1(a)
...

but of course (?) it does not work...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved