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.

 

[Hermanito]

I'm not sure a user defined function will be the solution for you.
How about this:
- make an overview on a sheet where you create the list
NAME - Value
VV1 - 3
VV2 - 4
etcetera in two columns...
then create names for the cells in the second column (easiest way to do this is: select the cell, enter the name where excel shows the coordinates, e.g. E4, change to VV1, but you cannot use names that would already be a valid cell; you cannot use B2 as name for instance)
When the names are all created you can use them just like that in your formulas in other cells.
Let me know if this works for you...

 

[Yard]

Note that some of those references won't work in Excel 2007, since VV1 and VV2 are cell addresses.

 

[Hermanito]

Good catch Yard... I tried those in 2003 and that worked fine, but indeed, 2007 has a lot more columns... all the way up to XFD instead of IV in earlier versions.
You can easily solve this by prefixing all your names with var or something...

 

[ashishbarot]

thnx for your reply, but can u xplain the same with an example so that understand more clearly

 

[Hermanito]

okay... I will try

- Enter value 10 in cell A1
- Enter 'myValue' in the box where it says A1 when you have A1 selected (this should be right above the A column header)
- Enter the formula '= myValue * myValue' in cell A2
- A2 should now show 100

I hope you get the idea then...

 

[ashishbarot]

And also dont forget that i want to use this function or name in any of my excel file, it means it should not be limited to that particular sheet

 

[Hermanito]

I'm not sure defined names work outside the workbook they are defined in. They do work across worksheets, but not across workbooks I'm afraid.

 

[ashishbarot]

then i'll have to create this list in every sheet / file,

pls there is NO other way OUT.

No one can help me ????........

 

[Hermanito]

You could define user defined functions in an add-in excelfile that you keep loaded all the time. The functions would need to be defined in a module, not in a sheet, and would look like this:

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

But then you cannot use formulas like 'VV2 + VV1', you would have to use them as follows: 'VV2() + VV1()'