Efficiency Of Custom Functions (UDF) Compared To Standard/Native Formulas
Sep 30, 2006
Assume I have a sheet consisting of 50 rows with 3 columns of figures. A fourth column contains a formula to calculate values, eg A1+B1+C1, on each line/row. I could enter A1+B1+C1 in the first cell and Copy, Paste to the other 49 cells. Alternatively, I replace A1+B1+C1 with a UDF.
Is there any difference in performance with using a UDF compared to using the long-hand/standard approach, or is it just a question of convenience? After all, both are doing the same operation with the same calculation.
View 5 Replies
ADVERTISEMENT
Mar 14, 2008
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
View 9 Replies
View Related
Sep 2, 2008
I want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
View 9 Replies
View Related
Dec 3, 2008
I am writing some custom functions to be saved in an Add-In file, but am having trouble saving named ranges. I would like named ranges in the Add-In file to be available in other spreadsheets. Is there a way to do this? Or is there a way to take a range from Excel and save it into a globally available named range using VBA?
View 9 Replies
View Related
Oct 17, 2005
Since I got such fantastic help with the last quesiton let me try one that
I've been beating my head against the wall on for two weeks now.
I'm calculating CAGR (Compound Annual Growth Rate) of an investment using
the following formula:
=((GEOMEAN(IF(Work!$A9:$A236<B2,Work!$DS$9:$DS$236))^12-1)*100)
A Column: Dates
D Column: Monthly returns in multiplier format
I want to do the same thing as this with GSD using this basic formula --
that is, only do the returns based on the IF statement:
=(EXP(STDEV(LN(Work!B9:B236)))^SQRT(12)-1)*100
And Sharpe with this formula:
=(AVERAGE(B49:B276)/STDEV(B49:B276))*SQRT(12)
Any help you can offer will certainly save me a ton of pain -- head's
beginning to hurt!
View 6 Replies
View Related
Nov 28, 2006
I have created some user defined functions and have associated them to a custom category. To simplify things, I wanted to add these functions and the custom category macro to an Addin I already have installed for all my employees. How do I get the the custom category macro to excecute? It should run as soon as excel is opened...but I am not sure what event should be used and where is it installed?
View 2 Replies
View Related
Dec 5, 2008
I am trying to create a custom function to act much like the database capabilities of excel in summing a column based upon multiple criteria of other columns. First, before I get too far, does anyone have an already built custom VBA function that will accomplish this? I find that using the built-in database functionality in excel is VERY slow!
Anyways, I am trying to pass an argument that contains multiple ranges (much like you can do with the SUM() function):
=MultipleSUMIF("E11:E14, F11:F15", "A1, E1", G11:G14)
The first set of arguments is the ranges that I need to look through. The second set of arguments is the values that I need to search within the first two ranges. The third argument is the column that I need to perform a SUM function on.
Instead of doing this, how can I create a function that will allow me to say, "Add another criteria range/value" and it will add another criteria to the list. I am currently passing a string, and that is not ideal, obviously.
View 9 Replies
View Related
Aug 20, 2008
Recently I found this forum through a Google search along with the perfect solution to a problem I was having finding and replacing text based on a table of replacement values. Here is the thread:
Multiple substitute or replace text using a table
I incorporated the SuperSub function that steveorg developed as a result of that thread into my worksheet by creating a new module and pasting the following code into it:
Function SuperSub(OriginalText As String, rngOldText As Range)
Dim cel As Range
Dim strOldText As String, strNewText As String
' loop through list of old_text used in substitute
For Each cel In rngOldText.Cells
strOldText = cel.Value
strNewText = cel.Offset(0, 1).Value
OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText)
Next cel
SuperSub = OriginalText
End Function
It works like a charm as a formula, but I also have a macro in the same workbook that, as soon as it selects the sheet with the formula, calls the SuperSub function and starts executing the code as a macro (even though there is no call to this routine in the macro). The macro ran just fine before incorporating the function into my worksheet.
The formula looks like this: =TRIM((supersub( UPPER(E2),rngSubst)))
If cell E2 contains the string "101 North Main Street, Apartment 5", it would return "101 N MAIN ST APT 5"
Here is the macro:
Sub Import()
'
Sheets("Shoebuy FTP").Select
Range("A2:R200").ClearContents 'This is where it jumps to Function SuperSub(OriginalText As String, rngOldText As Range)
The function runs for every occurrence of the formula in the worksheet, and when it is finished, all of the cells containing that formula show a value of "#VALUE!". I should mention that it does not change the formula at all, but I then have to highlight each cell that contains the formula, press <F2> to edit, then <ENTER> to get it to display the correct results again.
It is probably some stupid little thing that I didn't do when I created the function, but I can't figure it out. I have written numerous macros, but this is the first time I have ever created a custom function.
View 5 Replies
View Related
Mar 21, 2007
I am trying to create a custom button in Excel that will sort on two different columns. One column has $ Sales and the other has Unit Sales. So you can click on the button and it will sort by $ Sales and if you click the same button again it would sort by the unit sales. I'd also like the text on the button to reflect what option the data would be currently sorted by. I am able to do one of these macros and assign it to the button, but can't figure out how to get both sort options assigned to the same button. I have had outside people send spreadsheets to me with this but can't figure out how they did it.
View 2 Replies
View Related
Jul 31, 2014
I have this excel file with some functions inside a Pivot Table, which use a year value as filter (the value comes from a report filter field) and data from a different spreadsheet to calculate a percentile value ... I created custom functions to replace the excel functions, passing the values as parameters.. The problem is now that when we refresh the connection (Refresh All button under DATA tab) the cells that contain the values returned by the custom function lose those values (go blank) In order to get the values I need to select the filter value again, while when I was using the normal PERCENTILE function the values would always refresh normally
Now... I think the problem might be related to the fact that the spreadsheet in question calls 5 different custom functions at the same time.... and, those 5 function call another at least two more helper functions... so, maybe this recurrence in calling the functions is causing the thing to break up
Is there a way I can prevent this from happening? Maybe put some flag or something that will cause the functions to be executed one by one, and not all at the same time?
View 2 Replies
View Related
Jan 11, 2010
I have a spread sheet that contains many user defined functions from a 3rd party provider that downloads financial data from an external database. Often not all of the data is required by the user, and as the UDF's take a long time to calcuate, i'd like to provide the ability to easily switch them on and off. Originally I had been looping through cells and either adding an apostrophe in front of the = or removing it. However running this loop is very slow.
I found this thread
(Optimize VBA Loop For Inserting Formula)
and adapted it to convert formula strings (with ' in front) to formulas in a single pass by setting:
myRange.formula = myRange.value. This works very well. I can't, however, work out how to go back the other way in a single pass. Have tried: myRange.formula = "'" & myRange.formula. This works for individual cells, but not for more than one at a time.
View 3 Replies
View Related
Jun 24, 2014
I have created a sub to add new controls to the "Cell" shortcut menu. Can I remove the standard "Cell" shortcut menu controls? if so how do you do it?
[Code] ......
View 4 Replies
View Related
Jun 12, 2008
To avoid manually updating a formula 100+ times, I am looking for a way to update cells in a column with reoccuring formulas with references to other cells that vary respectively from cell to cell. Meanwhile, there are other cells without formulas that this will not apply to in the same column. I am trying to avoid macros if possible, unless it can be easily understood/modified by someone who knows next to nothing about macros or VBA. My initial thought was to use the find/replace feature, but I don't know of any way to do this so the reference will update respectively for each cell (ie A9 then A10 etc.) in both the find and the replace fields.
ie
Find: A9="Text1"
Replace: OR(A9="Text1",A9="Text2")
Where the row number updates respectively.
Simplified example:
Existing:
=IF(A9="Text1","1","2")
[misc. blank or non-blank cell]
=IF(A10="Text1","1","2")
etc..................................
View 2 Replies
View Related
Dec 10, 2012
how to build and execute formulas and functions. My love for messing with excel started with an interest in personal financial management utilizing amoritzation tables, budget planning, paychecks, tax bases, etc., My wife sees it as strange but I love messing around with the Excel formulas. For now it's all self taught but, as stated, that will be changing.
So far, I haven't been able to find how to execute the following scenario.
For brief background, I'm attempting to identify the results of various outcomes of an individual currency trade by changing the investment size given the results of the last "x" amount of trades. Put another way, I'm running data sets on Forextester wherein I trade a historical period of time on a specific set of currencies, i.e. "Pound/Dollar." I'm wanting to know what the resulting balances would be, or would have been, if I implemented a money management strategy that increased my investment on consecutive trades when there was an "x" number of consecutive "wins" (ending account balance more than beginning) on the previous "x" number of trades. and decreased my investment when there was an "x" number of consecutive losses (ending account balance less than beginning account balance) on "x" number of trades...
So far, I've built the formula so that if I input the beginning and ending balance of a specific trade and Excel populates a "W" or "L" (Win or Loss) in the "Results:" Cell which was pretty simple. The following is one of three scenarios.
Strategy
Conservative:
Start with risk 1% of Account balance (which is $10,000 for example)
The Rule: With each open trade reduce the account balance used by the amount risked on opened trade
(For consecutive trades, assume current trade results in a total loss of investment. Thus, $100.00 of the $10,000 accounts means you're working with $9,900 on next trade)
The Rule: After 3 consecutive winners that make (1.8 * Risk percent used by the trade * account balance used by the trade) or greater in profit, increase risk to 1.5%
First trade Investment: $100.00 plus minimum of 80% gain. Second Trade Investment: $99.00 plus minimum of
80% gain. Third Trade Investment: $98.00 plus minimum of 80% gain. For trade number four, the investment amount would now be 1.5% of the total account balance.
After 1 loss reduce risk to 1%
First Trade Investment $100.00 and you lose - size of loss doesn't matter. Next trade opens with investment of 1%.
After 2 consecutive losses reduce risk to 0.5%
First trade Investment $100.00 and you lose - size of loss doesn't matter. Next trade opens with investment of 1%. Second trade is also a loss. Thus, third trade invests 0.5% of total account balance.
After 4 consecutive losses reduce risk to 0.33%
After 2 consecutive wins that make (1.8 * 0.33%* account balance used by the trade) in profit, reset risk to 1%
(Two wins = 1% investment, regardless of number of previous losses
There are two additional scenarios but they all follow the same pattern. The differences are only in the actual risk percentage. I can modify the formula as needed.
View 1 Replies
View Related
May 23, 2008
I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))
View 5 Replies
View Related
Jun 4, 2008
I need all cells which containing formula IF in red colour. I have tried it with conditional formatting, but i can`t figure it out.
View 8 Replies
View Related
Jun 22, 2008
How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.
View 5 Replies
View Related
Jan 2, 2009
I have not been able to find any references to anywhere, so here I am....
I'm using Excel 2003 that has a menu bar added by another program that interfaces with it (via DDE, as I understand it). The DLL's from the other program have no documentation and/or ability for me to connect to the particular drop-down menu command I'm using. It is a refresh command that re-imports stock market trade data because DDE drops trade messages.
View 11 Replies
View Related
Jun 14, 2007
I am trying to identify the current user, similar to Environ("Username") in VBA
I have a vague recollection of a formula from an old version of Excel (4.0??) that I've read about that might acomplish the task.
I seem to recall reading about a function, I think it was Get
that is not supported, and not that well documented.
Does anyone out there know what I'm talking about.
Otherwise does anyone know of a native worksheet function that would return the current username?
View 9 Replies
View Related
Dec 13, 2006
All I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
View 4 Replies
View Related
Sep 25, 2009
My excel (2003) is stuck in array mode - is there any way to get it out of Array Mode and put it back in the letter:number display for formulas/functions? It's doing this in VBA too which is totally killing me.
View 2 Replies
View Related
Jan 28, 2014
Problem we are having with sorting a table.
The table (A1:E10) was created by pulling data from another worksheet using INDEX and ARRAY formulas.
We inserted 6 columns to move the table over to G1:K10 (used column F to space the two tables).
We then duplicated the table: cell A1=G1, B1=H1, ...., E1=K1 and dragged the formulas down. We now have a duplicate table that we want to sort.
We choose CUSTOM SORT and choose 2 levels of sorting ... column A and column C.
PROBLEM: The ARRAY formulation evidently overrides the sort function because the table remains as it was when the sorting command is executed.
Is there a way to bring the data from table G1:K10 over to table A1:E10 in a manner that will allow sorting??
View 9 Replies
View Related
Jan 29, 2009
I created a macro that checked a cell for invalid numbers. There were 6 I checked for: #REF, #NUM, ####, #Div/0, #N/A, #Null. Because you can only compare two "phrases" with OR, I had to write it like this:
View 12 Replies
View Related
Jul 12, 2007
If there is either a 1 or 0 i cell A1 what is the most efficient =IF(A1=1;B1;C1) or
=A1*B1 + (1-A1)*C1? Or perhaps their are something even more efficient??
View 4 Replies
View Related
Apr 21, 2009
My question is about the If-Else Construct.
I often write If-Else statements that require an action be taken only if something is true. If that something is false, no action is to be taken.
My question is, how do you code "no action".
The following is what i usually
View 5 Replies
View Related
Sep 18, 2007
It is to large to upload here and I have removed the sensitive data that would normally be present. The part I'm currently stuck at is the multiple search criteria, one part works while the other does not. Also, I have yet to discover a way to indicate to the user that the correct Row is selected and being edited in the right cells.
View 2 Replies
View Related
Apr 27, 2014
I run a rather simple loop (see below). But as it runs through A1 to A28000 it takes a lot of time. Is there a more efficient loop to operate this simple task?
View 9 Replies
View Related
Sep 1, 2009
how to reduce formula size/improve excel efficiency?
I have a really complicated formula with lots of COUNTAs, VLOOKUPs, IFs, etc etc etc which returns a variety of values, ZEROs (for blank cells being referenced), and #NAs.
I would like to turn all ZEROs and #NAs into ""s without repeating this super long formula over and over.
I am curently using:
=IF(or(ISBLANK([superlongformula]),ISNA([superlongformula])),"",[superlongformula])
View 9 Replies
View Related
Feb 8, 2008
Disclaimer: I do not know the correct "lingo" to describe Excel functions and formulas, and I'm not really sure if Excel is capable of doing what I'm proposing, so please bear with me.
I'm not an avid Excel user, but I've got a question on my current problem. I've got a spreadsheet with several columns on it for my retail sales business. The spreadsheet is used to calculate my selling price based on my dealer cost of a unit plus a percent markup.
Currently, the spreadsheet is set up so that the "percent markup" column can be changed so that I can enter, say, 20%, and it will automatically spit out my selling price based on my cost divided by .80(for example).
This works fine, and I like it. However, now I have over 70 different items on the list, and the higher my cost is on an individual item, the lower percent markup I am able to make. So currently, I'm going in manually and entering a different "percent markup" for each item, depending on my cost of the item.
I'd like to learn how to define parameters for the "cost" field that will directly input to the "percent markup" field. For example, I'd like all items that cost me between $2,000 and $5,000 to have a "20" inputted into the "percent markup" field. For items that cost me between $5,001 and $10,000 I'd like to have a "15" automatically inputted into the "percent markup" field, and so on. I'm envisioning a small chart off to the side of the spreadsheet that would look something like this:
Price range percent markup
$2,000-$5,000 20
$5,001-$10,000 15
$10,001-$15,000 14
$15,001-$20,000 13
and so on and so on....
The chart (such as above) would allow me to simply change the "percent markup" inputs and the "price range" inputs at will to allow me to affect my spreadsheet and eliminate the tedious line-by-line insertion of each "percent markup" number by hand.
View 9 Replies
View Related
Aug 5, 2012
The workbook which is attached with this thread works fine in my project but the problem is for double ids for example in my case OCD6415 is double in sheet 1. This macro just compared the two worksheet and when the comparison is found then copy the respected cells from worksheet 1 to worksheet 3 but i want that if the ids are double or more even then it must copy all the compared (even identical) ids to the worksheet 3. The code is following
VB:
Sub test2()
Dim a, i As Long, w(), e, s$
Application.ScreenUpdating = 0
[Code]....
View 9 Replies
View Related
