Looking for help creating a custom function in Excel.

manbearpig2020

New Member
Joined
Feb 6, 2017
Messages
4
Hello all!

This is my first post on here. From what I have seen, you guys are enormously helpful. I thought I would put forward my questoin

I have hit a wall trying to create a custom function in VBA. I am trying to find unique values in a list of copied values in some column. This is displayed in one column. I then want a list of unique functions to be displayed falling.

I actually have a macro working for that piece of it. I just cannot get the input and output sections to work properly. I want them to look/work like the SUM function. Where the function box can pop up and ask for the inputs and outputs. The output would just be a single cell. The input would be an array.

Does anyone know how to set user designated input and output up?

Thanks!


Code:
Option Explicit
Function GetUniques()
'For reference I have been working from this resource http://www.mrexcel.com/forum/showthread.php?649576-Extract-unique-values-from-one-column-using-VBA
Dim d As Object, c As Variant, i As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 1).End(xlUp).Row
c = Range("A2:A" & lr)
For i = 1 To UBound(c, 1)
  d(c(i, 1)) = 1
Next i
    Range("XFD1048576").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(, 1).Select
    ActiveCell.FormulaR1C1 = "Unique List"
    ActiveCell.Offset(1).Select
    Range(ActiveCell, ActiveCell).Resize(d.Count) = Application.Transpose(d.keys)
End Function
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,446
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

It looks like you are mixing up concepts of procedures and functions in the same VBA code.

Typically, with Functions, you do NOT have pop-ups asking for inputs. Inputs are usually indicated as parameters of the function.
Think of all the built in Excel functions, like SUM, VLOOKUP, COUNTIF, etc. None of them have pop-up messages. You enter the inputs as arguments in your function.
Also, you typically do not use ActiveCell or Selection in functions, nor do you typically hard-code range values.
And functions just return values to the cell where the formula is found in.
See the following link for help: Writing Your Own Functions In VBA

If you want pop-up boxes (input or output), or things placed in multiple cells, you would not use a Function, but rather a Procedure (Sub).

See if that helps clarify things for you. If not, please explain in detail exactly what you are trying to do (what you want to happen). Maybe walk us through an example.
 

manbearpig2020

New Member
Joined
Feb 6, 2017
Messages
4
Joe,

This was helpful, but I will follow up with a better example. I will use VLookUp as an example. Lets say I have Column A that is filled with unhelpful information that should not be moving around.

I would want to run a macro that creates a function GetUniques that asks . The functions arguments page provides much greater detail about what the arguments are actually at work doing in your formula.

Lets go back to VLookUp in excel. In the actual workbook if I type out "=vlookup" , Excel will start asking me to fill out the fields/ arrays in between the columns. I'll displpay that here.

=VLOOKUP(lookup_value,table_array, col_index_num,[range_lookup])

Now back to what I am trying to accomplish. The VLookUp's Arguements page asks us for Lookup_Value, Table_Array, Col_Index_Num, and Range_lookup, just like the Excel function does... Though the open VLookUp Arguments window will tell you what the LookUp_Value is "is the value to be found in the first column of the table, and can be a value, a reference, or a text string." That's as long as your cursor is in the VlookUp_Value field.

Then the macro I would be set up like VLOOKUP and ask for input array and the output field. I'd love if I could get a polished GetUniques Arguement page. That would really help direct the end user much better.

Thanks!!!

John

Column AColumn BColumn C
Row 1FrogAll CodesDistinct Codes
Row 2Frog456
Row 3Frog456
Row 4Frog678
Row 5Frog678
Row 6Frog890
Row 7Frog543
Row 8Frog543
Row 9Frog234
Row 10Frog213
Row 11Frog905
Row 12Frog905
Data After Macro:

Column AColumn BColumn C
Row 1FrogAll CodesDistinct Codes
Row 2Frog456456
Row 3Frog456678
Row 4Frog678890
Row 5Frog678543
Row 6Frog890234
Row 7Frog543213
Row 8Frog543905
Row 9Frog234
Row 10Frog213
Row 11Frog905
Row 12Frog905




<colgroup><col width="64" span="4" style="width: 48pt;"></colgroup><tbody>
</tbody>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,446
Office Version
  1. 365
Platform
  1. Windows
I imagine that would be pretty involved. You might have to involve User Forms to get that look that you currently see with existing native functions, but I am note sure how you would incorporate that in using Custom Functions (User Forms more commonly interact with Sub Procedures).

This may be a tall order, one that might fall more in line with Consulting work. If you are interested in that, you can reach out to MrExcel Consulting here: MrExcel.com | Excel Resources | Excel Seminars | Excel Products
 

manbearpig2020

New Member
Joined
Feb 6, 2017
Messages
4
Joe,

You were right I was going way too deep with it. I ended up doing something more simple with the Count function and automating the parameter selection in VBA. Thanks for your help!!!


Best!

ManbearPig
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,446
Office Version
  1. 365
Platform
  1. Windows
Great! Glad you were able to find a working solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,860
Messages
5,525,244
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top