User-defined functions (UDFs) are VBA procedures that typically take inputs (although inputs are not mandatory) to return a result to either a worksheet cell, or to another VBA procedure. By design UDF's that are called from a worksheet should only return a value to the cell from where the function was called - the UDF should not modify the contents or formatting of any cell, or the operating environment of Excel (there are workarounds to this design).
Questions tagged [excel-udf]
99 questions
0
votes
0 answers
Application.Caller to Range
I have a function which includes the below line;
targetRange.Offset(0, -2).Value = "=validate_pointLoad()"
This cell then references a new function which is below;
Public Function validate_pointLoad() As String
Dim CallerAddr As String
…
ChrisBull
- 467
- 6
- 21
0
votes
2 answers
Making a CountRows function in Excel
I am trying to make a simple countRows function that will count the number of cells I have in a dynamic range. Basically if I have values in cells, say B2:B500, the count would return 499. However next time around values are in cell B2:B501, the…
Jack Armstrong
- 1,182
- 4
- 26
- 59
0
votes
1 answer
vba range("A1").value gives gives #Value! When entered in a function
I want it return multiple values from a VBA function called from an Excel spreadsheet but always get the error #Value!
One example found on the web:
Sub ReturnCellValue()
Worksheets("Sheet1").Range("A1").Value = 3.14159
End Function
This…
Thomas38
- 1
0
votes
1 answer
Passing the value and color of the text in a cell to a user defined function
Currently I am experiencing issues with passing the value of a cell and its respective text color to my user defined function. I am passing the references as ranges and using .Font.ColorIndex. This is then used in a IF statement to determine if any…
Thomas Breakell
- 61
- 2
- 7
0
votes
1 answer
Excel 2011 - How to change output of textJoin from value of cell, to the cell name, so I can put it in Sum() to add those values
I'm using the textJoin UDF listed below to combine values from various rows into a single cell; it displays each value. However, I want to know if I can manipulate the output of this UDF so that instead of simply displaying the values I can add the…
Devil07
- 141
- 1
- 8
0
votes
2 answers
New to Excel VBA: #Value error on string creation
Sorry all, I assume the error is basic but I am not sure what I am doing incorrectly.
I am attempting to write a function that takes a cell and converts the characters marked in red to lowercase. I do this by reconstructing the string in a new…
Nolyn Inari
- 113
- 5
0
votes
2 answers
MATCH function with #N/D cells
I have the following table in Excel:
+----+--------+-------------+------------+-------------+
| | A | B | C | D |
+----+--------+-------------+------------+-------------+
| 1 | Month | Price alpha | Price Beta…
Nicolaesse
- 2,554
- 12
- 46
- 71
0
votes
1 answer
Excel UDF returning #N/A when switching to sheet
I have a UDF in excel which technically works perfect. However, occasionally, when switching to a sheet that have cells which call that UDF, it returns #N/A. It happens consistently when I open the file and then switch to that sheet.
The reason why…
DaveyD
- 337
- 1
- 5
- 15
0
votes
2 answers
EXCEL User Defined Function - Can't find project or library without any MISSING reference
I developed a user defined function in my computer and save the document as XLSM. When I distributed it to other users in my company, they get "Can't find project or library" error as long as they open the workbook and then hit "Enable Macros".
If…
Jing He
- 794
- 1
- 9
- 17
0
votes
2 answers
Excel : How to count occurrence of particular color ( Green ) in a Row?
I want to count number of occurrence of a word in the excel in green color. For Example my excel is looks like this :-
I want to calculate occurrence of Green (X) and Red (X).
mayank051285
- 89
- 3
0
votes
1 answer
Counting the distance between similar values by rows using excel-vba/udf
I am having trouble in counting the distance between values that are similar because there’s no function in excel that could achieve this and I deal with 2000 row of values. I would prefer excel-vba for this, a button perhaps that generates…
Egie Boy Aguspina
- 29
- 7
0
votes
1 answer
How to count the number of a value?
I am dealing with 956 values in a row. I have a problem in getting the row distances in each value because there are too many of them. Is there a way to achieve this using Excel-VBA with a button?
For example:
This shows the number of times where…
vxpoisongas
- 67
- 12
0
votes
1 answer
Error #VALUE with long string in UDF in VBA for output in Excel
I use the below UDF to concatenate references to include the result in
an SQL query like ref in ('ref1', 'ref2', ...).
The UDF works just fine normally, but when I need to put a huge list of references,
I get #VALUE in Excel.
I already take a look…
R3uK
- 14,417
- 7
- 43
- 77
0
votes
2 answers
Eomonth in Excel
How can I use this function in a user-defined function? I just cannot use a reference to a cell.
Function SONxMonth()
SONxMonth = EOMONTH(A1, 0) + 1
End Func
user3876120
- 5
- 3
0
votes
1 answer
Excel Automation Addin UDFs not accesible
I created the following automation addin:
namespace AutomationAddin
{
[Guid("6652EC43-B48C-428a-A32A-5F2E89B9F305")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class MyFunctions
{
public…
Eric
- 1,392
- 17
- 37