User Defined Functions

[ashishbarot]

Pls pls pls help me for this prob......

I want to create the user defined function in excel in which i will assign the value to a char and after that i should b able to use it in formula.

Eg: for "FL" i will assign 1
for "LF" i will assign 2
for "VV1" i will assign 3
for "VV2" i will assign 4

and so on.......then i want to use this to compare both figures like
i will put the formula like if(VV1 < VV2,true,false) result should b true coz VV1 is having value of 3 and VV2 is having 4.

So basically how we create the custom lists i want to give the priority to my own list and want to use it in formula.

I hope some one from all the masters will help me out in this topic

Waiting for your reply.

 

[ashishbarot]

BUT STILL MY PROB IS NOT SOLVED MR.Hermanito

WHEN I USE HIGHEST OF THIS NAMES

FOR EG: VV1 2 AND VV2 3........IT GIVES ME 3 NOT THE VV2
MAX(VV1,VV2)
RESULT IS 3
I WANT VVS2

 

[Hermanito]

Starting to use all-caps is considered yelling and rude.
I think I wasted enough of my time on you

 

[ashishbarot]

Hey no no man....that all caps came only by mistake my keyboard caps was on thats Y it came....come on there is nothing to feel bad

 

[ashishbarot]

So u mean to say that i have will have to create the function for every value i.e.

basically i have these list

<title>Excel Jeanie HTML</title>
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="text-align: right;">1</td> <td>IF</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: right;">2</td> <td>VVS1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: right;">3</td> <td>VVS2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: right;">4</td> <td>VS1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: right;">5</td> <td>VS2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: right;">6</td> <td>SI1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: right;">7</td> <td>SI2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: right;">8</td> <td>SI3</td></tr></tbody></table>
Can u help me out for the same.......i just want to use the max function with these values, so no prob if i cant use '+'

 

[Hermanito]

Okay then; you'll get the benefit of the doubt...

Have you tried the User Defined functions like I showed in the post with code in it?
And first you talked about VV1 and VV2 and now you have all of a sudden a VVS2. You need to be more clear and more precise about what you are trying to do and what results you are expecting...

 

[Hermanito]

basically i have these list


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: rgb(255,255,255)" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD>IF</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD>VVS1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD>VVS2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD>VS1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">5</TD><TD>VS2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">6</TD><TD>SI1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">7</TD><TD>SI2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">8</TD><TD>SI3</TD></TR></TBODY></TABLE>

If you put it like that, the defined names should be in the first column. The column next to it is only to easily see what each value is named, but the names themself should be defined on the cells with the values.
Also, like already mentioned, IF will not be a valid name, first because there already is a column IF, and second because IF is a reserved keyword.

 

[yytsunamiyy]

Try the following:

Place in a helper sheet called "List of Values" your list like this:

Excel Workbook
	A
1	FL
2	L
3	VV1
4	VV2
List of Values

Then, on your actual sheet you can use something like the following:

Excel Workbook
	A	B	C	D
1	Compare:	VV1	with:	VV2
2	VV2
Formulas

Cell Formulas
Range	Formula
A2	=IF(MATCH($B$1,'List of Values'!$A$1:$A$4,0)

edit:

For MAX / MIN you can use the following:

Excel Workbook
	A	B	C	D
4	Find max of:	VV1	VV2	FL
5	VV2
Formulas

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[ashishbarot]

Sir i tried like this;.......but how to use this function with Min i.e.

I will have the data like this

VVS1 VVS2 SI1 .......I want see which one is the highest from them result should b VVS1


Public Function IFL() As Long
IFL = 1
End Function

Public Function VVS1() As Long
VVS1 = 2
End Function

Public Function VVS2() As Long
VVS2 = 3
End Function

Public Function VS1() As Long
VS1 = 4
End Function

Public Function VS2() As Long
VS2 = 5
End Function

Public Function SI1() As Long
SI1 = 6
End Function

Public Function SI2() As Long
SI2 = 7
End Function

Public Function SI3() As Long
SI3 = 8
End Function

 

[yytsunamiyy]

see edited post #17

 

[Hermanito]

When you have the functions ready, use them like this:
'=MIN(VVS1(),VVS2(),IFL(),...)'