Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    I am having this very strange problem with a VBA function that I wrote for Windows XP Excel 2002. When I wrote the function, it worked. Then I saved and closed the workbook. Next time I opened it, I got a message saying that the macro was not authenticated and because of that would be deactivated. The system advised me to either change the security setting to low or better to digitally sign the macro. I chose the latter. I created the certificate and then signed the macro. After that I could open the file without the system generating the deactivation message yet the function still did not work. Whenever I called the function from a worksheet Excel simply did not recognize the function name. Of course I checked the function call 25 times and found no errors in it. I also wrote a VBA subroutine that read the input parameters from Excel spreadsheet and then called the function from VBA and it worked, but when I called it directly from Excel spreadsheet it did not.

    Anybody knows what I can do to fix that? Appreciate your help!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    <P ID="edit" class=small>(Edited by JohnBF on 06-Apr-04 17:36. )</P>Are you aware that any function called from another workbook must use the WB name as part of the argument, such as

    =PERSONAL.XLS!myFunction(<arguments...>)

    Edit. I should also note that functions don't work in spreadsheet cells if they are in the "ThisWorkBook" Object; they must be in a Module.

    If neither of those help not it, can we see the function?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    Post deleted by vbelyaev

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

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    I put your function in a workbook and saved it. It doesn't cause problems on Excel 2002 SP-2. My macro security is set to Medium, so I get the usual warning that the workbook contains macros; after confirming that I want to enable macros, the function works OK. So I don't think the code itself is problematic.

    Have you installed SP-3 for Office XP?

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    Thank you for looking at the function, Hans. That is what I thought. It does work. In fact it works on my computer at work (I have windows 2000 here), but refuses to do so under XP at home. To be honest I do not know what either SP-2 or SP-3. Could you please tell me what it is or maybe give me a reference to where I can look it up if the answer is too long? Thanks again.

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

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    (I tested in Excel 2002 on Windows XP Pro and Windows XP Home - no problems)

    From time to time, Microsoft releases updates to its software, to squash bugs (and introduce new ones). These are called service packs (SP), or service releases (SR). For Office XP/2002, three servica packs have been released up to now: SP-1, SP-2 and SP-3. You can check which one you have as follows:

    In Excel (on the problem PC), select Help | About Microsoft Excel. The first line below the "About Microsoft Excel" caption displays the version. On my system, it says Microsoft Excel 2002 (10.5815.4219) SP-2, indicating that I have installed service pack # 2 (SP-2) of Excel 2002. The number between brackets is the build number, which is not really important here.

  7. #7
    New Lounger
    Join Date
    Apr 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    Thanks a lot, Hans. I will try that tonight.

    Victor

  8. #8
    New Lounger
    Join Date
    Apr 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    I found what the problem was. Apparently Visual Basic gets all confused if you name a module in the project explore window the same way as the function (And how else am I supposed to name it?). I changed the name of the module to BlackScholesF keeping everything else unchanged and it is working now.

    Thanks for your help guys.

    Victor

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

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    I usually give modules names beginning with bas (for Basic), e.g. basBlackScholes.
    (And for completeness: class modules get names such as clsAppEvents, and user forms names such as frmInput)

  10. #10
    New Lounger
    Join Date
    Apr 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enable function in Excel 2002 (Windows XP) (VB/Excel 2002/Windows XP)

    That is a very good idea, Hans. I might use the naming nomenclature. Thanks again.

    Victor

Posting Permissions

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