Do you have any recommendations for web sites or books that would be good for step by step learning of VBA/Excel?



I did a google search but that came up with lots of stuff.

Jonathan.

Okay so I generally understand some of the concepts here. The VBA code that is written searches the particular date column and if it comes across any data in that row it records it in the printout sheet starting in Z1 and working on down, pulling the associated name in that row.

I guess where I am struggling is that if it finds nothing in the first row it records noting in Z1 of the printout sheet. So in the case of the date that has been shown it ends up skipping the first 7 lines of the Z column and finally recording the name of Derrick Bishop in Z8.

So in the actual printout what confuses me is how does the formula that is there know to skip the first 7 rows that no data exists and move to row 8. I would think that it would record no data in the first 8 rows. I guess I am really confused by how the formula knows to skip no data.

So then if i added more cells to create additional spaces, and use the fill handle its just going to produce the next number.

Still confused. Thank you for helping me learn this though because it has been interesting. I understand the worksheet a little better, but i admit that while i generally understand the concepts of the VBA code the specifics of the lingo that is being used is way over my head.

Jonathan

Not Z1; it starts in row 8. Notice the line in the program that says "y = 8"? It uses y as the row number for the output; it starts it at 8 and then increments it from there. I don't know why it should use 8, but it doesn't really matter.

And that's why the formula skips over the first seven rows: because the author (I presume that, too, was excelledsoftware) knew that's where the data started. So you're not imagining things; the formula had to point to the right place. It's just that the formula was told that the right place to start was row 8—that is, it was constructed using Y8 as the starting place.

Does that help you see what you have to do? If you add more lines to the bottom of the report (after row 32, I mean), how does that affect what has to happen in the formulae in cols C and D?

Whenever that happens, stop me and ask! I used to think there must be something wrong with me because I didn't understand the terminology that other people considered basic, and what I learned is not that there's nothing wrong with me but that it doesn't matter: The only way to make up the deficit is to start asking, even at the risk of making a pest of myself.

Here is my recommendation when it comes to learning VBA or any language for that matter.

All computer languages have functions, methods, variables blah blah blah. What I have found is that you need to program your mind first before programming starts to make sense. Here is my suggestion on what you should learn for VBA and in what order.

1. Macro recording (Start using the macro recorder to do simple operations then look at the code after. Trust me a lot of it wont make sense but some will.
2. Write your first msgbox macro Something simple like
sub boxxy ()
msgbox "Hi there everybody"
end sub

You will notice on the msgbox when typing that it will show you alot more things you can enter. Here is a hint if those things are in [square brackets] they are optional.
3. write your first inputbox macro (Very similar to msgbox)
4. Start understanding variables and datatypes. There is a ton of info out there on these. Use simple ones like strings and integers to begin with.
Example
dim boxxy as string
boxxy = "Here is a message boxy from boxxy"
msgbox boxxy
end sub

5. once you have variables down a little bit start writing if statements. Try it with an input box and a message box.
Start with
sub boxxy ()
dim boxxyPrmpt as integer
boxxyStr = "message 1"
boxxyprmpt = inputbox("Enter a number from 1 to 100")

if boxxyPrmpt > 50 then
msgbox "Higher than 50 entered"
else
msgbox "Less than 50 entered"
end if
end sub

You can even declare 2 more string variables and assign them to the 2 msgbox's

6. Loops learn the For (stands for "For this instance") and Do You will also need to understand the range function and maybe cells
research these and let us know what you run into.

It goes slow at first but gets a lot easier as you go.

Okay I think i got it all figured out. Take a look guys and let me know what you think.

Jonathan

2013 Membership Attendance.xls

I don't use tutorials and texts much. I read documentation, mostly references and once in a while, when I'm desperate, user's guides. But such as I have give I thee:

Microsoft's Developer Network (MSDN) has their official documentation for all the Office apps, all versions, plus a lot more. Start there and drill down to "Office and SharePoint development" and "Office client development"; that'll show you a list of all the currently supported versions of Office. Pick the one you have. Say it's "Office 2010": There you'll see a list of Office apps: Access, Excel, Outlook etc. Choose "Excel 2010", then "Excel 2010 Developer Reference", then "Reference"; that gets you the documentation on each object used in Excel 2010, with a list of each object's properties and methods. Some of the articles are missing important elements, and this isn't nearly as convenient (to my way of thinking) as the CHMs they used to provide, but it's Official and it's a heck of a lot better than nothing.

I don't know whether it'll be helpful, but you may get some hints by looking at the Access development section; since you already know something about that, seeing the information there may tell you more about how the Excel section is organized. But maybe not.

For the VBA language itself, go back to "Office 2010" and then select "Office 2010" (yes, again). This has a list of various features of Office itself, rather than of any particular application, including "Visual Basic for Applications Language Reference for Office 2010"; select that, then "Visual Basic Language Reference" to see a general reference on VBA.

Another one I use a lot—dunno whether it'll interest you—starts at "MSDN Library" and then goes down through "Web Development" and "Scripting". Here you'll find a reference on VBScript, which is a flavor of VB that's cut down some from VBA but has the advantage of being usable in Windows, ie not from any Office application itself; you can write a VBS program in Notepad, then double-click on the icon to get it to run. Also under Scripting are a couple of objects that I find useful in VBA even though they're not documented in Office: The FileSystemObject, for example, which lets me read and write plain-text files, look up documents in directories and such. There's also an object for handling regular expressions; a handy thing, that, though it took me a while to learn what they're good for. If you start using VBScript much, there are some other handy sites too.

Other than that, when the MS documentation fails me (and it does; I try to write user add-ons, but for some reason it always experiences an error so I'm unable to fill in the gaps), I just google my question and discover that it's usually been asked before, and answered. That's how I found this forum, and there are a few others too. But no doubt if you use a search engine you'll find your own favorites without any pushing from me.