[Request] - Convert Excel formula to VB6 Code

[Carb0n F1ber]

Ok, so I posted this in HF, and did not even get 1 reply. So hoping to get some support here in SF

=========== Copy + Paste from HF ==============

Well, with the little knowledge I have in VBA, I made a small form that could validate a certain number through input in a text box. But being not that good in VBA, I used the excel sheet to do the formula, and the form pulling the results from the excel sheet. I then hid the sheet, and allowed the form to execute.

Now, I need to give out this small "program" to a couple of my clients, but obviously don't want to look like an idiot for sending it in an excel sheet. So I need to convert this to .exe with the help of VB6.

Well, I can move around a VB6 area pretty much smoothly, but I am not that much into coding. So if someone here could help me, or point me in the right direction, I am pretty much sure I can grasp what needs to be done.

Basically, it's just a single input, and 3 outputs (valid number or not, age, date of birth) based on the input.

The 3 output excel formulas are (where D11 is the input cell value),

Code:

=IF(D11="","Please Enter Number",IF(LEN(D11)=12,IF((11-MOD((LEFT(D11,1)*2)+(MID(D11,2,1)*1)+(MID(D11,3,1)*6)+(MID(D11,4,1)*3)+(MID(D11,5,1)*7)+(MID(D11,6,1)*9)+(MID(D11,7,1)*10)+(MID(D11,8,1)*5)+(MID(D11,9,1)*8)+(MID(D11,10,1)*4)+(MID(D11,11,1)*2),11))=VALUE(RIGHT(D11,1))=TRUE,D11&" is a Valid Civil ID", D11&" is an Invalid Civil ID Number"),"Please check number of Digits"))

Code:

=IF(AND(LEN(D11)=12,LEFT(D11,1)="2",(11-MOD((LEFT(D11,1)*2)+(MID(D11,2,1)*1)+(MID(D11,3,1)*6)+(MID(D11,4,1)*3)+(MID(D11,5,1)*7)+(MID(D11,6,1)*9)+(MID(D11,7,1)*10)+(MID(D11,8,1)*5)+(MID(D11,9,1)*8)+(MID(D11,10,1)*4)+(MID(D11,11,1)*2),11)=VALUE(RIGHT(D11,1))=TRUE)),"Date of Birth : "&TEXT(MID(D11,6,1)&MID(D11,7,1)&"/"&MID(D11,4,1)&MID(D11,5,1)&"/"&"19"&MID(D11,2,1)&MID(D11,3,1),"dd/mm/yyyy"),IF(AND(LEN(D11)=12,LEFT(D11,1)="3",(11-MOD((LEFT(D11,1)*2)+(MID(D11,2,1)*1)+(MID(D11,3,1)*6)+(MID(D11,4,1)*3)+(MID(D11,5,1)*7)+(MID(D11,6,1)*9)+(MID(D11,7,1)*10)+(MID(D11,8,1)*5)+(MID(D11,9,1)*8)+(MID(D11,10,1)*4)+(MID(D11,11,1)*2),11)=VALUE(RIGHT(D11,1))=TRUE)),"Date of Birth : "&TEXT(MID(D11,6,1)&MID(D11,7,1)&"/"&MID(D11,4,1)&MID(D11,5,1)&"/"&"20"&MID(D11,2,1)&MID(D11,3,1),"dd/mm/yyyy"),""))

Code:

