[Request] - Convert Excel formula to VB6 Code

[Request] - Convert Excel formula to VB6 Code - Printable Version

+- Support Forums (https://www.supportforums.net)
+-- Forum: Categories (https://www.supportforums.net/forumdisplay.php?fid=87)
+--- Forum: Coding Support Forums (https://www.supportforums.net/forumdisplay.php?fid=18)
+---- Forum: Visual Basic and the .NET Framework (https://www.supportforums.net/forumdisplay.php?fid=19)
+---- Thread: [Request] - Convert Excel formula to VB6 Code (/showthread.php?tid=5830)

Pages: 1 2

[Request] - Convert Excel formula to VB6 Code - Carb0n F1ber - 04-08-2010

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

=========== 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 Smile

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

RE: [Request] - Convert Excel formula to VB6 Code - Carb0n F1ber - 04-15-2010

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

RE: [Request] - Convert Excel formula to VB6 Code - RaZoR03 - 04-15-2010

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

Thanks

RE: [Request] - Convert Excel formula to VB6 Code - Carb0n F1ber - 04-15-2010

(04-15-2010, 05:39 AM)RaZoR03 Wrote: 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 Thumbsup

RE: [Request] - Convert Excel formula to VB6 Code - RaZoR03 - 04-18-2010

Looks nice....Thanks

RE: [Request] - Convert Excel formula to VB6 Code - Carb0n F1ber - 04-18-2010

(04-18-2010, 12:26 AM)RaZoR03 Wrote: Looks nice....Thanks

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

RE: [Request] - Convert Excel formula to VB6 Code - Extasey - 04-18-2010

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

RE: [Request] - Convert Excel formula to VB6 Code - Carb0n F1ber - 04-18-2010

(04-18-2010, 04:18 AM)Extasey Wrote: 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 Big Grin

....

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 Smile

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. Smile

I'll wait to read your barcode code Big Grin

RE: [Request] - Convert Excel formula to VB6 Code - Extasey - 04-18-2010

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 Sad

)

RE: [Request] - Convert Excel formula to VB6 Code - Carb0n F1ber - 04-20-2010

(04-18-2010, 04:48 AM)Extasey Wrote: 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 Smile