i'm a little frustrated with vba

[kylefoley76]

i'm trying to teach myself vba for excel. it's not that easy. i need it to analyze forex data which is what i do for a living, i day trade the forex market. 95% of the time when you work on the forex market is downtime so i have a lot of time to study vba. anyway, i've been able to master 10 foreign langauges including arabic, sanskrit, ancient greek and heiroglyphs which are not easy languages and i also have the ability to study for 14 hours straight and although i've only been studying excel vba for 5 weeks i'm a little discouraged and think i might not be able to get to the level i want.

here's the problem, the books or videos i consult only take you about a 1/20th of the way there, or they either assume you know a lot and so you can't understand what they're talking about. the examples they give are either too easy so they're useless towards helping you understand the real complex stuff, or they're too hard so you can't understand them.

i've gone through mrexcel's live lessons, vtc.com's videos, excel vba for the absolute beginner, excel programming inside and out and i still feel as though i'm only 5% of the way there.

i'm paying a russian 25$ an hour to help me but i'm skeptical that he can help me get to the level i want.

here's my question. did you guys have prior programming experience? did you take a course in programming? was it just something you've been doing all your life? how long do you think it will take before i get really good at excel vba? do a lot of people have my problem?

 

[Blade Hunter]

I learned firstly by recording macros in Excel and Word and pulling them apart to see how they worked.

From there moved on to pulling other peoples code apart and working out what it did.

I was a professional VBA developer for years based on skills gained with this method.

Since then
From there I have used forums like this. I help out where I can. I generally pick problems that I have a rough idea how to solve but not without working something out. This expands my skills and helps the community.

Hope that helps.

 

[Smitty]

here's my question. did you guys have prior programming experience? did you take a course in programming? was it just something you've been doing all your life? how long do you think it will take before i get really good at excel vba? do a lot of people have my problem?

1. No
2. No
3. No
4. It depends
5. Yes

The answers you get to this will be all over the board.

I think you'll find that most people stumble into VBA by accident as a means of becoming more experienced with Excel, either for work, hobby, school, etc. Although I understand it is (Excel training) becoming more mainstream in school, so I'd expect that to change a bit.

Personally, I've never had a class, but I've done a lot of reading and observing. I've been using Excel since about '94 as a course of work and just learned it over time - I'd used Excel for a few years before I even knew what macros were. How long it takes to get good with it really depends on the level you want to take it. Some people never need to go past the recorder, if get there at all.

And few people can just jump into VBA with no previous experience and make it work right off the bat. A lot of it depends on your usage and needs.

But stick with it - you have a great resource for questions here.

 

[SamTYler]

IMO, the hardest thing to grasp and the thing that almost no book explains well is that MS office applications are based on Objects.

A Workbook is an Excel.Application Object as is a Worksheet. A Workbook has/contains two Collection Objects Named Sheets and Worksheets. They are Collections of references to all the Excel.Application.Worksheets and Sheets Objects related to that Workbook. While they share some Properties, Methods, Events and Objects, they also possess diferent ones. The main difference is that the Sheets Collection also refers to the ChartSheets Collection and a Worksheet is a spreadsheet.

VBA uses the "dot" technique to "drill down" from a parent object to the least significant Object, Method, Property, or Event you wish to access.

Easy example:

Application.Excel.Workbooks("Book1").Sheets("Sheet1")Range("A1").Value

Lets you read or change the Value Property of the Range("A1") Object referenced by the Range Collection of the Worksheet "Sheet1" Object of the Sheets Collection Object of the Excel Application.

Did you notice that the Range Property of the WorkSheet Object returned a Range Object? Many Objects Properties and Methods Refer to an Object of the type "property name" or Type "method name." Whenever you can't find the bug in your code, check that.

Medium Example: Change the Font color in a cell

Application.Excel.WorkSheets("Sheet1").Range("A1").Font.Color

.Color is a Property of a Font Object

.Font is a Property of the Range Object "A1"

("A1") is like the name of a Range Object. It is the Alpha Index of the Range Collection. The Range collection Object has an Item Property whose usage depends on if the Range Collection contains more than one column. The Range Object Item Property index is numeric or alphanumeric and can be "" or "(n, [n|a])."

.Range is a property of the Worksheet Sheet1. It returns a Range Object, if the Range index is ("A1") or a Range Collection if the index indicates more than one cell. Generally you just use Range("A1") or Range("A1:B2") and occasionally Cells(1, 1).

Sheet1 better be a Worksheet Object, AKA Spreadsheet,
("Sheet1") is the index to a WorkSheets Collection Object and a Sheets Collection Object.

.Worksheets is an Application.Excel Property that returns a Sheets Collection Object containing references to all the WorkSheets in the Active Workbook. IOW, a Worksheets Collection Object.

Fortunately, when Excel gets a compiled procedure from the Application.VBE Object, it makes a bunch of assumptions. Unless otherwise stated, the procedure refers to the Active Sheet in the Active Workbook. Most VBA code you see or write will make use of every assumption Excel makes. It's like understanding Classical Greek by studying the language of the dockworkers without a dictionary or thesaurous.

When I decide to learn VBA for Excel, I was laid up from cancer and had the time to develop a comprehensive object model with all the helps in the Cell's Comments. It is 2.3 Mb in size, zipped to 544Kb. It contains 10 Objects models, 1 per sheet, all related to Excel.

I think it would help you a lot. If you are interested PM me your Email addy.

 

[Jon von der Heyden]

I joined MrE back in 2004. Many years on and I'm still learning. Actually I don't think the learning ever stops!

So if you want to learn then here's the thing: Don't just use this forum to ask your own questions. Attempt to answer questions too. And I don't just mean the fast grab ones that you already know the answer to. I mean attempt some questions that are a little out of your comfort zone. It doesn't matter if what you suggest isn't the best. It's ok to get it wrong too. That's what it takes to learn. I think another important note is to participate frequently

 

[Richard Schollar]

I agree with what Jon said - to improve your skills massively, attempt to answer others' questions on the Board. This will lead to the greatest improvement because it will teach you the most about VBA.

 

[SuperFerret]

Everything I know on Excel has come from clicking and picking stuff apart (and the great answers you get on here when you're really stuck).

I have decided that my skills are still on the beginner level, so I am going to go on a couple of courses, starting with an Excel formulas course and moving onto some VBA courses to help me learn (I've been wanting to do a course in something for a while when I noticed these so I'm going for it).

A lot of times it depends how you learn best, some prefer to be hands on and do it to learn, others prefer to read up on it until they understand then try it.

I hope you do learn it, from what I do know it's very interesting (not that my other half thinks so )

Good luck in your quest for knowledge!

 

[arkusM]

I am still early on the learning curve myself, but this is what has benefited me. Have a problem to solve. Figure out what data you start with and imagine the end goal. I find that the "I would like to be able to... " is a good place to start. And start "small", by that I mean don't try and build the god model right off, break you project into steps. Then come here when you get stuck. Like everyone before has said this place is a FANTASTIC resource.
VBA is just another language with some bizarre grammar rules.
Stick with it.

Cheers.

 

[sous2817]

I'm agree with arkusM's response. When I started, I learned the most from trying to work through actual problems that I'm faced with rather than plod through a book.

Also, don't be afraid to ask direct questions that are specific to your situation rather.

Last bit that has helped me a lot was learning how to step through the code and see things happen as the code processes...having 2 monitors really helps (for me at least).