udf

I have a UDF to write out the names in proper naming style. But, I am not able to incorporate names such as, say for example, Douglas MacArthur. Instead of Douglas MacArthur, the UDF outputs Douglas Macarthur. How can I tweak the UDF to handle names of the types as Douglas MacArthur? The...

Excluding blank cells Function showformula(rng As Range) If rng.HasArray = True Then showformula = "{" & rng.Formula & "}" Else showformula = "Sheets(""" & ActiveSheet.Name & """). Range(""" & rng.Address & """)" & ".FormulaR1C1 = " & """" & rng.FormulaR1C1 & """" End If...

I am trying to obtain the name of a named range passed to a UDF. I want to use it in error messages. Based on previous discussions and an Internet search, I came up with this, which doesn't work. Public Function ShowRangeName(rng As Range) Dim rngname As String rngname = rng.Name.Name MsgBox...

I have a UDF that is called from just one cell (D20) in just one sheet (Sheet1) in just one workbook (Book1). But if I change the contents of any cell in that sheet, or any other sheet in that workbook, or even any cell in any sheet in any open workbook, the UDF gets called. And it always gets...

Suppose in Sheet1, I have given the cell C4 the name "MaxValue" and MaxValue (C4) contains the value "100". Is there a way that a UDF called from any cell in Sheet1 can access the value in C4 using the name MaxValue without passing it as an argument? I am working on a UDF that needs a bunch of...

I am working on a UDF that may be more complicated than I can handle. (sigh) The UDF is called just once from a single cell in the only sheet in the only open workbook. I have break point (F9) set at the top of this section of code: . . . Dim iCol As Long Dim RBestI As Variant Dim...

If Function A is called from an Excel cell and then calls Sub B, is there any way that B can return an error condition directly back to the Excel cell on behalf of A without returning control to A? I am working on a UDF (A) that does a lot of error checking. Wanting to standardize the error...

In a UDF I am able to determine the address of the calling cell using Application.Caller.Address Is there something similar that I can use to determine the name of the sheet and workbook? Thanks

I may be in over my head here, but I cannot figure out what the heck is going on. I have a complicated UDF that gets an error because one of the variables that gets data from a passed range is sometimes Empty and sometimes not. The UDF is called from several locations in the sheet. If I set the...

Is there a way for me to declare a variable in a UDF so that it will be available to any UDF that it calls? I tried Public varname as double Global varname as double but they both got error messages. A search turned up several webpages about global variables, but they all seem to indicate...

I am working on a UDF that processes a large table of data. A small example might look something like this. <tbody> R/C W X Y Z AA AB AC AD 24 $100 4.5 A Y B33 3/22 250 2005 25 $250 5.8 C N X21 4/15 135 2011 26 $75 1.5 F N R03 3/29 1995 27 $110 6.0 D Y G44 4/20 400 2015 </tbody> The...

I would like to write a UDF that can "look up" a value in a sheet table if passed the name of the table and the search text. Here's a simplified example of what I would like to do: Suppose I have this table in Sheet1 containing the scores in a trivia game: <tbody> C/R C D 4 Amy 28 5 Brad...

Maybe the Alzheimer's is worse than I thought. I am trying to use this custom format to get numbers right-justified, but with a small margin on the right. 0.00_. It works almost all of the time. There is one situation in one workbook where it fails and I cannot figure out why. If I enter some...

I have a UDF in an add-in module. I decided to make some changes, so I copied it to a code module in a macro-enabled (.xlsm) test workbook. It was all working fine until I closed the test book. When I reopened it, none of the calls to the UDF were getting executed. They all showed a Value error...

Hi folks. I'm building a new WB and moving values via VBA from various, scattered cells on one sheet to create a single row of selected values on a 2nd sheet for charting, probably by way of a command button to run the UDF. The UDF finds the next empty row in target sheet to deposit the values...

Hi All I currently have the below UDF for my spreadsheet. It looks at two columns and counts the highest date, if the leavers column is empty and the date entered is less than the DateToCheck I would like to add 2 more variables to this UDF if possible. 1. I would the UDF to count...

I'm after some guidance. I have a file that is currently 30,000 rows with lots of formulas, I'm wondering if a UDF or 3 would assist in the speed and data return example =IF(O29637="","",IFERROR(IFERROR(VLOOKUP(O29637&"",NAMES,2,0),VLOOKUP(--O29637,NAMES,2,0)),"")) or...

Hello guys I wasn't expecting to post a question here, but here it is: related to another post here (https://www.mrexcel.com/forum/excel-questions/1090639-formatting-cell-value-based-another-cells-colour.html) I decided to make a small UDF to determine the color of a cell. At first it all went...

Hello, I have two UDF's defined in my workbook. One in Module 1 and the other in Module 2. The first UDF is used solely on Sheet1, and the second UDF is used solely on Sheet2. When I calculate the Sheet1 and navigate to Sheet2, every UDF on the Sheet2 has a #VALUE ! error. And when I...

I've created a UDF that returns the values "Good", "Average", "Bad" depending upon the colour of the text format of a cell. I'm trying to use it in an array formula or a sumproduct formula, but I get only a zero or one which seems to have little relationship with the actual colours of the...