VBA - Where to Start - Please Advise

[ladyck3]

I've been getting a lot of help from this forum but I've reached a road block... ME. I need to learn 'something' in the basics category to understand what people are telling me.

Skip has the patience of Job, I mean it, but I cannot do this any more and feel good about requesting assistance if I don't have some sort of rudamentary knowledge.

I don't have anyone in my work environment to coach me or bounce ideas off of and I'm at the very edge of my limits and ready to JUMP.

IF I can just get some basic information under my belt I think this will be beneficial to me .... but where does one start?

I'm told I should learn Javascript to get a general bottom shelf understanding of scripting or programming or however I should label this but I need your advise. VBA is Visual Basic for Applications, as my understanding so wouldn't it be more beneficial to learn Visual Basic?

I hear C, C+, C++, C+++ to infinity, or Perl or this or that.. and their own purposes whether its programming or web construction I get that too. I've worked with ASP some, with the help of a coworker who swears I need to get into SQL instead of ASP because he hates Access.. TOUGH.

Ok, off on tangents. Bottom line, what do I need to do to START I mean out of the door knowing nothing about any programming languages, their speak, their syntax, how to read or write it... what is the very basic first step?

Is there a resource you can recommend? I can't even imaging taking college courses for this because my needs and desire is not to become a programmer but to understand a few simple things or what I perceive to be pretty simple for those in the know. I'm old enough to be retiring in a few years but young enough to still want to shine and present tools to my group that are valuable and I've reached the point to where what I need to accomplish these tasks is an understanding of what I'm working with.

I see DIM have no clue why its there, have no clue about that or OBJECTS or Troubleshooting code by F9'ing to break it and F8'ting to find out where it is in the spreadsheet to understand what the heck I'm looking at (God Bless you Skip for your patience I swear you are a SAINT).

I mean we are talking B A S I C some sort of resource that I can at least get my feet wet. I purchased the book VBA for DUMMIES a couple of years ago but is that where I need to start? But you see, all of the books I've found figure you have SOME clue about SOMETHING before you start.

I don't.

Help?

Thank you.

LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

I'd just stick with Excel VBA, cuz that's what you're gonna be using almost exclusively, I'd guess.

Start using your macro recorder to record any sort of redundant task that you might have in Excel. Look at the recorded code. Use VB HELP to determine what objects, properties and methods are in the code you recorded. Use your DUMMIES handbook also.

Use SEARCH to find topics of interest in Tek-Tips or elsewhere on the web.

http://www.microsoft.com

is a valuable resourse for VBA, too.

It is essential to understand Objects, particularly the Excel Object Model. You do not need to know EVERYTHING about the model. Once you have a basic understanding of Objects, Properties, Methods and you get the picture of what inheritance is all about, things start making more sense.

There are many members here at Tek-Tips how get a great amount of satisfaction sharing information and 'seeing' other members take off mor on their own. Take advantage of that. If you have noticed, I encourage people to discover things for themselves, whenever possible, often by asking questions myself.

Good luck.

BTW, I like John Walkenback's books, but that's a personal preference.

http://www.j-walk.com.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

If you purchased VBA for Dummies, did you try and use it? It is not the best out there, but it is something you can start with. It does in fact cover starting from the beginning, sort of.

As for other languages, if you are going to primarily be working with Office apps, then VBA is where you start. If you are not going to be using Javascript, then why start there?

It takes time, which means it takes patience. You will find encouragement here. Most importantly, it takes actually trying to do something, of writing actual code and seeing what happens.

Recording macros and looking at the resultant code is helpful, but only to a point. For example, you mention using Dim. You will never have a Dim in a recorded macro.

So, you need to ask specific question, here if you like. If you ask politely, and indicate a willingness to listen, this is a fantastic site to learn from.

Keep questions as specific and explicit as possible. Digestible chunks. To answer re: Dim.

Dim is a declaration of a variable.

Variable have data types. A string (text) is a data-type.

Dim strClient As String

Other data-types are various number structures: Long, Double, Single

Date is a data-type.

If a variable is Dim'd with no defined data-type, it is inherently declared as a Variant data-type. That simply means you can toss in ANY type of data into it.

What does THAT mean? It means that a variable declared as a String can only be given a string as a value. It means a variable declared as a Long can NOT be given a string value.

