 Search:

# user defined functions

Asked By: Oscar    Date: Nov 17    Category: MS Office    Views: 1815

I have a function that works if I pass it a full value, but I
don't know how to get it to work if a cell is passed instead.

ie - if I pass it Round25(£657.98) then it'll return the correct
value, but if I pass it Round25(A1) then it doesn't work.

Would appreciate it if someone could point me in the right directon :)

Function Round25(Money)
Dim P As Integer
Dim Pound As String

P = Right(Money, 2)
Pound = Left(Money, Len(Money) - 2)

If P >= 87 Or P < 12 Then
Round25 = Pound & "00"
Exit Function
Else
If P >= 12 And P < 37 Then
Round25 = Pound & "25"
Exit Function
Else
If P >= 37 And P < 62 Then
Round25 = Pound & "50"
Exit Function
Else
If P >= 62 And P < 87 Then
Round25 = Pound & "75"
Exit Function
End If
End If
End If
End If
End Function

Share:

I tested your function, using both a numeric value  and a cell  reference, in a
worksheet and by calling the function  from another sub and it worked fine
(assuming you're looking for a result of 657.00).

How are you using the function?

One code question: If the decimal is greater than .87 don't you want to round
up?

Yeah, the same for me???

The udf seems fine.

Where are you getting the pound sign from? Are you typing it in? What
*exactly* are the whole cell  formulas that produce the different
results?

What kind of error do you get?
Since you're using string functions  and then applying numeric tests
it begins to get confusing.
It looks (from the name of the function  and the code) as though
you're trying to round to the nearest 25p? (Hope I'm not meddling
here)

To do this with numbers use:

Function Round25(money)
Round25 = Round(money * 4, 0) / 4
End Function

It may not be worth having a function at all, try
=ROUND(A1 * 4, 0) / 4
in a cell.

You may have to format the cells as money to make it look right, also
it rounds 12p down and 13p up, whereas you round 12 up, and similar
at other boundaries; this could easily be adjusted.

If the original data is text you could just strip the £ sign off
and
use numbers to convert then add the £ sign back on (or format the
result as money).

BTW, a bunch of If.. ..then ..else.. end ifs is quite difficult to
fathom when writing macros, have a look at the 'Select Case'
statement.

Is the data you're working on text or values?
In what form do you want the result?

Second thoughts... It *may* be a typing ... that's the type of
variable ... problem combined with the properties for A1 in terms of
number type and number of decimal places. What are you typing into A1

The idea was to either accept a value  to two decimal places wather
from a currency field or a general field with a '£' tagged onto the
front.

The "=ROUND(A1 * 4, 0) / 4" seems to do the job though, so I guess I
should spend more time familiarising myself with what's already on
offer before trying to re-invent the wheel :)

For future reference though - where should I paste the code in order
to get the cell  references to work?

I tried it in modules(worksheet + personal) and the worksheet and all
I was getting was a #Name? error,

Probably best to paste it in a module created by right clicking on
'VBAProject(YourFileName)' in the Project Explorer pane of the VB
Editor, choosing 'insert...', then choosing 'Module'.

Didn't find what you were looking for? Find more on user defined functions Or get search suggestion and latest updates.