Looking for help creating a custom function in Excel.

M

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
 
Sort by date Sort by votes
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.
 
Upvote 0
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


<colgroup><col width="64" span="4" style="width: 48pt;"></colgroup><tbody>
[TD="width: 64"][/TD]
[TD="class: xl66, width: 64"]Column A[/TD]
[TD="class: xl66, width: 64"]Column B[/TD]
[TD="class: xl66, width: 64"]Column C[/TD]

[TD="class: xl67, width: 64"]Row 1[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64"]All Codes[/TD]
[TD="class: xl66, width: 64"]Distinct Codes[/TD]

[TD="class: xl67, width: 64"]Row 2[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]456[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 3[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]456[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 4[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]678[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 5[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]678[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 6[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]890[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 7[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]543[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 8[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]543[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 9[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]234[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 10[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]213[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 11[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]905[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 12[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]905[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl68, colspan: 2"]Data After Macro:[/TD]

[TD="class: xl69, width: 64"][/TD]

[TD="class: xl67, width: 64"]
[/TD]
[TD="class: xl66, width: 64"]Column A[/TD]
[TD="class: xl66, width: 64"]Column B[/TD]
[TD="class: xl66, width: 64"]Column C[/TD]

[TD="class: xl67, width: 64"]Row 1[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64"]All Codes[/TD]
[TD="class: xl66, width: 64"]Distinct Codes[/TD]

[TD="class: xl67, width: 64"]Row 2[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]456[/TD]
[TD="class: xl66, width: 64, align: right"]456[/TD]

[TD="class: xl67, width: 64"]Row 3[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]456[/TD]
[TD="class: xl66, width: 64, align: right"]678[/TD]

[TD="class: xl67, width: 64"]Row 4[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]678[/TD]
[TD="class: xl66, width: 64, align: right"]890[/TD]

[TD="class: xl67, width: 64"]Row 5[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]678[/TD]
[TD="class: xl66, width: 64, align: right"]543[/TD]

[TD="class: xl67, width: 64"]Row 6[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]890[/TD]
[TD="class: xl66, width: 64, align: right"]234[/TD]

[TD="class: xl67, width: 64"]Row 7[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]543[/TD]
[TD="class: xl66, width: 64, align: right"]213[/TD]

[TD="class: xl67, width: 64"]Row 8[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]543[/TD]
[TD="class: xl66, width: 64, align: right"]905[/TD]

[TD="class: xl67, width: 64"]Row 9[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]234[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 10[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]213[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 11[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]905[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, width: 64"]Row 12[/TD]
[TD="class: xl66, width: 64"]Frog[/TD]
[TD="class: xl66, width: 64, align: right"]905[/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl68"][/TD]

[TD="class: xl68"]


[/TD]

[TD="class: xl68"][/TD]

</tbody>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Great! Glad you were able to find a working solution.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
347
Excel Jason
E
T
  • Question Question
Select from a term list, but input the corresponding key
Replies
1
Views
195
sijpie
sijpie
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
618
Anthony47
A
G
  • Question Question
How can i extract the correct leave time on this code?
Replies
1
Views
593
rlv01
R
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
1K
Alex Blakenburg
A

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top