Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Custom Function! (Excel VBA 2002)

    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:
    <pre>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</pre>


    Could anyone assist with these two questions please?
    Thanx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Custom Function! (Excel VBA 2002)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Function! (Excel VBA 2002)

    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>post 380993</post#> for an explanation why it is important to require explicit variable declaration.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Function! (Excel VBA 2002)

    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
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Function! (Excel VBA 2002)

    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!
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Function! (Excel VBA 2002)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Function! (Excel VBA 2002)

    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.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Function! (Excel VBA 2002)

    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.
    Regards,
    Rudi

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function! (Excel VBA 2002)

    Without addressing the cell value (using your UDF) this code should provide the formatting you're after:<pre>Sub UDFFormat()

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

    End Sub
    </pre>

    Alan

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function! (Excel VBA 2002)

    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:

    <pre>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
    </pre>

    Legare Coleman

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    THANX a stack!!!!

    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!!!
    Regards,
    Rudi

  12. #12
    New Lounger
    Join Date
    Sep 2004
    Location
    York, Virginia, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function! (Excel VBA 2002)

    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

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Function! (Excel VBA 2002)

    Thanx Paul. I did do that afterwards.
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •