All Questions
Tagged with user-defined-functions vba
653 questions
0
votes
0
answers
73
views
Excel UDF: use Characters object without ScreenUpdating
It appears that if an Excel VBA user-defined function makes use of a Characters object, the cell in which that function is used displays some part of that object while the VBA code is running, even if ...
- 559
1
vote
2
answers
122
views
How to write Excel UDF so that it can handle being passed a single cell, or array of cells
Background
I wrote the below UDF to check whether a cell's contents is valid (i.e. if a cell's data validation is set to a list of A,B,C. Returns TRUE if the cell is "A", "B" or &...
- 416
0
votes
1
answer
111
views
Error with Excel Lambda function in Name manager
I have set up a Lambda formula that calculates interpolated values from a given set of coordinates
similar to existing trend/linest/forecast functions
handles non consecutive inputs as well as errors ...
1
vote
1
answer
65
views
MsgBox for error handling in a VBA UDF called from cells in Excel?
Is it possible to allow Excel users to stop MsgBox messages from VBA error handlers popping up and also re-allowing MsgBox messages? Or is it possible to programmatically control their frequency, for ...
- 58
1
vote
0
answers
68
views
Is there a way to use ExecuteExcel4Macro function with index function to retrieve values of a range (more than 1 cell) without looping?
I use ExecuteExcel4Macro function with index function since index can retrieve data from a closed workbook without the need of openning it. But, I want a dynamic function which retrieves the file path,...
1
vote
2
answers
105
views
I am getting a Value error when calling a VBA function in Excel
I have two VBA Excel functions: IdentifyOutliers and OutlierString. OutlierString calls IdentifyOutliers. IdentifyOutliers finds outliers in a named Excel range (Data_Table_1) in the same workbook. ...
- 49
0
votes
0
answers
76
views
VBA recognizes UDF when called in subfunction, but not when typed in Excel cell [duplicate]
I have coded a public function (z_samp(baseline as Double, prop_diff as Double) as Double) in the VBA window. When I call the function via a sub, everything works as expected. However, when I type &...
- 1
1
vote
3
answers
84
views
Grouping parts of one CELL with contents within another CELL
Here is an Excel example of what I need to solve preferably using a VBA Code.
Cell A1 contains Z002,Z003,Z004,Z006,Z007
Cell B1 Contains Z200
Cell C1 Contains Z002,Z003,Z004,Z005,Z006,Z007,Z008
B1 is ...
- 47
0
votes
3
answers
63
views
EXCEL VBA: Why is it not possible to call a UDF, which contains anywhere Application.Evaluate(), with Application.Evaluate()?
It took me some time to figure out that it is not possible use the Application.Evaluate() function with a user defined function which contains also Application.Evaluate(). See the code example below.
...
-2
votes
3
answers
147
views
How to use in UDF the Evaluate function for an Excel array formula string longer than 255 character
For a time I use an UDF which is similar to LAMDA function, where the input formula is a string. This works always without any issue. Within the UDF the function string is processed with the Excel ...
- 6,613
1
vote
2
answers
84
views
How to integrate User Defined Function with excel formula
The following User Defined Function is referencing previous excel sheet.
Function PrevSheet()
Application.Volatile
PrevSheet = Worksheets(ActiveSheet.Index - 1).Name
End Function
The ...
0
votes
1
answer
105
views
Excel VBA: Select top k numbers from a range and compute an average
I need to select top k numbers from an Excel range (column) and calculate their mean.
In Excel it looks easy:
=AVERAGE(LARGE(<<range>>,SEQUENCE(<<k>>)))
When using a range ...
1
vote
0
answers
265
views
Excel VBA to query user info from LDAP returns "Provider does not support the property"
TLDR;
Dim cnn As New ADODB.Connection
cnn.ConnectionString = "Provider=ADSDSOObject"
cnn.Open
returns Provider does not support the property on line 3.
every variation of the above ...
- 137
0
votes
1
answer
172
views
Custom functions in Excel right-click menu
My team created Excel Add-in toolbars to open our Excel macro tools. The custom functions in these Add-in are no longer displaying (or accessible) on the Excel 'right-click' menu.
We use Windows 10 / ...
- 1
0
votes
2
answers
98
views
Create a function with offset reference
I want to create a function that one of the arguments is one cell below the selection.
I want to multiply A, B and offset one row below the selection of cell of B.
Function MYFUNCTION(Num_1 As Integer,...
- 1