=IF(AND(LEN(D11)=12,LEFT(D11,1)="2",(11-MOD((LEFT(D11,1)*2)+(MID(D11,2,1)*1)+(MID(D11,3,1)*6)+(MID(D11,4,1)*3)+(MID(D11,5,1)*7)+(MID(D11,6,1)*9)+(MID(D11,7,1)*10)+(MID(D11,8,1)*5)+(MID(D11,9,1)*8)+(MID(D11,10,1)*4)+(MID(D11,11,1)*2),11)=VALUE(RIGHT(D11,1))=TRUE)),"Age : "&YEAR(NOW())-YEAR(DATE("19"&MID(D11,2,1)&MID(D11,3,1),MID(D11,4,1)&MID(D11,5,1),MID(D11,6,1)&MID(D11,7,1))),IF(AND(LEN(D11)=12,LEFT(D11,1)="3",(11-MOD((LEFT(D11,1)*2)+(MID(D11,2,1)*1)+(MID(D11,3,1)*6)+(MID(D11,4,1)*3)+(MID(D11,5,1)*7)+(MID(D11,6,1)*9)+(MID(D11,7,1)*10)+(MID(D11,8,1)*5)+(MID(D11,9,1)*8)+(MID(D11,10,1)*4)+(MID(D11,11,1)*2),11)=VALUE(RIGHT(D11,1))=TRUE)),"Age : "&YEAR(NOW())-YEAR(DATE("20"&MID(D11,2,1)&MID(D11,3,1),MID(D11,4,1)&MID(D11,5,1),MID(D11,6,1)&MID(D11,7,1))),""))

Please help me convert the above 3 formulas to show output on a VB6 form, where D11 will be a text box.

Here is the form that I use through Excel, it will help give a better idea



It would be great if you guys could atleast point me to the right tutorial for this kind of work. Thanks in advance :biggrin:

[Carb0n F1ber]

Bump, someone ??????..... I really need to kick start this starting somewhere..................

[RaZoR03]

Thanks so much,i was looking how to do this for ever.

Thanks

[Carb0n F1ber]

Thanks so much,i was looking how to do this for ever.

Thanks

Lolzzzz someone gained some knowledge from something that I was "seeking" knowledge for

[RaZoR03]

Looks nice....Thanks

[Carb0n F1ber]

Looks nice....Thanks

Why do I have a feeling that you are just trying to increase post count

[Extasey]

I have two suggestions:
1. Try and embed the excel sheet and continue normal functioning of the application
2. Split the if statements up for me an i'll lend you a hand.
Because of the format, I have no idea whats going on in 2 and especially 3. I may have an example at school that was a bar-code validation program I could send you if your able to learn from source code (some are, most aren't).

[Carb0n F1ber]

I have two suggestions:
1. Try and embed the excel sheet and continue normal functioning of the application
2. Split the if statements up for me an i'll lend you a hand.
Because of the format, I have no idea whats going on in 2 and especially 3. I may have an example at school that was a bar-code validation program I could send you if your able to learn from source code (some are, most aren't).

Thanks so much for your reply ....

1. I cannot embed the excel sheet as you suggest, because the sheet's properties are set to be invisible and its only the form that loads upon opening the workbook

3. I can learn great from source codes, I think I might get lucky if I see how the barcode validation is done .. Looks like exactly what I want

2.

Code:

11-MOD((LEFT(D11,1)*2)+(MID(D11,2,1)*1)+(MID(D11,3,1)*6)+(MID(D11,4,1)*3)+(MID(D11,​5,1)*7)+(MID(D11,6,1)*9)+(MID(D11,7,1)*10)+(MID(D11,8,1)*5)+(MID(D11,9,1)*8)+(MI​D(D11,10,1)*4)+(MID(D11,11,1)*2),11))=VALUE(RIGHT(D11,1)

The above would be the main code. The remaining codes are just validating the inputs, ex. should be 12 digits.

The second and third codes are just displaying the age and date of birth if the above code is "True". Date of Birth is pulled out from the number itself, with validation to check if less than year 2000 or above, being the first digit 2 or 3

Hope this helps.

I'll wait to read your barcode code

[Extasey]

If I get some time, I'll try to throw some code together for you, but I return to school tomorrow so I'll see if its still on the servers and if it will be of any help (hopefully the admin hasn't done a purge again... it will have been deleted if he did )

[Carb0n F1ber]

If I get some time, I'll try to throw some code together for you, but I return to school tomorrow so I'll see if its still on the servers and if it will be of any help (hopefully the admin hasn't done a purge again... it will have been deleted if he did )

Thanks mate... Did you get the time to check on the barcode code ?? I'm still waitin for ya