1. ## 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,

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------------------------------
Application.Volatile
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
End Function

2. ## 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)

It seemed to work just fine in my Excel 2002.

3. ## 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. ## 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

5. ## 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

6. ## 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. ## 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

8. ## 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

9. ## 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

10. ## 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

11. ## 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

12. ## 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.

13. ## Re: XP Broke Grade Macro (XP)

Worked great. Thanks.

Ronny

14. ## 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

15. ## 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 Last

#### Posting Permissions

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