Prevent Custom Functions From Running Simultaneously?
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
ADVERTISEMENT
Apr 8, 2008
I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?
Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C
Function to count used rows....................
View 2 Replies
View Related
Feb 13, 2010
I am trying to run a loop on a few columns simultaneously, from the last row to the top (well, until the 2nd row).
So I have a few columns of data, A through I, and the number of rows they populate changes all the time.
So how would I run a loop that will check for the following:
If a certain row in Column F is greater than the previous row in column F by 1.5times, then in that same row, if number in Column B is greater than number in E, the code should put the the value of E in a new column, lets say M (in the same row)
I know that sounds very confusing, but is that even possible? I am playing with loops, and I can only do very simple loops where I define a range, like
View 14 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
Jul 9, 2014
I use a few Functions in my VBA code. All these functions, are declared as "Public", and reside in a single module. However, they are called from many different modules during code execution. (i.e. many functions called from many modules - hence the "Public" declaration).
My issue is that in addition to being available to different VBA Modules, these Functions are also available on the worksheet as a UDF (so if a user presses "=" in the formula bar, the auto-complete shows these functions when the first characters match). Is there a way to remove the availability of the function on user worksheet? i.e. to allow a Function to be called from different modules in VBA, but prevent it from being available on the worksheet.
My current work-around is to prefix all Public Function names with letter "j" - as no excel formula seems to begin with it - none of them show up as auto-complete options. Nevertheless, the Functions are still available to the user - which is what I would like to prevent.
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
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
Nov 14, 2008
I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.
View 6 Replies
View Related
Apr 29, 2008
I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.
View 9 Replies
View Related
Sep 11, 2008
I have made a vba program in excel 2003 that opens a worksheet using Workbooks.Open, and copies all the worksheets out into the program etc. The worksheets that I am opening have macros that automatically execute when the worksheet is opened (opening up some forms ). These macros are running when I open the file using VBA. How can I prevent this from happening. The automatically executed code in the workbook being opened is located in "this workbook" and can be seen below.
Private Sub Workbook_Open()
Dim CfileName As String
'Check Config sheet for template state
If Config.Cells(10, 3) = 1 Then
CustomizeDirInfo.Show
End If
If Config.Cells(10, 3) = 2 Then
GetConfData
GetGrpData
StdFileGen.Show
Else
Exit Sub
End If
End Sub
View 9 Replies
View Related
Oct 19, 2007
I know how to enable/disable events using VBA code, however is there an option within excel to turn it on/off? My problem is this...
At the beginning of my code I disable events and at the end I enable it again (I need to do this to avoid being caught in a loop). However something is going wrong somewhere in my code and the code stops halfway through. I'm trying to test sections of the code, but I often inadvertently stop the code without enabling the events again. Therefore I can't get my VBA to execute again unless I close excel down and restart. This is a pain as I have to find my place in the code again!
View 5 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
Sep 8, 2007
I added a menu command button to a toolbar in a specific workbook. The macro is housed only in that workbook and the macro needs to run on every worksheet in the workbook. However, the button also appears on EVERY Excel workbook that I open when it should not. How can I create a menu command that only appears in the workbook that contains the macro?
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
Aug 15, 2012
How do I set up a custom validation formula to prevent duplicate enties?
For example I I've already enter the song name 19 and Paul Hardcastle (BandName), how do I set it up where I can not enter that combination again on a row?
I was wanting to use a custom CountIF function but I could not get it to work.
A
B
Song
BandName
19
Paul Hardcastle
[Code] ....
View 4 Replies
View Related
Aug 23, 2008
I am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.
Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .
View 7 Replies
View Related
Nov 28, 2011
I have a couple of functions running in my workbook, and for no reason that I can see, when one of them runs during a simple macro I use to show/hide some columns, the worksheet vanishes. Is there a way to stop all functions running while I show/hide columns?
View 5 Replies
View Related
May 17, 2008
I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error.
View 8 Replies
View Related
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
View Related
May 22, 2012
Basically, I have a sheet that shows time in Days going downwards (monday tuesday etc.) and is broken out by week. In Column C, I have Project Names. Columns G-O have numbers.
What I need to do is run a custom sort multiple times down the page so that each week has been sorted by Project Name (column C). I was hoping to be to just make a quick macro shortcut to run my custom sort and each new range I selected but I don't know how to make the macro run on the actively selected cells and not hard-coded cells.
View 2 Replies
View Related
Oct 26, 2007
Is it possible to do more than one search simultaneously. Let me elaborate. I have a data of 40,000 students in DATA A and searches each student in DATA B which is also around 40,000 students. Instead of doing a For Next Statement, searching one student at a time, is it possible for it to search all the students simulatenosly because searching 40,000 students in a data base of 40,000 students takes FOREVER?
View 9 Replies
View Related
Feb 15, 2014
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
View 9 Replies
View Related
Dec 31, 2013
I have approx. 50 Defined Name and which were divided into 2 parts, i.e "name.2012" & "name.2013" means each files had ".2012" or ".2013" but the issue is I want to change or replace ".2012" or ".2013" with any other name or number in simultaneously. Just like inside excel sheets, Find and Replace method.
View 2 Replies
View Related
Nov 11, 2008
I use a two monitor setup driven from a Radeon 9550 graphics card, but the problem I have occurs on a single monitor as well. When I open multiple workbooks or worksheets I can only view one at a time the other remains embedded behind the open window. Nothing I know of will let me view both at the same time.
If there are two open worksheets full size on the screen and I minimize one they both minimize. If I decide to close one workbook/sheet down all open workbooks/sheets close down as well. Can I view two workbooks at the same time? Can I close one down while keeping one open?
View 4 Replies
View Related
Apr 8, 2009
I'm trying to sort selected rows by two columns, but simultaneously instead of sorting by "Column A and THEN Column B". In my case, this is numerical data where there is ONLY data in one column or the other, never both.
For example:
5 _
10 _
_ 1
_ 6
_ 4
Should sort to:
_ 1
_ 4
5 _
_ 6
10 _
View 9 Replies
View Related
Mar 1, 2012
freezing columns and rows it does not seem possible to freeze MULTIPLE rows AND MULTIPLE columns simultaneously in the same spreadsheet.
In my spreadsheet I would like to freeze columns A, B, C as well as rows 1 - 8.
View 5 Replies
View Related
Jan 10, 2014
Is it possible to make a copy of a chart that is edited simultaneously as when you edit the original chart...somewhere else in the same workbook.
View 1 Replies
View Related
Apr 10, 2007
Spreadsheet is made up of Col A - Col AK:
Col A = Purchase Date
The info is continuous:
Row1 12/1/06
Row2 1/2/06
Row3 3/5/07
I need monthly totals to display simultaneously. Do not want to use Subtotals option.
View 9 Replies
View Related
