How to debug a user defined function

The user defined function below is causing duplicates and this causing a build to fail. What is the best approach to test each part of the function so I can pinpoint where the issue is? And assistance would be a huge benefit. I've never really had ...
more »

2017-10-03 11:10 (1) Answers

VBA Pass value between subs

I am trying to learn how to pass values back and forth between subs. I have created several macro subs that ultimately use the same lines of code over and over again between them. To start I want to be able to look up/count the # of columns and row...
more »

2017-10-02 06:10 (1) Answers

VBA User Defined Function #VALUE error

I have four sheets: INVESTMENTS sample row-1: ABC, INV_ID1 sample row-2: ABC, INV_ID2 sample row-3: XYZ, INV_ID3 sample row-4: XYZ, INV_ID4 RETURNS-ABC sample row: date1, status_INV_ID_1, returns_INV_ID_1, status_INV_I...
more »

2017-08-27 14:08 (1) Answers

Get Text from Range - VBA (Excel)

I want to make a data validation list from text only in a Range of cells. I searched for formula but I found nothing so I decided to make my own function but its not working. I tried those codes: Code 1: Function ListFromRange(rng As Range) As Ra...
more »

2017-08-08 12:08 (1) Answers

Can't return correct error type for an Excel UDF

I've written a fairly simple UDF in Excel, and now I'm adding error checking. Essentially it just returns the substring between two delimiters in a string. If either delimiter is not found, I want it to return an #N/A error (as something like MATCH w...
more »

2017-07-13 07:07 (1) Answers

Take augmented function inputs

I have the following declared: Public Declare Function GetSystemMetrics Lib "user32.dll" (ByVal X0_Y1 As Long) As Long It grabs the monitor resolution. So that in the future I remember to type 0 for x resolution and 1 for y resolution I have name...
more »

2017-05-02 14:05 (1) Answers

Split a cell and remove duplicates?

I am doing cut sets and path sets for a project for school. I have a LONG list of data similar to the following 1 9 9 9 9 9 16 1 9 9 9 9 9 17 1 9 9 9 9 9 15 1 9 9 9 9 9 18 1 9 9 9 9 9 19 1 9 9 9 9 9 20 1 9 9 9 9 9 21 1 9 9 9 9 10 16 1 9 9 9...
more »

2017-04-15 21:04 (2) Answers

#Value error using custom function in Excel

I'm getting a #VALUE error when I try to call my custom function. All it is supposed to do is a bit of math. Does anyone see what could be wrong here? I copied this one off the internet: Source:
more »

2017-04-05 19:04 (1) Answers

UDF with infinite parameters

I am writing a User Defined Function (UDF) which take some cells as argument. These cells contain the same data but with a different level of precision; the function show the best precision available. The argument of the funcion are written in the ...
more »

2017-04-04 10:04 (4) Answers

Function of function(functional) in Maxima

I want to define a function of a function ( maybe functional) in Maxima. For example, I want to define T in the code: f(r,GN,M)::=(2*GN*M^2)*(2*GN*M/r-(2*GN*M/(2*r))^2); X(r,GN,M) ::= 2*GN*M/(2*r)+2*GN*M/r*(1-2*GN*M/(4*r)); T(r,GN,M) ::= diff(f(r,GN...
more »

2017-03-29 09:03 (2) Answers

VBA: Testing for perfect cubes

I'm trying to write a simple function in VBA that will test a real value and output a string result if it's a perfect cube. Here's my code: Function PerfectCubeTest(x as Double) If (x) ^ (1 / 3) = Int(x) Then PerfectCubeTest = "Perfect...
more »

2017-03-08 19:03 (4) Answers

Excel UDF weighted RANDBETWEEN()

Well not really RANDBETWEEN(). I'm trying to create a UDF to return the index of a number within an array, where the larger the number the more likely it is to be chosen. I know how to assign probabilities to random numbers in a worksheet (i.e. usi...
more »

2017-02-10 13:02 (3) Answers

Why can't a single-letter UDF name use C or R?

I can write the following valid VBA functions: Public Function C() As Long C = 5 End Function Public Function R() As Long R = 6 End Function But I can't use them in an Excel formula, without qualifying them with the VBA project or module name...
more »

2017-01-27 01:01 (2) Answers

Excel VBA UDF autocomplete

I realize there are thousands of comments and hundreds of questions related to UDF tooltips on this website, external forums and miscellaneous discuss groups. I am new to VBA and Excel really, but have learned a lot recently. With that time I've put ...
more »

2017-01-16 03:01 (0) Answers

EXCEL VBA user defined function - else without if

The attached code is run on VBA, but I do not understand why there is an error says else without if or if without end if. I am pretty sure that I have matched every end if with if statement. Sub teee() is just for testing the decimalize function. I...
more »

2016-10-27 13:10 (2) Answers