Basic Stuff.
Hi ...
I am, after several years of half-hearted attempts, making a serious effort to learn Excel VBA. Why is it so hard to do just simple stuff?? I just get so frustrated. The help files are of little value. I almost never find what I'm looking for, and when I do I am seldom intelligent enough to adapt the example to my own code. I was an amateur programmed back in the days of DOS QuickBasic, and a pretty good one, even if I say so myself. But I just cannot catch on to Excel VBA. I have a book, an old one. "Using Visual Basic For Applications Excel Edition." Written by Jeff Webb, published 1994. Outdated of course, but I couldn't make sense of this book when I bought it back in 1994. Why is it so hard to do simple, basic stuff?? Tonight I've been working on a very easy problem, but, after an hour, I still have no clue. All I want to do is assign the data in a text cell to a variable, change it (split it into two names) then put it back in the cell and the adjacent cell. It should take 30 seconds to write the code, but I can't figure out how to even get the line assigned to the variable. There are so many capricious, esoteric ways of addressing a certain part of the worksheet. When do you use cell?? When do you use cells?? How can you make a variable refer to a range?? What is a Collection?? I'm sure that there is logic and sense to the way Excel VBA is constructed, but I just can't see it. To me it seems completely random and arbitrary. I understand the theory, I think. I understand what methods are and properties, but there seems to be no concrete advice on how to use them in actual practice I just opened my Using VBA book at random. Here's what I see: range.Borders([Index]) Returns one or all borders in a range. If a range does not have a border, the returned border object has Linestyle xlNone. You guys no doubt know what he's talking about, but to me it might as well be written in Swahili. Why can't there just be a basic reference that tells a programmer the basic info he needs to know?? How to assign a cell to a variable. How to write a variable to a cell. How to use variables to refernce cells. (I don't believe I have ever - I mean ever - seen an example that does not use this structu Range("C12:N12") or else R1C1 notation.) Instead we have page after page of methods with no practical example of how to use them. I don't care about linestyle or windowdisplayhorizontalscrollbars. I just want to make simple applications and not fight the nonsense. The language should be working with the programmer, not against him. Thanks for letting me vent. Sam A man who had lately declared That property ought to be shared, Thought it going too far When they called for his car, And a list of exceptions prepared. Thomas Thorneley, From The Penguin Book Of Limericks |
Basic Stuff.
Hi Sam,
I feel for you. Learning any language, whether it is a computer language or a foreign language, is difficult. At first, learning comes slowly and then with time it improves. The trick is to not get defeated. << How to assign a cell to a variable. dVar1 = Sheets("Sheet1").Range("A1").Value << How to use variables to refernce cells Not sure, did you mean the opposite of the first example? Sheets("Sheet1").Range("A2").Value = dVar1 << (I don't believe I have ever - I mean ever - seen an example that does not use this structu Range("C12:N12") or else R1C1 notation.) Range will have to include some argument. You could have a variable as an argument. sImportantRange="B1:D1" Sheets("Sheet1").Range(sImportantRange).value=dVar 1 Here is a simple little subroutine to demonstrate the above. Sub Test1() Dim dVar1 As Double Dim sImportantRange as String '\ Silly example to demonstrate some basic concepts '\ "A1" on Sheet1 has the numerical value 5 sImportantRange = "B1:D1" dVar1 = Sheets("Sheet1").Range("A1").Value Sheets("Sheet1").Range("A2").Value = dVar1 Sheets("Sheet1").Range(sImportantRange).Value = dVar1 End Sub Check out these 3 books. The crowd here generally favors the first book as the best. I found the first two books to be very helpful, although the second book is more advanced. That said, I found that it presented some material that was covered in the first book in a more straightforward manner. But that is just one person's opinion. If you are like me, having more than one source is helpful. I think you will really like John's book (1st one) because he uses lots and lots of examples. If you work through them, then by osmosis, it will begin to make sense. Again, the trick is not giving up. Excel 2003 Power Programming with VBA ; John Walkenbach; John Wiley & Sons; ISBN: 0-764540726 Excel 2002 VBA Programmers Reference; John Green, Stephen Bullen, Rob Bovey, Robert Rosenberg; 993 pages; Wrox Press Inc; ISBN 1861005709 Excel 2000 Programming For Dummies; John Walkenbach; 408 pages; Hungry Minds, Inc; ISBN: 0764505661 I hope this message is of some benefit. Good luck, and come back and let us know where you are having difficulty. Regards, Kevin |
Basic Stuff.
Kevin Stecyk wrote:
(Snip) Here is a simple little subroutine to demonstrate the above. Sub Test1() Dim dVar1 As Double Dim sImportantRange as String '\ Silly example to demonstrate some basic concepts '\ "A1" on Sheet1 has the numerical value 5 sImportantRange = "B1:D1" dVar1 = Sheets("Sheet1").Range("A1").Value Sheets("Sheet1").Range("A2").Value = dVar1 Sheets("Sheet1").Range(sImportantRange).Value = dVar1 End Sub Good luck, and come back and let us know where you are having difficulty. Lack of totally explicit instructions on how to implement the above example in my Excel 2002. Brian Tozer |
Basic Stuff.
Hi Brian,
It's good to see you again. I had assumed (always dangerous) that if a person was asking about programming questions, he or she has at least gotten to the VBE editor. But maybe that was too big of a leap? I am using XL 2000, but I think the instructions will apply to your version as well. Here's what you do: 1) Start Excel 2) Go to Sheet1 3) Go to Cell A1 and insert numerical 5 4) Alt-F11 (or Tools | Macro | Visual Basic Editor....takes you to the Visual Basic Editor) 5) Insert Module 6) Copy and paste code (I will provide code again at the end). 7) Alt-Q (or File | Return and Close to Microsoft Excel) 8) Alt-F8 (or Tools | Macro | Macros) 9) You can choose to Step Into or Run at this point 10) If you choose to Step Into....you will be taken back to VBE. In VBE you can press F8 or use the toolbar to execute each line of code. 10 A) As you move through each line of code, have a look the spreadsheet to see the changes. You are done. This is a very, very simple subroutine. It merely demonstrates.... a) Assigning a value from a cell to a visual basic variable b) Assigning a value from a visual basic variable to a cell c) Assiging a string "B1:D1" to a variable which was used to define a range. You should look up in XL's help on Dim (dimension) variables. You should note that I would have been better to use Dim Var1 as a variant rather than as a double. That way, any value could be placed in A1. However, I used double so that most numerical values could be placed in A1. Also, the '\ are simply comments. VBA requires only the single apostrophe to designate a comment. Whatever follows the single apostrophe is considered a comment. However, I use '\ because it is easier to see it. It stands out more. The backslash is merely part of the comment. Sub Test1() Dim dVar1 As Double Dim sImportantRange As String '\ Silly example to demonstrate some basic concepts '\ "A1" on Sheet1 has the numerical value 5 sImportantRange = "B1:D1" dVar1 = Sheets("Sheet1").Range("A1").Value Sheets("Sheet1").Range("A2").Value = dVar1 Sheets("Sheet1").Range(sImportantRange).Value = dVar1 End Sub I hope that helps. Regards, Kevin "Brian Tozer" wrote in message: Lack of totally explicit instructions on how to implement the above example in my Excel 2002. |
Basic Stuff.
"Kevin Stecyk" wrote in
: Hi Brian, It's good to see you again. I had assumed (always dangerous) that if a person was asking about programming questions, he or she has at least gotten to the VBE editor. It all seems to be there in the help under "Automating tasks" for using the VBA editor and a few examples to kick off under "Programming concepts" which is the second topic under "Programming information" after "What's New." I'd be very surprised that someone reading this group wouldn't have ventured into the VB editor ever. I find most people usually get started by recording a macro of performing an action and then inspecting what happens. Granted the code isn't great, but it'll get you started. This continues to be useful even after you progress beyond being a beginner as you try to identify what the particular obscure feature you need is called. Paul |
Basic Stuff.
Thanks so much for your trouble Kevin in supplying this superb and
comprehensive answer. I will play with this tomorrow and get back to you if I have any query. A stroke has made learning a difficult process so I really appreciate your effort. Brian Tozer Kevin Stecyk wrote: Hi Brian, It's good to see you again. I had assumed (always dangerous) that if a person was asking about programming questions, he or she has at least gotten to the VBE editor. But maybe that was too big of a leap? I am using XL 2000, but I think the instructions will apply to your version as well. Here's what you do: 1) Start Excel 2) Go to Sheet1 3) Go to Cell A1 and insert numerical 5 4) Alt-F11 (or Tools | Macro | Visual Basic Editor....takes you to the Visual Basic Editor) 5) Insert Module 6) Copy and paste code (I will provide code again at the end). 7) Alt-Q (or File | Return and Close to Microsoft Excel) 8) Alt-F8 (or Tools | Macro | Macros) 9) You can choose to Step Into or Run at this point 10) If you choose to Step Into....you will be taken back to VBE. In VBE you can press F8 or use the toolbar to execute each line of code. 10 A) As you move through each line of code, have a look the spreadsheet to see the changes. You are done. This is a very, very simple subroutine. It merely demonstrates.... a) Assigning a value from a cell to a visual basic variable b) Assigning a value from a visual basic variable to a cell c) Assiging a string "B1:D1" to a variable which was used to define a range. You should look up in XL's help on Dim (dimension) variables. You should note that I would have been better to use Dim Var1 as a variant rather than as a double. That way, any value could be placed in A1. However, I used double so that most numerical values could be placed in A1. Also, the '\ are simply comments. VBA requires only the single apostrophe to designate a comment. Whatever follows the single apostrophe is considered a comment. However, I use '\ because it is easier to see it. It stands out more. The backslash is merely part of the comment. Sub Test1() Dim dVar1 As Double Dim sImportantRange As String '\ Silly example to demonstrate some basic concepts '\ "A1" on Sheet1 has the numerical value 5 sImportantRange = "B1:D1" dVar1 = Sheets("Sheet1").Range("A1").Value Sheets("Sheet1").Range("A2").Value = dVar1 Sheets("Sheet1").Range(sImportantRange).Value = dVar1 End Sub I hope that helps. Regards, Kevin "Brian Tozer" wrote in message: Lack of totally explicit instructions on how to implement the above example in my Excel 2002. |
Basic Stuff.
Thanks Paul for your reply, but none of the terms that you quote apear in
the index of the Help for my Excel 2002 or its Help for the VBA Editor, so I am at a loss to know what you are referring to. Brian Tozer Paul wrote: It all seems to be there in the help under "Automating tasks" for using the VBA editor and a few examples to kick off under "Programming concepts" which is the second topic under "Programming information" after "What's New." I'd be very surprised that someone reading this group wouldn't have ventured into the VB editor ever. I find most people usually get started by recording a macro of performing an action and then inspecting what happens. Granted the code isn't great, but it'll get you started. This continues to be useful even after you progress beyond being a beginner as you try to identify what the particular obscure feature you need is called. Paul |
Basic Stuff.
Hi Kevin.
Just reporting total success, and I understood everything fine. In the last few days I have gone thru 3 printer ink cartridges printing hundreds of pages of info but have not come across all that you have just explained. Thanks so much again. Brian Tozer Kevin Stecyk wrote: Hi Brian, It's good to see you again. I had assumed (always dangerous) that if a person was asking about programming questions, he or she has at least gotten to the VBE editor. But maybe that was too big of a leap? I am using XL 2000, but I think the instructions will apply to your version as well. Here's what you do: 1) Start Excel 2) Go to Sheet1 3) Go to Cell A1 and insert numerical 5 4) Alt-F11 (or Tools | Macro | Visual Basic Editor....takes you to the Visual Basic Editor) 5) Insert Module 6) Copy and paste code (I will provide code again at the end). 7) Alt-Q (or File | Return and Close to Microsoft Excel) 8) Alt-F8 (or Tools | Macro | Macros) 9) You can choose to Step Into or Run at this point 10) If you choose to Step Into....you will be taken back to VBE. In VBE you can press F8 or use the toolbar to execute each line of code. 10 A) As you move through each line of code, have a look the spreadsheet to see the changes. You are done. This is a very, very simple subroutine. It merely demonstrates.... a) Assigning a value from a cell to a visual basic variable b) Assigning a value from a visual basic variable to a cell c) Assiging a string "B1:D1" to a variable which was used to define a range. You should look up in XL's help on Dim (dimension) variables. You should note that I would have been better to use Dim Var1 as a variant rather than as a double. That way, any value could be placed in A1. However, I used double so that most numerical values could be placed in A1. Also, the '\ are simply comments. VBA requires only the single apostrophe to designate a comment. Whatever follows the single apostrophe is considered a comment. However, I use '\ because it is easier to see it. It stands out more. The backslash is merely part of the comment. Sub Test1() Dim dVar1 As Double Dim sImportantRange As String '\ Silly example to demonstrate some basic concepts '\ "A1" on Sheet1 has the numerical value 5 sImportantRange = "B1:D1" dVar1 = Sheets("Sheet1").Range("A1").Value Sheets("Sheet1").Range("A2").Value = dVar1 Sheets("Sheet1").Range(sImportantRange).Value = dVar1 End Sub I hope that helps. Regards, Kevin "Brian Tozer" wrote in message: Lack of totally explicit instructions on how to implement the above example in my Excel 2002. |
Basic Stuff.
"Brian Tozer" wrote in
: Thanks Paul for your reply, but none of the terms that you quote apear in the index of the Help for my Excel 2002 or its Help for the VBA Editor, so I am at a loss to know what you are referring to. Brian Tozer They are chapters in the contents page. I strongly suggest you summon the Help window from Excel itself and not from the VBA editor, from Excel 2000 onwards calling it up from the VBA editor only gives you the help files for the VBA language and nothing else. I am at a loss to explain why MS deemed this a good idea, in Excel 97 asking for help in either place got you the same help file, I think it is some sort of cross-office-products help integration gone wrong. If you are trying to figure out what's going on without reading through the contents I can see how you'd have problems. It would be rather like trying to learn a foreign language by opening a dictionary and picking random words out of it. Personally I turn off the help assistant, so I go straight to the help contents when I press F1. Paul |
Basic Stuff.
"Brian Tozer" wrote in
: Thanks Paul for your reply, but none of the terms that you quote apear in the index of the Help for my Excel 2002 or its Help for the VBA Editor, so I am at a loss to know what you are referring to. Another thing worth mentioning is that a lot of the VBA help seemed to get dropped from the Index and text search when they "upgraded" the help system for Office 2000, Office 2002 (I refuse to use the silly marketing name) fares no better. If you are only looking in the Index or only asking the paperclip you will probably not find anything of use at all for VBA. To get to the VBA help you need to look in the contents under "Automating Tasks" and "Programming Information". Paul |
Basic Stuff.
Woah. Didn't recognize the situation hear. I was one who was
assuming too much. This tidbit may help then. Do you realize that VBA help isn't loaded by default when Excel is installed? Unless you did, try re-installing Excel. Do NOT use the default package. Instead, painstakingly go through all the installation options, and be certain to check all things having to do with VBA. I don't know why Microsoft is being so rediculous as to not have this loaded by default. "Brian Tozer" wrote in message ... Thanks so much for your trouble Kevin in supplying this superb and comprehensive answer. I will play with this tomorrow and get back to you if I have any query. A stroke has made learning a difficult process so I really appreciate your effort. Brian Tozer Kevin Stecyk wrote: Hi Brian, It's good to see you again. I had assumed (always dangerous) that if a person was asking about programming questions, he or she has at least gotten to the VBE editor. But maybe that was too big of a leap? I am using XL 2000, but I think the instructions will apply to your version as well. Here's what you do: 1) Start Excel 2) Go to Sheet1 3) Go to Cell A1 and insert numerical 5 4) Alt-F11 (or Tools | Macro | Visual Basic Editor....takes you to the Visual Basic Editor) 5) Insert Module 6) Copy and paste code (I will provide code again at the end). 7) Alt-Q (or File | Return and Close to Microsoft Excel) 8) Alt-F8 (or Tools | Macro | Macros) 9) You can choose to Step Into or Run at this point 10) If you choose to Step Into....you will be taken back to VBE. In VBE you can press F8 or use the toolbar to execute each line of code. 10 A) As you move through each line of code, have a look the spreadsheet to see the changes. You are done. This is a very, very simple subroutine. It merely demonstrates.... a) Assigning a value from a cell to a visual basic variable b) Assigning a value from a visual basic variable to a cell c) Assiging a string "B1:D1" to a variable which was used to define a range. You should look up in XL's help on Dim (dimension) variables. You should note that I would have been better to use Dim Var1 as a variant rather than as a double. That way, any value could be placed in A1. However, I used double so that most numerical values could be placed in A1. Also, the '\ are simply comments. VBA requires only the single apostrophe to designate a comment. Whatever follows the single apostrophe is considered a comment. However, I use '\ because it is easier to see it. It stands out more. The backslash is merely part of the comment. Sub Test1() Dim dVar1 As Double Dim sImportantRange As String '\ Silly example to demonstrate some basic concepts '\ "A1" on Sheet1 has the numerical value 5 sImportantRange = "B1:D1" dVar1 = Sheets("Sheet1").Range("A1").Value Sheets("Sheet1").Range("A2").Value = dVar1 Sheets("Sheet1").Range(sImportantRange).Value = dVar1 End Sub I hope that helps. Regards, Kevin "Brian Tozer" wrote in message: Lack of totally explicit instructions on how to implement the above example in my Excel 2002. |
Basic Stuff.
"Tim Coddington" wrote in
: Woah. Didn't recognize the situation hear. I was one who was assuming too much. This tidbit may help then. Do you realize that VBA help isn't loaded by default when Excel is installed? Unless you did, try re-installing Excel. Do NOT use the default package. Instead, painstakingly go through all the installation options, and be certain to check all things having to do with VBA. I don't know why Microsoft is being so rediculous as to not have this loaded by default. Even when the VBA help is there it doesn't show up in the index or in the text search. In my opinion the answer wizard is absolutely useless, as it stands you want to search for a keyword in the help and you can't because the answer wizard doesn't know it, whereas the good old text search would have found it. Compare with the HTML help system that came with Visual studio that builds the index and contents on the fly by checking the .chm files in the directory. If the directory contents change, then so does the index. It is hugely useful to be able to step forward and back ... when I read a book I don't have to go back to the contents page before moving on to the next or previous page, nor do I have to guess words out of the index to find the next page. Paul |
Basic Stuff.
Thanks very much for your patience Paul, I have now found the two items
thanks to you, and will devour them. Brian Tozer Paul wrote: "Brian Tozer" wrote in : Thanks Paul for your reply, but none of the terms that you quote apear in the index of the Help for my Excel 2002 or its Help for the VBA Editor, so I am at a loss to know what you are referring to. Another thing worth mentioning is that a lot of the VBA help seemed to get dropped from the Index and text search when they "upgraded" the help system for Office 2000, Office 2002 (I refuse to use the silly marketing name) fares no better. If you are only looking in the Index or only asking the paperclip you will probably not find anything of use at all for VBA. To get to the VBA help you need to look in the contents under "Automating Tasks" and "Programming Information". Paul |
Basic Stuff.
Hi Brian,
My pleasure. Thank you for coming back and reporting that you found the information helpful. The books that were recommended (included again in this message) are very good. Excel 2003 Power Programming with VBA ; John Walkenbach; John Wiley & Sons; ISBN: 0-764540726 Excel 2002 VBA Programmers Reference; John Green, Stephen Bullen, Rob Bovey, Robert Rosenberg; 993 pages; Wrox Press Inc; ISBN 1861005709 Excel 2000 Programming For Dummies; John Walkenbach; 408 pages; Hungry Minds, Inc; ISBN: 0764505661 Because I have not read the third book on the list, I cannot speak to it directly. But John Walkenbach does a very good job at whatever he writes, so I am sure you would find that book good too. I will return to John in just a sec. I found the first two books to be indespensible. For those that are able to learn computing languages easily or already have a strong computing background, the first book might be the best route. But for me, I found having two different but complimentary sources to be helpful. The second book is more advanced but is still a good read. I should note, I skipped chapters in the second book where I lacked interest. For example, I skipped the chapter on COM add-ins. John Walkenbach has an excellent website if you are interested in learning more about Excel. You should browse through his "Spreadsheet Page". He lists VBA code on his website that solves various challenges. It is helpful to review his code and learn. He also has a lot of interesting other stuff about Excel that you will find interesting. He also lists links to other well known Excel sites. Here, let's make this a bit easier. John's site: http://www.j-walk.com/ From there you can readily access the Spreadsheet page: http://j-walk.com/ss/ Links to other good Excel sites: http://j-walk.com/ss/excel/links/ In particular, look at the developer links: http://j-walk.com/ss/excel/links/xllinks2.htm If you have a great sense of humor, be sure to read John's blog (web log) daily. In my opinion, it is one of the best, if not the best, blogs out the http://j-walk.com/blog/ His blog was recently mentioned in PC Magazine: http://www.pcmag.com/article2/0,4149,1400401,00.asp . If you enjoy reading John and other's blogs, I encourage you to use "Feed Demon" an RSS reader which can be found he http://www.feeddemon.com/feeddemon/index.asp . I use Feed Demon to access blogs and get news from various sites, including NY Times. This is probably a lot longer of a response than you were looking for or wanting, but it should provide you with an excellent resource list. Please come back with your questions. I look forward to seeing you again soon. Regards, Kevin "Brian Tozer" wrote in message ... Hi Kevin. Just reporting total success, and I understood everything fine. In the last few days I have gone thru 3 printer ink cartridges printing hundreds of pages of info but have not come across all that you have just explained. Thanks so much again. Brian Tozer |
Basic Stuff.
Thanks to all of you for your responses. Last night, I was finally able
to get my code to do what I wanted, so I am feeling better now. Sam |
Basic Stuff.
I know this thread is a little old, but I just saw that it mentioned my old book. Yes, it is out of date! I do think it did a pretty good job for the time, though. One of the very first sections is Analyzing Recorded Code -- I agree with others on this thread that is one of the best ways to learn
That said, I didn't write Using Excel VBA for beginners (the back cover shows Accomplished to Expert level). I assumed the beginner market would be filled by other, thinner books. I was right to some degree I think Walkenbach Power Programming VBA book is pretty good, though I was disappointed to see it didn't roll in many of the new 2003 features. It's definitely better for beginning programmers than my old Excel book, and I think John is an excellent writer. The Wrox book is also pretty good -- more of a reference. I'm not sure how that fits in now that Wrox is owned by Wiley (I think) If anyone's interested, I've got a new project started at www.mstrainingkits.com/Excel. There are currently draft chapters up there dealing with Excel 2003 features; Lists, XML, and Security. Again, it's not beginner stuff, but if anyone is interested I'm looking for constructive feedback Thanks Jeff Webb |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com