Dim lngMyNumber As Long

lngMyNumber = "Yadda yadda"

This is a syntax error. The data-type is declared as a Long (a number), but you are trying to give a value of a string (text).

The most important (IMO) aspect of Dim-ing variables is SCOPE.

When you Dim a variable, essentially you are telling VBA to assign a block of memory to it. That block of memory does not have a value yet. The value is a value after you GIVE a value.

Dim lngMyNumber As Long
' a block of memory assigned to the variable, but has NO value in that block.

lngMyNumber = 23451
' the value 23451 now in that block

But.........


That block of memory only exists (assigned to that variable)while the variable exists. This is Scope.

Public strYadda As String

Sub One()
Dim strBlah As String
  strYadda = "Ho-hum"
  strBlah = " whatever"
  MsgBox strYadda & strBlah
End Sub

The message box will display: "Ho-hum whatever". Seems simple enough. Suppose right after you execute Sub One, you execute Sub Two:

Sub Two()
  Dim strNext As String
  strNext = " the next one"
  MsgBox strYadda & strNext
End Sub

The messagebox displays: "Ho-hum the next one"

Why? Because strYadda has PUBLIC scope. This means it exists (and can be used) in any procedure in the code module. Here is a different Sub Two:

Sub Two()
  Dim strNext As String
  strNext = " the next one"
  MsgBox strYadda & strBlah & strNext
End Sub

This will fail. Why because the variable strBlah is ONLY in Scope (it exists) inside the procedure it is declared in. In other words, it ONLY exists for the instructions of Sub One - where it is declared (Dim). So when the VBA parser comes to it in Sub Two, it basically says..."Huh? I have no idea what the heck strBlah is. There is no variable strBlah."

And there isn't. There is only a varaible strBlah while Sub One - where it is declared - is being actioned. Once Sub One terminates, ALL variables declared within it are destroyed.

Note however that the Public variable strYadda still exists. It keeps it memory block, and its given value for as long as the code module is available. In most cases this means the entire time.

Which is why variables (for the most part) should be declared and used only for the time (procedure) that you are actually using them.

In other words, if a variable is only used in one procedure, there is NO point in declaring it as a Public variable. The flip side to that is, if a variable is used in multiple procedures, then it should be declared as Public.




Oh, and yes....Skip is a saint.

Gerry

 

[mintjulep]

To paraphrase Yoda, you don't have to know anything, you just need to do something.

You can't learn how to program by reading a book.

Step 1: Identify something that you need to do.

Step 2: Assume that there is a way to do it.

Step 3: Find the way.

Step 4: You're done!

(Step 3 can be the hard one)

So what do you need to do?

 

[jjjjjjohn]

Hi Laurie,

IMHO you should take a class at the local community college or whatever.

You say you don't want to become a programmer but want to understand what an object is or what it means to declare a variable. These may seem like simple things buy I think they are actually VERY difficult concepts for a non-programmer. And at the same time they are very important. Without them you can play around with the code a little but you will hit a wall very quickly and find that things just don't make sense anymore.

A forum is a great place when you have specific questions but as a beginner you usually don't even know WHAT you should ask and, as you yourself have mentioned, most books assume some previous knowledge of programming.

Hope that helps.

Regards,
John

 

[ladyck3]

For those who have asked "What are you trying to do?" please read:

Excel = Enter Static Date when Reaching 100%
thread707-1583430

What am I trying to do? As John said.... I am lost.

Gerry, Skip is a saint and he's worked with me so diligently but I feel so stupid. I know the reason Tek-Tips is here is to help each other but in this instance, Skip... you have spent TOO much of YOUR time on me... and I feel horrible.

You are right, you are giving me tools and as you said:

If you have noticed, I encourage people to discover things for themselves, whenever possible, often by asking questions myself.

Oh yes, I know.. but as I said, I've got multiple instances of the same code and the break it and see what is happening stuff is a fantastic idea... Troubleshooting is totally what I do... its been my career until recently but when I'm troubleshooting something that I have NO CLUE about I just get frustrated and my brain revolts.

