Custom Function! (Excel VBA 2002)

Topic

New Reply

Hi again!
In the attachment is a worksheet containing a custom function. Could I get some assistance with regards returning the calculation in the format shown in column G in the attachment?!

Another question!
Is it possible to call a sub procedure from within a function procedure? Why I ask… I don’t believe I can return the answer from a function procedure to the spreadsheet in a centered format. What I want to know is if its possible to embed the name of a sub procedure inside the function procedure. I tried it, but it debugs. I don’t know if i am going about it in the right manner. See example:

Function PercInc(Current_Year, Previous_Year)
Result = (Current_Year - Previous_Year) / Previous_Year
PercInc = Format(Result, "#.00%")
CenterVal
End Function
Sub CenterVal()
    ActiveCell.HorizontalAlignment = xlCenter
End Sub

Could anyone assist with these two questions please?
Thanx

Reply | Quote

Replies

Can’t comment on your first question since there is no attachment.

I think the problem with your CenterVal is that a User Defined Funciton is not allowed to make changes to a worksheet, except by returning a result. But maybe one of our Excel experts will have a suggestion for you.

StuartR

Reply | Quote

Thanx Stuart and Hans.
Sorry about the attachment, here it is…

Could you help me with the format of the returned value. I would like to know how to specify the format in the function procedure to format to % and show negative values in red! (I understand now that a function cannot do actions on a sheet! Thanx)

You still have not mentioned if it is possible if a sub procedure can be called from within a function procedure! If a function procedure cannot center the data, can I get a sub procedure to center it for me, while called from the function!???

Thanx

Reply | Quote

About calling procedures from custom functions: yes, functions can call other functions and procedures, BUT any instruction that modifies the workbook will be ignored or cause an error if the function is used as a worksheet function. Microsoft has made it impossible to modify a workbook from a worksheet function, whether it be directly or indirectly. The only thing a worksheet function can do is return a value in the cell. That, of course, can trigger an action – for example, the Worksheet_Calculate event will occur.

Reply | Quote

About calling procedures from custom functions: yes, functions can call other functions and procedures, BUT any instruction that modifies the workbook will be ignored or cause an error if the function is used as a worksheet function. Microsoft has made it impossible to modify a workbook from a worksheet function, whether it be directly or indirectly. The only thing a worksheet function can do is return a value in the cell. That, of course, can trigger an action – for example, the Worksheet_Calculate event will occur.

Reply | Quote

I’m afraid that what you want is neither possible, nor desirable.

You might have the function return a value as a formatted string, but the disadvantage is that the return value cannot be used directly in further calculations, since it is a string. It is much better to make the function return a numeric result, so that the cell value can be used in other formulas.

You cannot make a function specify the colo(u)r of the return value, or its alignment.

You should set the formatting of the cell separately, either manually, or through a macro.

Reply | Quote

Thanx…you are clearing up lots 4 me now!
I see there is a FormatPercent function too, but this also returns the result as text! I will thus take your good advice to heart and have the user format from the worksheet.

Reply | Quote

Without addressing the cell value (using your UDF) this code should provide the formatting you’re after:

Sub UDFFormat()

    With ActiveCell
        .HorizontalAlignment = xlCenter
        .NumberFormat = "0.00%_ ;[red ]-0.00% "
    End With
    
End Sub

Alan

Reply | Quote

A hearty thanx to all who corrected, advised and replied to me. All answers have been useful and I have learned from the query I posted earlier. You have all been a great help!!!

Reply | Quote

A hearty thanx to all who corrected, advised and replied to me. All answers have been useful and I have learned from the query I posted earlier. You have all been a great help!!!

Reply | Quote

Without addressing the cell value (using your UDF) this code should provide the formatting you’re after:

Sub UDFFormat()

    With ActiveCell
        .HorizontalAlignment = xlCenter
        .NumberFormat = "0.00%_ ;[red ]-0.00% "
    End With
    
End Sub

Alan

Reply | Quote

Thanx…you are clearing up lots 4 me now!
I see there is a FormatPercent function too, but this also returns the result as text! I will thus take your good advice to heart and have the user format from the worksheet.

Reply | Quote

I’m afraid that what you want is neither possible, nor desirable.

