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

    Major UDF Problem (Excel 2000 >)

    Hi all,
    i need a lot of help here!
    I and a college are trying to build a formula that incorporates argument descriptions in the function palette! I am using the info from <post#=434089>post 434089</post#> to base my code on. Unfortunately I am running into problems with regards the unregistering and also the auto close events. I just cant seem to get the debugs away and get this function working well.

    If anyone is up for a challenge, please assist me. thanx in advance!

    See the attachment file. The sheet called Tests(2), cell B19...contains the UDF.
    Regards,
    Rudi

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

    Re: Major UDF Problem (Excel 2000 >)

    1. Why don't you have Option Explicit in the modules? Apparently you STILL haven't turned on 'Require Variable Declaration'! There are several undeclared variables.

    2. You register only one function (NPVYC), so it doesn't make sense to unregister 3 items (For i = 1 To 3) in the Auto_Close macro. Use For i = 1 To 1 or take out the loop entirely.

    3. Why in heaven's name do you call Auto_Open and Auto_Close in the NPVYC function itself? Auto_Open will run automatically when the workbook is opened, and Auto_Close when the workbook is closed. You should remove them from NPVYC.

    4. The instruction

    If Yield_Range(counter) = "" Or 0 Then

    is incorrect, you cannot combine conditions this way. It should be

    If Yield_Range(counter) = "" Or Yield_Range(counter) = 0 Then

    With the above modifications, the function returns a result; I can't judge whether it is what you want.

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

    Re: Major UDF Problem (Excel 2000 >)

    I still humbly accept your repremands great instructor Hans <img src=/S/bow.gif border=0 alt=bow width=15 height=15>, but to justify myself I will inform you that I have learned my lessons clearly under your guidance a long time ago. I have become very particular regarding variable declaration and the likes! My home PC have Option Explicit activated in the options of VBA, and I realise that Auto_Open and Close are independent macros that run on open and closeing of Excel.

    The workbook I sent you was the raw copy from a college of mine that I passed on to the lounge. I attempted to try and fix the problems in the file but did not get too much great results as I am unclear with the code of Laurent Longre! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I decided to send the original as I probably stuffed up some code under the trial and error adjstments I made in my copy.

    I have made adjustments based on your guidance in the previous post, but i now am getting this error each time I open the workbook! Any ideas now? <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Thanx.
    Regards,
    Rudi

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

    Re: Major UDF Problem (Excel 2000 >)

    No idea without seeing the actual workbook. I have attached the version you posted with the modifications I proposed; I don't get error messages when I open or close it.

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

    Re: Major UDF Problem (Excel 2000 >)

    I noticed that when I edit the Auto_Open macro to test the descriptions it causes the error. See attachment.
    The moment I take out the words " test description", it works again. Is this not the area to describe the functions arguments? Why when I cange the descriptions, does it debug with the error in the previous post!?
    Sorry for being a nuisance with this!
    PS...this happens in the file that you revised for me!
    Regards,
    Rudi

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

    Re: Major UDF Problem (Excel 2000 >)

    It seems that there is an upper limit of 97 characters to the length of the string containing the argument descriptions. As soon as you make it 98 characters, the code bombs.

    I don't have the slightest idea how Laurent Longre's code actually works, so I don't know whether this is an intrinsic limitation of argument descriptions.

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

    Re: Major UDF Problem (Excel 2000 >)

    Thanx again for all your assistance Hans. <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22> <img src=/S/ribbon.gif border=0 alt=ribbon width=15 height=15>

    I will continue to dabble around and see what else I can do!

    PS...I understand when you say, "I don't have the slightest idea how Laurent Longre's code actually works", but are you able to shed more light on the ExecuteExcel4Macro Method of Application? How is this used, as I've seen it in the past a few time, and the help files only give this as an example! :
    Worksheets("Sheet1").Activate
    Range("C3").Select
    MsgBox ExecuteExcel4Macro("GET.CELL(42)")...?
    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Regards,
    Rudi

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

    Re: Major UDF Problem (Excel 2000 >)

    The only thing I know about it is that up to version 4, Excel did not have VBA. Macros were written on macro sheets, with each instruction in a cell in the form of a formula using macro functions.

    The ExecuteExcel4Macro method is included in Excel VBA for compatibility reasons; it can be used to execute those macro functions. It is still used sometimes because there are macro functions that have no VBA equivalents, such as GET.CELL, REGISTER and UNREGISTER.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Major UDF Problem (Excel 2000 >)

    I have a copy of the XL4 macro help file. it says this about the REGISTER function (see attachement):


    You can find the macro helpfile here.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Major UDF Problem (Excel 2000 >)

    Thankyou Hans and Pieterse.
    It seems I have enough additional info to keep me busy over the holidays that are coming up soon!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    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
  •