| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 23049 | 2002-08-06 05:45:00 | Excel97 VBA - 16 digit numbers | parry (27) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 68632 | 2002-08-06 05:45:00 | Hi, I am having trouble with Excel recognising 16 digit numbers correctly . I have a textbox in a form that accepts data and then places the data in a sheet . The textbox . text value requires a 16 digit number to be entered . When this value is placed in the sheet by the VBA code, Excel changes the 16th digit to zero, regardless of what was actually entered into the textbox . I do not believe this is incorrect code as I have typed a 16 digit value in a Text file then pasted in Excel and the same thing happens . I have looked in Tools-Options-Calculations and see there is a checkbox called "Precision as displayed" . The tooltip to this says it calculates to full precision (15 digits) but unchecking or checking this makes no difference to the data in the sheet . Any ideas? I have tried formatting as a string,double, etc before placing in the sheet, using left/right functions to break number down below 16 then putting them together again but all to no effect . All formatting types, even using the custom format and # as the placeholder didnt work . The field needs to be 16 digits as it's the length of a credit card number . The data in Excel will be written to a text file, so I suppose I could try breaking the number into two cells then adding them when writing to the text file but not sure if that will even work yet . Does anybody know how to get around this problem in Excel or is it a limitation that cant be avoided? cheers Parry |
parry (27) | ||
| 68633 | 2002-08-06 05:49:00 | I would guess that you are getting the 15 digit precision. ;-) Since you won't be doing arithmetic (I hope) on CC numbers, why not keep it as a text string? |
Graham L (2) | ||
| 68634 | 2002-08-06 05:51:00 | If you are transferring to a text file, why not format the entry area as 'text' then you can enter as many digits as you like? | wuppo (41) | ||
| 68635 | 2002-08-06 07:09:00 | Thanks Graham, unfortunately I do need to do calculations on the 16 digit number believe it or not! :-) Needs a check digit validation to prove it's a legitimate card. However, after the calculation I try and turn this into a string by using cstr.... with activecell .offset(0,1).value = cstr(txtCardNumber.text) Think Wuppo might be onto something though in having the destination cell formatted as text BEFORE entering data. Trouble was I only formatted to text afterwards, and no doubt Excel had already shat on it by then so reformatting was useless. After trying many things it was time to leave work anyway, just long enough to post a message here and storm off muttering to myself, he he :-) Thanks for the prompt reply chaps, will check this out tomorrow with a fresh brain. cheers Parry |
parry (27) | ||
| 68636 | 2002-08-06 07:22:00 | That should be simple. Get the 16 digits as text. Pick off the check digit, (as a string, convert it to numeric), extract and convert the 15 other digits to numeric, calculate the check value, then compare. When I did a lot of data handling, I kept everything as text, and converted only when necessary. That way I never got unexpected over/underflows, truncation errors etc. But I wasn't limited to VBA; I used real programming languages.;-) | Graham L (2) | ||
| 68637 | 2002-08-06 07:38:00 | So what would you consider to be a real programming languages Graham? | -=JM=- (16) | ||
| 68638 | 2002-08-06 07:41:00 | Ouch! Thanks Graham, unfortunately my employers want Excel so thats what they get. ;-) I'm basically using Excel as a database - wrong tool for the job. A bit like using a saw to knock in a nail! No apparent input mask or anything that would be available in a proper database so the validation routines are as long as state highway #1. Im only a journeyman as well - not a real programmer :-) Will take your advice and try and turn the textbox values into text first then convert to numbers then back to text as needed. Thanks again, Parry |
parry (27) | ||
| 68639 | 2002-08-06 07:42:00 | Burroughs Extended Algol (the best ), Pascal, Algol 60, Modula 2, ... Even PDP11 Macro. ]:) | Graham L (2) | ||
| 68640 | 2002-08-06 07:47:00 | I'm learning Turbo Pascal at the moment. I don't like it all that much so far. | -=JM=- (16) | ||
| 68641 | 2002-08-06 08:00:00 | I knew I'd seen something about this ... google, given "credit card check digit" gives some good sources. The first one gives further links .. Excel and VB code. The ?third (www.cs.queensu.ca/home/bradbury...) also looks helpful. | Graham L (2) | ||
| 1 2 | |||||