You might have the function return a value as a formatted string, but the disadvantage is that the return value cannot be used directly in further calculations, since it is a string. It is much better to make the function return a numeric result, so that the cell value can be used in other formulas.

You cannot make a function specify the colo(u)r of the return value, or its alignment.

You should set the formatting of the cell separately, either manually, or through a macro.

Reply | Quote

Thanx Stuart and Hans.
Sorry about the attachment, here it is…

Could you help me with the format of the returned value. I would like to know how to specify the format in the function procedure to format to % and show negative values in red! (I understand now that a function cannot do actions on a sheet! Thanx)

You still have not mentioned if it is possible if a sub procedure can be called from within a function procedure! If a function procedure cannot center the data, can I get a sub procedure to center it for me, while called from the function!???

Thanx

Reply | Quote

Can’t comment on your first question since there is no attachment.

I think the problem with your CenterVal is that a User Defined Funciton is not allowed to make changes to a worksheet, except by returning a result. But maybe one of our Excel experts will have a suggestion for you.

StuartR

Reply | Quote

Stuart is correct. User defined worksheet functions can only return a value; they cannot modify a worksheet in any other way.

By the way, you use an undeclared variable Result in your function. That would indicate that you don’t require explicit declaration of variables. That is dangerous! I strongly recommend that you select Tools | Options… in the Visual Basic Editor and tick the “Require variable declaration” check box. This will add a line Option Explicit to all new modules you create; you will have to add this line manually to already existing modules.

See (for example) post 380993 for an explanation why it is important to require explicit variable declaration.

Reply | Quote

Thanx 4 the advice Hans. The code was just a sample I set up for the post. I do have variable declaration activated in my original file. I have, by past experience, learned to switch it on and KEEP it on!
Much appreciated!

Reply | Quote

Thanx 4 the advice Hans. The code was just a sample I set up for the post. I do have variable declaration activated in my original file. I have, by past experience, learned to switch it on and KEEP it on!
Much appreciated!

Reply | Quote

Stuart is correct. User defined worksheet functions can only return a value; they cannot modify a worksheet in any other way.

By the way, you use an undeclared variable Result in your function. That would indicate that you don’t require explicit declaration of variables. That is dangerous! I strongly recommend that you select Tools | Options… in the Visual Basic Editor and tick the “Require variable declaration” check box. This will add a line Option Explicit to all new modules you create; you will have to add this line manually to already existing modules.

See (for example) post 380993 for an explanation why it is important to require explicit variable declaration.

Reply | Quote

As several people have already said, you can not change anything on the worksheet from a UDF called from the worksheet, even if you call a sub procedue.

Although, I think that it is much better to format the cells containing the UDF and just let it return the value. However, you could put the code below into the worksheet change event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ocell As Range
    If Intersect(Target, Range("B5:C20")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each ocell In Intersect(Target, Range("B5:C20"))
        With Range("D" & ocell.Row)
            .Value = (Range("C" & ocell.Row).Value - Range("B" & ocell.Row).Value) / Range("B" & ocell.Row).Value
            .NumberFormat = "0.00%_ ;[red ]-0.00%;0%"
        End With
    Next ocell
    Application.EnableEvents = True
End Sub

Reply | Quote

The one thing to add to this sub is the center function and then you would have all that you asked for.

~~~
With Range(“D” & ocell.Row)
.Value = (Range(“C” & ocell.Row).Value – Range(“B” & ocell.Row).Value) / Range(“B” & ocell.Row).Value
.NumberFormat = “0.00%_ ;[red ]-0.00%;0%”
.HorizontalAlignment = xlCenter
End With
~~~

Hope this helps

Reply | Quote

Thanx Paul. I did do that afterwards.

Reply | Quote

Thanx Paul. I did do that afterwards.

Reply | Quote

The one thing to add to this sub is the center function and then you would have all that you asked for.

~~~
With Range(“D” & ocell.Row)
.Value = (Range(“C” & ocell.Row).Value – Range(“B” & ocell.Row).Value) / Range(“B” & ocell.Row).Value
.NumberFormat = “0.00%_ ;[red ]-0.00%;0%”
.HorizontalAlignment = xlCenter
End With
~~~

Hope this helps

Reply | Quote