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, 03:44 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

i think what happened to you is that the
Code:
Application.EnableEvents = True
line was never reached, so it's not responding to the worksheet_changed event. i rewrote the code a little to handle a couple of exceptions that will be raised:
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
Sub enabel()
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    stText = Target
    iLength = Len(stText)
    If iLength > 0 Then 'here's the start of the new if block
        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
        iLength = Len(stNums)
        If iLength > 2 Then 'here's another if block start
            stNums = Left(stNums, iLength - 2)
            Target.Offset(0, 1) = stNums
            Target.Offset(0, 2) = lSum
        End If 'and the end of this if block
        
    End If 'heres the end of the new if block
    Application.EnableEvents = True
End If
End Sub
 
Old February 23rd, 2011, 03:49 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

put the following code
Code:
    Else
        Target.Offset(0, 1).Clear
        Target.Offset(0, 2).Clear
right above the line
Code:
    End If 'heres the end of the new if block
and it will delete the values to the left of the a column automatically when you delete a value in the A column.
 
Old February 23rd, 2011, 03:53 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

one last thing. You'll notice below the declarations there's a new sub called "enabel". if you have any problems with the code not responding, place your cursor in that sub and then press play on the toolbar. This will turn event handling back on.
 
Old February 23rd, 2011, 03:54 PM
Registered User
 
Join Date: Feb 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thx! how similar is vba code to vb code in visual basic? if you know one, do you know the other?

ok, here's the 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 a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4
Const e As Integer = 5
Const f As Integer = 6
Const g As Integer = 7
Const h As Integer = 8
Const i As Integer = 9
Const j As Integer = 10
Const k As Integer = 11
Const l As Integer = 12
Const m As Integer = 13
Const n As Integer = 14
Const o As Integer = 15
Const p As Integer = 16
Const q As Integer = 17
Const r As Integer = 18
Const s As Integer = 19
Const t As Integer = 20
Const u As Integer = 21
Const v As Integer = 22
Const w As Integer = 23
Const x As Integer = 24
Const y As Integer = 25
Const z As Integer = 26

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 "a", "A"
stNums = stNums & a & ", "
lSum = lSum + a
Case "b", "B"
stNums = stNums & b & ", "
lSum = lSum + b
Case "c", "C"
stNums = stNums & c & ", "
lSum = lSum + c
Case "d", "D"
stNums = stNums & d & ", "
lSum = lSum + d
Case "e", "E"
stNums = stNums & e & ", "
lSum = lSum + e
Case "f", "F"
stNums = stNums & f & ", "
lSum = lSum + f
Case "g", "G"
stNums = stNums & g & ", "
lSum = lSum + g
Case "h", "H"
stNums = stNums & h & ", "
lSum = lSum + h
Case "i", "I"
stNums = stNums & i & ", "
lSum = lSum + i
Case "j", "J"
stNums = stNums & j & ", "
lSum = lSum + j
Case "k", "K"
stNums = stNums & k & ", "
lSum = lSum + k
Case "l", "L"
stNums = stNums & l & ", "
lSum = lSum + l
Case "m", "M"
stNums = stNums & m & ", "
lSum = lSum + m
Case "n", "N"
stNums = stNums & n & ", "
lSum = lSum + n
Case "o", "O"
stNums = stNums & o & ", "
lSum = lSum + o
Case "p", "P"
stNums = stNums & p & ", "
lSum = lSum + p
Case "q", "Q"
stNums = stNums & q & ", "
lSum = lSum + q
Case "r", "R"
stNums = stNums & r & ", "
lSum = lSum + r
Case "s", "S"
stNums = stNums & s & ", "
lSum = lSum + s
Case "t", "T"
stNums = stNums & t & ", "
lSum = lSum + t
Case "u", "U"
stNums = stNums & u & ", "
lSum = lSum + u
Case "v", "V"
stNums = stNums & v & ", "
lSum = lSum + v
Case "w", "W"
stNums = stNums & w & ", "
lSum = lSum + w
Case "x", "X"
stNums = stNums & x & ", "
lSum = lSum + x
Case "y", "Y"
stNums = stNums & y & ", "
lSum = lSum + y
Case "z", "Z"
stNums = stNums & z & ", "
lSum = lSum + z
End Select
Next
iLength = Len(stNums)
stNums = Left(stNums, iLength - 2)
Target.Offset(0, 1) = stNums
Target.Offset(0, 2) = lSum
Application.EnableEvents = True
End If
End Sub
 
