Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    XP Broke Grade Macro (XP)

    I'm not a VBA programmers but years ago, members of this form helped me write an Excel function that I could use to convert letter grades to points. For example,

    =GRADE(B,100)

    would return a value of 85. I've shown the code at the bottom of this message.

    It worked fine for years in Excel 97 and Excel 2000 but returns #NAME? in Excel XP. Can someone tell me how to fix this? (I'm not a programmer so if someone knows what to do, I'll need very simple instructions.)

    Thanks
    Ronny


    --------------------------CODE------------------------------
    Function Grade(Cell, TotalCell)
    Application.Volatile
    Grade = 0
    If UCase(Cell) = "A+" Then Grade = 1.03
    If UCase(Cell) = "A" Then Grade = 1#
    If UCase(Cell) = "A-" Then Grade = 0.9
    If UCase(Cell) = "B+" Then Grade = 0.89
    If UCase(Cell) = "B" Then Grade = 0.85
    If UCase(Cell) = "B-" Then Grade = 0.8
    If UCase(Cell) = "C+" Then Grade = 0.79
    If UCase(Cell) = "C" Then Grade = 0.75
    If UCase(Cell) = "C-" Then Grade = 0.7
    If UCase(Cell) = "D+" Then Grade = 0.69
    If UCase(Cell) = "D" Then Grade = 0.65
    If UCase(Cell) = "D-" Then Grade = 0.6
    If UCase(Cell) = "F" Then Grade = 0
    If UCase(Cell) = "0" Then Grade = 0
    Grade = Grade * 100 * TotalCell
    Grade = Int(Grade)
    Grade = Grade / 100
    End Function
    Ronny Richardson

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XP Broke Grade Macro (XP)

    Ronny,

    I inserted the code you gave into a standard module.

    On a worksheet
    column A - I inserted various letter grades (20 rows)
    column B - I entered =GRADE(A1,100), =GRADE(A2,100), etc next to each letter grade.

    It seemed to work just fine in my Excel 2002.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: XP Broke Grade Macro (XP)

    It seems that Excel is not finding the exact location of the function. Are you sure that in Excel is set up to run Macro's from all locations rather than running just the macro's for a paticular sheet ?

    A quick way to find out is to copy the entire procedure to the Macro module for the open workbook and see if the function runs.

    Regards,

    TD

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: XP Broke Grade Macro (XP)

    It worked fine for years with Excel 97 and 2000. I went back and tried it again in Excel XP and it still does not work but this time I did notice that it gave me an error message complaining that a macro was unsigned, what ever that means.

    Ronny
    Ronny Richardson

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XP Broke Grade Macro (XP)

    Check your Macro Security levels. If you want to have a signature, use SelfCert.exe - see the explanation in the second paragraph of <!post=this post,307441>this post<!/post>. HTH
    Gre

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: XP Broke Grade Macro (XP)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Ronny:

    Microsoft at its main site http://www.microsoft.com at the office web page has a link to "Templates"
    If you select this you will go to the Template page. At the bottom there is a category called education
    If you select this category you will see a selection for Teachers then a sub-selection fro tests and grades.

    One of these seems to do exactly what you desire. Just download and save on you computer.
    Regards,

    TD

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: XP Broke Grade Macro (XP)

    >In order to set up a simple ceretificate, one can use SelfCert.exe in the C:Program FilesMicrosoft OfficeOffice
    >directory. The certificate will then be available for signing through the Tools option on the VBE Worksheet
    >Menu Bar. You do have to lower your default Macro security level, when first running the Workbook
    >containing the signed code, to get the option to trust the signer.

    It does not appear to work with XP. I installed the SeflCert software, created the certificate, loaded the Excel file containing the macro at Low security settings and tried to sign it. When I saved the file, it said the certificate was defective and would not be used.

    Ronny
    Ronny Richardson

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: XP Broke Grade Macro (XP)

    I have years of grades sheets using this function. Any other approach would invalidate all of these sheets so I have to stick with what I have.

    Ronny
    Ronny Richardson

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: XP Broke Grade Macro (XP)

    I have figured out that if you keep your security settings to medium, it works fine, it just asks you before loading the worksheet each time. It will not work with security settings set to high.

    Ronny
    Ronny Richardson

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XP Broke Grade Macro (XP)

    You will also need to have the SelfCert certificate recognised by Windows as a Trusted Certificate. Control Panel|Internet Options|Content|Certificates|Trusted Publishers|Import. HTH
    Gre

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: XP Broke Grade Macro (XP)

    I could follow all the steps but I could not make it work because I could not find the certificate file. The dialog box suggested *.PFX and *.P12 as extensions but I don't seem to have either of these types of files. The personal list shows the certificate but does not give any hints where it is located. Do you know?

    Ronny
    Ronny Richardson

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XP Broke Grade Macro (XP)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>Sorry, you will have to start by exporting it to a file from Control Panel|Internet Options|Content|Certificates|Personal|Export first. I agree that it is not the most inuitive of procedures.
    Gre

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: XP Broke Grade Macro (XP)

    Worked great. Thanks.


    Ronny
    Ronny Richardson

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: XP Broke Grade Macro (XP)

    This was working great until I opened the worksheet on a different machine at work. It opened fine but when I entered some grades and went to save the worksheet, it told me I did not have permission to save the file with the certificate. It saved the file but stripped off the certificate. Do you know what happened and how to avoid it again?

    Ronny
    Ronny Richardson

  15. #15
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XP Broke Grade Macro (XP)

    This error happened because you tried to alter code in a workbook that had a digital cert attached. Since your PC does not have the install certificate (such that you can apply a certificate on your own) it disabled the certificate. This occured because changing the code was a security violation and it disabled the cert as an indication of this violation (which is one of the reasons for applying a certificate in the first place). Just ignore it and continue. You can't get the cert reapplied unless you have the right pieces installed on your computer (as provided by the company that issued the cert).

    Deb

Page 1 of 2 12 LastLast

Posting Permissions

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