Letters to Numbers decode?

mike1000008 · February 23rd, 2011, 08:38 AM

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

mtranchi · February 23rd, 2011, 09:20 AM

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

mtranchi · February 23rd, 2011, 09:26 AM

put this in right after the line that says "Next" to chop off the last ", "

Code:

    iLength = Len(stNums)
    stNums = Left(stNums, iLength - 2)

mike1000008 · February 23rd, 2011, 11:11 AM

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

How do you turn off the events?

mtranchi · February 23rd, 2011, 11:14 AM

you're welcome :) it's already in the code:

Code:

Application.EnableEvents = False

mike1000008 · February 23rd, 2011, 02:02 PM

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

mtranchi · February 23rd, 2011, 02:13 PM

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.

mtranchi · February 23rd, 2011, 02:19 PM

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.

mike1000008 · February 23rd, 2011, 03:22 PM

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

mtranchi · February 23rd, 2011, 03:34 PM

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?