# udf

1. ### Can I use a worksheet as a function?

Hi, I would like to use the calculations on one worksheet as a function in another worksheet. To explain, let's say I have two input values in Sheet1, Number_of_staff in A1 and Hourly_rate in A2. Then, the total cost for my project appears in A3. But calculating the total cost might use 100...
2. ### Automating Company Holidays on calendar

Hello again wonderful people. I have a partially automated calendar Excel file I track my time off hours in for work. I've been upgrading it a little at a time, as I learn new functionality and I've gotten to a point that seems to be very close, but is stumping me. I've extracted the relevant...
3. ### Filter in multiple areas

Hello to all, Im strugling to solve this for a week now. I would like to create filter something like: =FILTER(I4:I97,(J23:J40="PN")+(J42:J59="PN")+(J61:J78="N")+(J80:J97="N"),"") but always with error. This is just an example, I already tryed multiple variants of this, but still with some...
4. ### Using UDF on multiple sheets causes #NAME? error

Hi, I need some help with the following. I have two workbooks where I need to do a lookup in between and obtain a value. To be more precise there is one active mastersheet which contains all the data, and a map with separate sheets, and each sheet does a lookup on the mastersheet and obtain a...
5. ### User Defined Function (UDF)

Hello everyone! I've recently gotten to understand that you're able to create your own functions by taking an Excel-workbook, adding some VBA "Functions" to it in modules, and then save and load it as an Add-in into Excel. Now this is so amazing, a real game-changer. But, what I want to know...
6. ### Loop Through Sequential Variables for UDF

Hi all. I am trying to see if it is possible to loop through a series of variables with a standard naming convention. I am using this to create a UDF for me to use. What I am hoping to achieve is something like this where I can choose to enter up to a certain number of ranges as arguments for...
7. ### VBA debug compile error

Hello, I am working on this code that matches multiple criteria and then does a bunch of copy pasting (simply put) I am looping through my array, sending through the criteria to the UDF, which loops through the second data region to find the match and paste the values I want pasted simple...
8. ### Clear argument descriptions of UDF using MacroOptions method

I used MacroOptions method to add descriptions for UDF. How to clear/unregister UDF descriptions? My code: Because of ArgumentDescriptions:=Empty , the code makes error 1004.
9. ### User defined function with list as a result

Hi, I've created an UDF which takes some arguments and returns a list as a result, see example: Function myFunction(a, b, c) Dim MyFunction_result(1 To 4) As Double result1 = a + b + c result2 = a * b * c result3 = Sqr(a ^ 2 + b ^ 2 + c ^ 2) result4 = a * (b + c) MyFunction_result(1) =...
10. ### User Defined Function Stops Working When Renaming File

I have an excel file that is exported weekly that I need to convert certain cell colors into other colors. To accomplish this I found a User Defined Function =identifycolor (seen below) that I wrote into the conditional formatting to change the color of a cell if it met one of the color codes...
11. ### UDF om aantal jaren, maanden en dagen van een periode tussen twee datums in vloeiend nederlands

Dit is geen vraag, maar een oplossing voor wie er naar zoekt: een UDF die verbeterd de functie datumverschil uitvoert: wie de periode tussen twee datums wil omschrijven met jaren, maanden en dagen, zonder dat er 0 jaar of 0 maanden of 0 dagen tussen zit, kan deze formule gebruiken ... Perfect...
12. ### UDF to retrieve formula in target cell

Hi, I need to create a custom VBA function to perform an operation similar to VLOOKUP. I want to perform a search operation like VLOOKUP. But the only difference is, I need to retrieve the formula in the target cell instead of retrieving value in the cell, and I don't want to print the...
13. ### VBA for Dates Computation is too Slow

The following VBA UDF returns a 4-value-array using a Date value as input. Even though it is simple, if I ran for ~10'000 rows calculation time is not satisfactory (>30seconds). What am I doing wrong and how should I write the code to make it faster? ' YrMoDoy Function YrMoDoyDom(InputDate)...
14. ### UDF evaluates after every change

Hi, I have an issue, i though pretty simple one but cant handle it so i guess was wrong. I have a UDF that calculates average of exchange rates between 2 dates Option Explicit Public Function averageFromRange() As Double Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("Exchange...
15. ### Custom Fuzzy Lookup (in rows)

Hi all, I have inherited some fuzzy match code (below) which compares two text strings, it works fine and gives me the character similarity I need, but it also gives me 5 columns of other data (which I don't want or need). Example below. Would anyone know which bit of the below to edit out so...
16. ### User-defined function that returns an array doesn't work in a named table

I made a user-defined function in vba that returns an array. It works fine in normal spreadsheets but failed when it is applied to a named table with error "#SPILL!" appears in the first cell. I'm sure there is no other content in the spill range. If I turn the normal spreadsheet range that the...
17. ### Loop through Range/Array - Vectorized Calculations (VBA UDF)

I understand that I can take a range of values and make a vectorized calculation on it by using {...} in plain excel (Method 2) (Method 1) below is showing the simplest way of accomplishing what I need: 1. Take the values in a range/vector/array, 2. subtract a fixed value from each 3. save as...
18. ### Combining UDF with IF function

I need to combine a UDF (below) with a set of IF functions. Function MaxN(n&, r As Range) Dim i&, j&, m#, t#, v v = r.Cells.Value2 For i = 1 To UBound(v) If UBound(v) - i + 1 >= n Then t = 0 For j = i To i + n - 1 t = t + v(j, 1)...
19. ### Merge duplicate and similar rows into new sheet

The initial part of my query is similar to some other posts (although I am not adept enough at VBA to modify the code as needed to suit my purposes), but because the second part goes beyond what I've been able to find already on the forum, I thought I'd post the whole question here. I'm working...
20. ### UDF to reformat hierarchical numbering for sorting

The fact that Excel is unable to identify and correctly sort hierarchical numbering (ex. 1.1, 1.2, 1.2.1, 1.2.2, 1.3...) is an age-old problem which has been widely discussed in many fora. There are numerous workarounds, but they are often project-specific or formula-based. At the moment, I have...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back