Old February 23rd, 2011, 04:00 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

Visual Basic has been replaced by VB.Net and no, knowing VBA doesn't mean you can easily write code in VB.Net If you're going to learn programming, I'd recommend C#, it's not as wordy as VB.Net so the code's easier to read. Also, in the code you copied into your workbook, use indentation to make your code easier to read, i.e. indent if blocks, select statements, loops, etc. You can copy the newest code that i put up and then copy your extra case blocks into it if you wish, or you can place the three new if statements i have into your code, just make sure you place them EXACTLY where i have them.
 
Old February 24th, 2011, 12:30 PM
Registered User
 
Join Date: Feb 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is awesome! I really appreciate it!!

How's the pay for C# programmers? Any good C# books, websites you can recommend?

Thx again!
Mike
 
Old February 25th, 2011, 01:39 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

you're quite welcome mike, glad i could be of help. as far as good books/websites, i started with learnvisualstudio.net, which a fee-based site and gave me a decent start with some of the concepts i wasn't familiar with having been only familiar with VBA. (Moving from VBA to C# is like stepping out of the stone-age into a Best-Buy. But VBA is the only language that comes with excel, so i still use it whenever working with spreadsheets) The site covers VB.Net, C#, ASP.Net, gives you the basics of SQL and also covers other stuff i haven't yet checked out. It's a video based tutorial system that focuses on the beginner and for me it was a great start. But if you can't afford his site, try doing a search on "free e-books c# 2010." Wrox has a book that i refer to as well: "Beginning_Visual_CSharp_2010" (there's a forum for that book on this site as well). I've found that sometimes i need to read about a topic from more than one source to wrap my head around it fully.

Once you get a toe-hold on the language, then just do a specific search on what you're trying to accomplish, like "call a method in an MDI child window c# 2010", not "work with MDI forms." For the more general, you'll prolly need to hit the books.

what you'll find is that it may not be so much about the site as the person who answered a particular question, so you might want to hunt around a little for an easier solution. i once was looking to get at the individual pixels in a .jpg file, and came across this one post in which the author gave this convoluted, cryptic, like 75 lines of code, example on how to do it. Then at the bottom someone commented: "Are you kidding me? It's bmp.getPixel() and bmp.setPixel()." (Glad i read to the bottom of that page :) ) It can be the same with books too, even from the same publisher. I have like 8 or 10 Wrox books on various topics, some are duds, others fantastic. Overall I do gravitate towards Wrox books though, and shy away from the dummies series as about 40% of their words are used for really bad jokes. i could chop any dummies book down to a pamphlet without any loss of topic.

You can download Visual Studio Express from microsoft for free, and although it's a pared down version, it gives you the ability to do just about anything you want as far as making software. Be aware though, that if you have an older computer, you're looking at like a 5 hour download/install process and that it's a beast on older computers.

as far as pay, i'm an amateur programmer (as of now anyway), so no idea. in fact, don't take my word on recommending c#. there's tons of languages out there, check out this link to get you started on which you might want to learn

http://www.eweek.com/c/a/IT-Manageme...arn-Right-Now/

i've tried java, javascript, python, vb.net. i chose c# because i was having difficulty finding decent documentation with the other languages. maybe i just didn't look in the right places, but it seems to me a problem with open source software is there is no cohesive, professional system to teach new programmers how to use the software. after getting my start with learnvisualstudio.net, i have yet to type in search terms for what i want to accomplish in c# and not find the question already answered. plus, the IDE rocks.

if you have other questions not specific to VBA, prolly a good idea not to clog up wrox's site, so email me directly mtranchi {at} h o t m a i l . c o m (trying to trick the email hunting bots :) )





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.