IF functions

[Vic]

Hope someone can put me right!

I have a column of figures, say column A, and next to each item (Column B)
is an "IF" formula. As an "IF" formula can only accept 7 or 8 different
possibilities I have had to put similar formulae in Columns C an D since
there are about 20 different possibilities. If I was to make this formula a
Function in VBA:-
-where do I store it if I want to distribute the spreadsheet to other
people, and
-What impact will this have on the size of the file which at the moment is
large and which I am trying to reduce through measures such as this. I'm a
relative beginner at VBA and the code takes me a long time to write so I
thought I would try and get some information up front before potentially
wasting my time.

Thanks in anticipation

Vic

[Peter Beach]

Vic,

To create a user-defined function press Alt F11 to go into VBA and then from
the menu select Insert / Module. This will create a new module which is as
much part of the spreadsheet as Sheet1, Sheet2 etc. When you send the
spreadsheet to other people the user-defined functions will be part of the
spreadsheet in just the same way as the other components are.

Generally I would expect using user-defined functions will reduce the size
of the spreadsheet but will increase its calculation time.

If you are unfamiliar with VBA you should check out the Select Case
construct which is probably what you are after. You might end up with some
code like:

Function MyComplexIf(InpVal As Double)
Dim RetVal as Variant

Select Case InpVal
Case 1 To 10:
' Put some code in here to set RetVal
' Something like RetVal = 1234
Case 11, 14, 15:
' Put some code in here to set RetVal
Case Else:
' Put some code in here for "other" cases
End Select
MyComplexIf = RetVal
End Function

HTH

Peter Beach
Excel MVP

Vic <vicb...@hotmail.com> wrote in message
news:84oqnh$7vc$1...@uranium.btinternet.com...

[Vic]

Thanks Peter

I had a look and I'm making progress!

Vic

"Peter Beach" <pbe...@globalnet.co.uk> wrote in message
news:#JJsQgaV$GA.204@cppssbbsa04...

[Tom Ogilvy]

You also might want to look at the Choose worksheet function. It may be
applicable although not as flexible in terms of criteria as the IF function.

Regards,
Tom Ogilvy

Vic wrote in message <84pudj$kk$1...@neptunium.btinternet.com>...