PDA

View Full Version : Excel97 VBA - 16 digit numbers



parry
06-08-2002, 05:45 PM
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

Graham L
06-08-2002, 05:49 PM
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?

wuppo
06-08-2002, 05:51 PM
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?

parry
06-08-2002, 07:09 PM
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

Graham L
06-08-2002, 07:22 PM
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.;-)

-=JM=-
06-08-2002, 07:38 PM
So what would you consider to be a real programming languages Graham?

parry
06-08-2002, 07:41 PM
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

Graham L
06-08-2002, 07:42 PM
Burroughs Extended Algol (the best ), Pascal, Algol 60, Modula 2, ... Even PDP11 Macro. ]:)

-=JM=-
06-08-2002, 07:47 PM
I'm learning Turbo Pascal at the moment. I don't like it all that much so far.

Graham L
06-08-2002, 08:00 PM
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.

parry
06-08-2002, 08:31 PM
Thanks for the links. Good to know I'm calculating things correctly.

Russell D
07-08-2002, 12:06 PM
A Custom Cell Format of ####-####-####-#### allows 16 digit entry and display.

parry
07-08-2002, 12:46 PM
Thanks Russell. Im a bit crook today but when I get back to work I will check it out. I have a feeling that regardless of what data type you use when dealing with the data in VBA, the format of the destination cell takes over.

My theory is that if you have a number formatted as text in VBA, when it is placed in a destination cell that has a general/number format, the destination cell format will take precedence. So, even though I changed format to text within VBA, as soon as I populated the value into the cell the format changed back to a number.

I suppose its much like a paste-special command where you choose only values. It all depends upon the destination cell format.

I will test the theory tomorrow.

thanks all for your replies.
Parry

Russell D
07-08-2002, 01:49 PM
Sorry to hear you're crook.

I don't know how you are passing the userform textbox entry to a cell, but in an example I have tried, if you use a commandbutton on the form to confirm the textbox entry, using following routine will work as per my previous post.

Private Sub CommandButton1_Click()
Range("Z1").NumberFormat = "####-####-####-####"
Range("Z1").Value = TextBox1.Value
End Sub

parry
07-08-2002, 05:20 PM
Thanks Russell, your too kind. Youve confirmed my theory about having to format the destination cell first before passing through the value to the cell.

I was just going to format the whole sheet as text, but your code is better because if I ever export the code to another workbook I'm gonna fall into the same trap with the formatting (although I dont think I will ever forget this :-)). By always formatting the cell first within the code I'm not going to have the >15 digit problem.

I just need plain text so will just use...
Selection.NumberFormat = "@"
ActiveCell.Value = CStr(TextBox1.Text)

I wonder why old Billy made the max 15 digits - just my luck when I needed 16! :-) Although, with all his cash I would be surprised if this number was big enough for him to balance his chequebook. Look for an increase in this number in the next release of Office ;-)

Thanks a bunch
Parry

Graham L
07-08-2002, 05:42 PM
Unusually, it's not Bill's fault. That happens to be a standard precision. It's far too big for 32 bit integers (only +/- 2 Gig) and is part of the IEEE numeric standard which came out in the 80s. 15 digits is about what you can sensibly represent in 80 bits.

It's always messy moving between character and numeric representations. You are often "lucky" if you get a warning when youi have just lost all validity in both representations. :-(