MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

user defined functions

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

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
If P >= 12 And P < 37 Then
Round25 = Pound & "25"
Exit Function
If P >= 37 And P < 62 Then
Round25 = Pound & "50"
Exit Function
If P >= 62 And P < 87 Then
Round25 = Pound & "75"
Exit Function
End If
End If
End If
End If
End Function



7 Answers Found

Answer #1    Answered By: Ella Brown     Answered On: Nov 17

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

Answer #2    Answered By: Liam Bouchard     Answered On: Nov 17

Yeah, the same for me???

The udf seems fine.

Answer #3    Answered By: Hababah Younis     Answered On: Nov 17

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

Answer #4    Answered By: Alfonsine Miller     Answered On: Nov 17

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

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
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'

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

Answer #5    Answered By: Fedde Bakker     Answered On: Nov 17

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
for your test?

Answer #6    Answered By: Taylor White     Answered On: Nov 17

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

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,

Answer #7    Answered By: Cay Nguyen     Answered On: Nov 17

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.