Let me give you a pattern for a crochet doily in graphic form and written instructions and see how much sense it makes to you.... (taking it for granted that you probably don't know how to crochet) but your significant other or yoru mother or someone in your life may know how and you can turn to them and get a head start with someone reading the pattern side by side with you and show you how the stitches are made.

You are so painstakingly giving me code and when its broken, you are giving me patient steps to help me troubleshoot but I honestly don't understand it and I've got NO ONE in my life that I can talk to about it because there's no one that knows this stuff....

So I've decided that I really do want to get this protection stuff working as we started but I need a ground level understanding of what it is I'm looking at before I can even try to fake my way through this.

I'm so eternally grateful for everyone's help and from time to time I've been able to help others (which pleases me to NO END) but I'm tired of feeling like I'm taking and taking too many people's hard earned valuable time... For VB or VBA I need to have a grip a little bit anyway.

And yes, it will be for Excel only.

Gerry, God Love you but huh? I kinda understand what you said.... some of what you took so much time to send kinda made me go HUH? some of it does make perfect sense but I don't know enough to write it and test it.

I'm going to go the Make a Macro route... and I thought I had John Walkenbach's book.. I think I have it in PDF but not hard copy... no I bought it I have to find it (we're in a state of flux right now, my baby is moving out...) FREE ROOM

Love to all, I mean that... thanks for being there and I appreciate you all!

THANK YOU!


LadyCK3
aka: Laurie

 

[mintjulep]

Ah!, now we have a starting place. You threaten us with a crochet pattern. Something like this perhaps?

Rnd 1: Start with an adjustable ring in the light brown yarn, and crochet 4 sc into the ring, pull tight and join with a slst.
Rnds 2-4: *sc in next sc, 2 sc in next* repeat from * around, ending up with 6 sts in rnd 2, 9 sts in rnd 3, and 13 sts in rnd 4.
Rnds 5-7: Sc in each sc around. (13 sts)
Rnd 8: Switch to the dark brown yarn and crochet 2 sc in each sc around. (26 sts)
Rnd 9: *sc in next sc, decrease in next* repeat from * around. (13 sts) Now is a good time to start stuffing.
Rnds 10-12: Continue to decrease every other stitch, ending up with 9 sts in rnd 10, 6 sts in rnd 11, and 3 sts in rnd 12. The stem should be naturally formed by rnd 12.
Bind off, weave in end, and embroider a cute little face!

Well, it doesn't mean anything to me. But guess what?

That's a program!

It's a series of predefined steps, to be executed in a specific sequence, represented in a language with a specific lexicon and syntax.

It's a program.

So you DO know how to read and interpret a program. Now all you need to do is learn the rules for a different language.

 

[ladyck3]

UPDATE: I've got John Wallenbech's Excel VBA for Dummies book, its for Excell 2000 and 2003 but I'm sure it will be useful

Thanks for the tip I'm into Chapter 1 ..

I do have one question... About Macros and shared workbooks (before I start reading). The workbook that I'm using has a macro which takes the user from whatever sheet they are on to the main index page and then saves the workbook. This way no matter who uses it, they are all opening it to the index page when they open it....

But one of the users got a message (as I did see when saving it this time) that Macros are disabled in a shared environment.. this is not a matter of changing Macro Security is it? Am I hosed (for now?)?

Ok, thanks


LadyCK3
aka: Laurie

 

[ladyck3]

Well there ya go.... I'm all that! (NOT) hehehe but Gerry I think you are a girl right? And you know how to crochet, right? I was throwing that out to the fellas who were probably not crocheters, showing how they might be a bit confused at reading a program and understanding how it works.

But... touche' m'friend... touche' <grin>

THOSE.. "PROGRAMS" I've been reading and executing for almost 45 years.....

GO ME!

And to you... "Smarty-pants"


LadyCK3
aka: Laurie

 

[SkipVought]

Macros (VBA) can be RUN in a shared environment.

However, you cannot open end edit the VBA code while the workbook is shared. It FIRST must be made Unshared, to keep other users from saving while you are editing VBA code.

Once you are finished updating your code and saving the wokbook, you can make it shared.

HOWEVER, for a novice, coding in a workbook that will be shared might not be the best way to cut your programming teeth. There are features in shared workbooks that are limited. Have you read Excel Help on SWs?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

I will go back and look at the help file again, I did see some of your feedback from 2002 but I figure that maybe Office 2007 is a bit more mature by now?

HA, sure

Anyway, I had it working b efore I shared it. I unshare it when I need to make changes and then I reshare it.

I'll take a look again tomorrow

Thanks for getting back to me.

LadyCK3
aka: Laurie

 

[ladyck3]

OOPS... Correction

I said:

Well there ya go.... I'm all that! (NOT) hehehe but Gerry I think you are a girl right? ....... etc

I'm getting my helpers all mixed up (and you think I can program??) I meant MintJulep ... regarding the crochet analogy

ok.. g'night folks I have reading to do

LadyCK3
aka: Laurie

 

[fumei]

Personally, I think you are doing far better than you are giving yourself credit for.

Just keep working at it, doing things. THAT is the key. Actually trying to do stuff. Yes, it is important to understand things, it decidely makes things in the long term much much easier. But all of us here started the same way...trying to do stuff.

I think you are using this forum precisely as it is intended to be used. I would not worry about taking up too much of anyone's time. We do this because we want to, and believe me, if Skip ever feels like he is being taken for granted or inappropriately being used...you would hear about it!

What keeps us going is being appreciated. Heck, we certainly are not getting paid. So if I may speak for Skip (a dangerous assumption), dinna fess yourself. As long as you are appreciative, and clearly indicate that you are trying to learn - i.e. you display effort in that direction - he will be there.

As for taking a course, well...maybe. Unfortunately there are few (if any) courses on VBA. Other languages, yes. It may be helpful, but it may be detrimental as well. Hard to say.

Gerry

 

[ladyck3]

Thanks Gerry, I appreciate knowing y'all are in my corner

And to Skip, I did find out the problem with the "To Master & Save" macro... the user's Macro Security setting was at the default.. we enabled macros and all's well.

So see, I'm not as dumb as I look because I thought of that troubleshooting measure all on my own

<reading> See ya later

LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

I agree with Gerry. Do not deprecate yourself. There is a world of difference between viewing yourself as honestly as you can and belittling your capabilities.

Be an honest seeker. Admit your needs, but begin to gain confidence in your skills and abilities. There are lots of enablers, here at Tek-Tips, of that kind of behavior. I get great delight in observing each "Eureka!" Its better than a little purple star.

And I am seeing these things in you! Press on!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Pressing on:

I created 2 macros... the first one works, the second one doesnt but I know why it doesn't, its variables and I don't know if taming the variables is possible. If it is then just let me know. Its not important at this time that I tame the variable because I'm just trying to test the boundaries of a macro.

The first macro takes information I have copied from a report on the web and it Inserts a column between A and B so that I can use Text To Columns and separate the data at the comma.

02/15/2010,21:49:00

I need the dates only so I just split off the time. That works. I stopped recording because this next section did not work so I thought well record it separate and see what happens. I get it.

Sub PendPiv()
'
' PendPiv Macro
' ceate Pivot Table for Pending
'
' Keyboard Shortcut: Ctrl+t
'
    Columns("A:H").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R1048576C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet10!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet10").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Date"), "Count of Date", xlCount
End Sub

Its breaking at the sheet reference because when it creates a new worksheet, the sheet number will change. So I deleted the one that I had,knowing the next numbered sheet would be Sheet10 and I entered that into the macro in the two sheet reference locations.

It created a pivot table but when I recorded the macro it did not catch one function.

The Date Column (Column A) when I create a pivot table manually is drug into the ROW LABELS box and it is also drug into the VALUES box because I am checking to see how many pending files we have and from what dates.

The macro did not drag the date into the ROW LABELS box, only into the VALUES box.

So the 2 things I'm wondering about are these:

1. How does one deal with the changing sheet number differences to use this daily. I do this EVERY DAY in a new workbook EVERY DAY.

2. Why didn't the ROW LABELS thing work but the VALUES does?

I am going to try this again but this time keep the pivot on the same page as the data and see if it works.

OH... Skip.. I'm gonna have ELECTROLUX moments though not EUREKA! hehehehe

LadyCK3
aka: Laurie

 

[fumei]

Skip may very well respond, but if I may suggest, as the subject of this thread is a general plea for VBA help, it may be more helpful for OTHER people if you post your specific question in its own thread.

Gerry

 

[ladyck3]

Good idea

LadyCK3
aka: Laurie