Looking for help creating a custom function in Excel.

[manbearpig2020]

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!

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

 

[Joe4]

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]

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 A	Column B	Column C
Row 1	Frog	All Codes	Distinct Codes
Row 2	Frog	456
Row 3	Frog	456
Row 4	Frog	678
Row 5	Frog	678
Row 6	Frog	890
Row 7	Frog	543
Row 8	Frog	543
Row 9	Frog	234
Row 10	Frog	213
Row 11	Frog	905
Row 12	Frog	905
Data After Macro:
	Column A	Column B	Column C
Row 1	Frog	All Codes	Distinct Codes
Row 2	Frog	456	456
Row 3	Frog	456	678
Row 4	Frog	678	890
Row 5	Frog	678	543
Row 6	Frog	890	234
Row 7	Frog	543	213
Row 8	Frog	543	905
Row 9	Frog	234
Row 10	Frog	213
Row 11	Frog	905
Row 12	Frog	905

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

 

[Joe4]

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]

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]

Great! Glad you were able to find a working solution.