Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 23rd, 2011, 08:38 AM
Registered User
 
Join Date: Feb 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Letters to Numbers decode?

Hi All,
I'm trying to convert a name (in 1 cell in excel spreadsheet) to its numerical representation. something like:

a=1
b=2
c=3
etc....

I'd like to be able to type a name in a cell (in spreadsheet) and then have the numerical representation in the cell next to the name and then a "sum" cell that sums up the numerical decode. something like:

Name Decode of name Sum
Mike --- 13, 9, 11, 5 --- 38

I appreciate your help!
Mike
 
Old February 23rd, 2011, 09:20 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

something like this would work. I put this in the module behind sheet1 of a blank workbook if you fiddle with it, just remember to make sure you turn off events like i did otherwise you get stuck in an endless loop. this will only do something if you type Mike into column A of the worksheet
Code:
Option Explicit

Dim stText As String
Dim iLength As Integer
Dim iCurrPosition As Integer
Dim stCurrChr As String
Dim lSum As Long
Dim stNums As String
Const m As Integer = 13
Const i As Integer = 9
Const k As Integer = 11
Const e As Integer = 5

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    stText = Target
    iLength = Len(stText)
    stNums = ""
    lSum = 0
    For iCurrPosition = 1 To iLength
        stCurrChr = Mid(stText, iCurrPosition, 1)
        Select Case stCurrChr
            Case "m", "M"
                stNums = stNums & m & ", "
                lSum = lSum + m
            Case "i", "I"
                stNums = stNums & i & ", "
                lSum = lSum + i
            Case "k", "K"
                stNums = stNums & k & ", "
                lSum = lSum + k
            Case "e", "E"
                stNums = stNums & e & ", "
                lSum = lSum + e
        End Select
    Next
    Target.Offset(0, 1) = stNums
    Target.Offset(0, 2) = lSum
    Application.EnableEvents = True
End If
End Sub
 
Old February 23rd, 2011, 09:26 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

put this in right after the line that says "Next" to chop off the last ", "
Code:
    iLength = Len(stNums)
    stNums = Left(stNums, iLength - 2)
 
Old February 23rd, 2011, 11:11 AM
Registered User
 
Join Date: Feb 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default letters to numbers decode?

Thx for your help!!! much appreciated!!!

How do you turn off the events?
 
Old February 23rd, 2011, 11:14 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

you're welcome :) it's already in the code:
Code:
Application.EnableEvents = False
 
Old February 23rd, 2011, 02:02 PM
Registered User
 
Join Date: Feb 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks again!!

What does the Application.EnableEvents for? Why isn't it defaulted to "False"?

One more quicky for you -- is there a way to create a variablized solution for the repetitive "case" logic? For instance, something like:

Psuedo code (hopefully to give you the idea of what i'm talking about). The idea being to only have 1 block of code and have it loop through the code based on the count of the "i" variable?

do i=1 to 26
Select Case stCurrChr
Case "&i", "&i"
stNums = stNums & i & ", "
lSum = lSum + i
End Select
end

Thanks again!
Mike
 
Old February 23rd, 2011, 02:13 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

events in general are triggers for code to run, in this case i used the event that gets raised when something on the worksheet changes. the thing is that in the code that gets run when the worksheet changes is code that enters stuff into the worksheet, thus triggering the Worksheet_Change event again. So Application.EnableEvents = False turns off the trigger that gets hit when something changes on the worksheet, until the code is done, then turns it back on at the bottom of that sub.
Not sure what you're after with your question about the looping code. I already have a loop in the code that goes thru each letter of whatever you type into column A. That's what the For... Next block is. Just need to fill out the rest of the letters and it will work fine.
 
Old February 23rd, 2011, 02:19 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

if what you're trying to do is get out of having to write a case block for each letter of the alphabet, i don't think so. Use copy and paste. you don't however, have to set a Const for each letter, you can just add the number directly in each Case block. I don't know why i did it like that, was thinking i was going to be using the numbers more frequently or something.
 
Old February 23rd, 2011, 03:22 PM
Registered User
 
Join Date: Feb 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, ready for this....it was working, but now it's not -- not sure if i disabled macros or something. Do you know if diabling macros will cause the code not to work? Are macros and vba script related?

When it did work, the first time i tried, i tried deleting a few blank records and then it jumped to the vba code where a "debug" box popped up. After that, it stopped working. i even closed out the workbook (w/o saving), came back in and it's still not working. Any ideas?

Can you recommend any other good resources for learning vba code? (ie. books, websites, etc.)

Thx much!
Mike
 
Old February 23rd, 2011, 03:34 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

Excel VBA 2002 is the one i learned VBA with. And yes, when you record a macro what it does is write code in a module in the VBA editor.
Did you change the code at all? If so, can you post the whole thing so i can look at it?





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to number with letters & numbers JoshC XSLT 4 February 25th, 2009 12:11 PM
Compare numbers and letters in same cell EricB123 Excel VBA 1 January 21st, 2007 03:30 PM
Remove letters from numbers Corey Access 7 December 18th, 2005 09:09 PM
fillin array with letters from a to Z and numbers sajid C# 10 May 3rd, 2005 03:38 PM
Taking both numbers and letters as input HateMe C# 1 May 13th, 2004 11:33 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.