Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 6th, 2013, 02:22 PM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Function to convert CUSIP code to ISIN code ... cusipconv()

Dear,

I have the following code (Function: cusipconv) to convert CUSIP code to ISIN. Some people used it before and reported satisfactory results. In my case the function turns out only ziros and no ISIN code. Can you kindly look into the function to see what is the problem. I provide here with CUSIP code for 5 companies that you can use to test the function.

CUSIP
00371F206
04543P100
00081T108
002564102
000957100

Many thanks.



Public Function CUSIPCONV(ByVal st As String) As Long

'Using ideas from original by Jelle-Jeroen Lamkamp 28 Apr 2008

Dim x As Integer, lTotal As Integer, stTemp As String, stNum As String

st = UCase(Trim(st))

If Len(st) <> 11 Or Mid(st, 1, 1) < "A" Or Mid(st, 1, 1) > "Z" Then Exit Function

stNum = ""

For x = 1 To 11
stTemp = Mid(st, x, 1)
If stTemp >= "0" And stTemp <= "9" Then
stNum = stNum & stTemp
ElseIf stTemp >= "A" And stTemp <= "Z" Then
stNum = stNum & CStr(Asc(stTemp) - 55)
Else
Exit Function
End If
Next x

stNum = StrReverse(stNum)

lTotal = 0

For x = 1 To Len(stNum)
lTotal = lTotal + CInt(Mid(stNum, x, 1))
If x Mod 2 = 1 Then
lTotal = lTotal + CInt(Mid(stNum, x, 1))
If CInt(Mid(stNum, x, 1)) > 4 Then
lTotal = lTotal - 9
End If
End If
Next x


CUSIPCONV = (10 - (lTotal Mod 10)) Mod 10

End Function
Reply With Quote
  #2 (permalink)  
Old March 28th, 2013, 03:37 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Please check the following code with the exact values. This is why the code exits:

Code:
 
If Len(st) <> 11 Or Mid(st, 1, 1) < "A" Or Mid(st, 1, 1) > "Z" Then Exit Function
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How To convert code aliusmankhan VB How-To 1 July 21st, 2008 01:56 PM
Convert to C# Code sanjaymannnet ASP.NET 1.0 and 1.1 Basics 1 September 16th, 2006 11:47 AM
Convert C++ Code to C# skin C# 2 May 30th, 2006 06:34 AM
Convert VB to C# code skdp VS.NET 2002/2003 2 March 1st, 2004 11:45 PM



All times are GMT -4. The time now is 05:13 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.