# Is there an API for VBA like there is for Java?



## UWMmakow (Mar 6, 2014)

My only programming experience is with Java, and with basic knowledge of coding and logic from that, I'm teaching myself VBA. My biggest, biggest, *biggest* frustration is that I have no clue where to look to learn the syntax and language! I can imagine the flow of code perfectly in my mind, but I just don't know how to write it out.

Ideally there would be some sort of document like this but I'm beginning to believe that doesn't exist for VBA. I've found this page but it doesn't seem to be complete.

For instance, right now I'm trying to figure out how to link a range to a listbox to populate it from, and I've been Googling around and trying every snippet of code I find to no avail. If there was just a page somewhere that listed all the properties and methods you can call on the listbox class, I'd be golden. But that just doesn't seem to be out there.

Am I just using the reference guide wrong? Are the answers I'm looking for hidden in that page? If not... where in the hell do people go to learn the language??

You can imagine how frustrated I'm getting. I really appreciate any direction on this.


----------



## xenou (Mar 6, 2014)

Hi,

Yes and no.

For core VBA itself, the VBA help file doesn't seem to have many fans but I actually used it a lot when learning vba.  Just hit the F1 key.  If you highlight a word, it takes you to a context sensitive page.  For instance, highlighting a function name or object name gets you the help on that item.  [Note: I find it is best if you *don't* use online resources for help - the local help file is usually more concise and more relevant, whereas online you never know what they will try to feed you as content.  There is some setting for this buried deep in Excel options.]

I highly recommend _VB and VBA in a Nutshell_ by Paul Lomax.  It's a concise compendium of all VBA functions including some related resources like the Scripting library, with clear examples and also explanation of common pitfalls to watch out for.  Cheap used copies were available last time I checked.

Microsoft seems to be changing up it's web pages (again).  I can't seem to find a plain vanilla developers reference.  It exists somewhere.  TechOnTheNet seems to have copied it too, so you can find the same articles googling their site.

Also there is more than one definition of VBA here - you need to learn the _Excel Object Model _if you are programming Excel.  For learning specifically about how to use Excel Objects in VBA then the link you gave is actually an excellent place to go.  And of course reading books and the rest of the usual tips about getting better with Excel VBA programming.  I like the Wrox series called the _Excel VBA Programmers Reference _for learning Excel VBA programming - it's got great explanations of key topics for getting your way around in Excel VBA, and old versions will do in a pinch if you want to save some money - many of the basics are still unchanged.


----------



## RoryA (Mar 6, 2014)

To see what properties, methods and events a given class exposes, the Object Browser in the VB Editor is ideal - just press f2.


----------



## UWMmakow (Mar 6, 2014)

zenou-
The help file has been extremely hit or miss for me. Sometimes it shows me exactly what I need, sometimes, just as with the reference page, it's incomplete and doesn't list the method I'm looking for.

I was just now able to get a few books from someone, and they have been somewhat helpful (although, the problem I'm currently working on, and have been for literally the last 4 hours still isn't solved ). But still none of them list the actual language so I don't feel like I'm getting anywhere.

Maybe I'm going at this from the wrong angle. I just want to see the language, and I feel like I'll be able to piece together everything before that through trial and error. That could be a mistake, that could be jumping the gun, but hell, until I have the language I won't know if that's true.


RoryA-
This seems like it could be the right path. Half of it just seems like another version of the reference page I linked to. But then there's a long list of "Xl---" stuff. Following that, I found XlFormControl>XlListBox, but then, where are the methods you can call on a ListBox? Maybe this isn't designed to answer that question. So then how/what would I use it for? Seeing that ListBox is a FormControl is all well and dandy, but again that doesn't get me anywhere closer to knowing the language and calling methods.


You guys have pointed me in a couple of directions for a wealth of knowledge and I thank you for that. I'm still just sitting here utterly flabbergasted that Microsoft doesn't have the language listed anywhere online, or if they do, it's F***ING BURIED on MSDN. Seriously, even if the language is listed elsewhere online or in books... where did the publishers of that content get the language from? This just isn't making sense to me.


----------



## AngelJ (Mar 6, 2014)

UWMmakow said:


> zenou-
> The help file has been extremely hit or miss for me. Sometimes it shows me exactly what I need, sometimes, just as with the reference page, it's incomplete and doesn't list the method I'm looking for.


Microsoft's documentation is seldom incomplete.  And in the few instances that it is it's not recommended that developers use undocumented methods.  I think it's just unclear what you should be looking for.  You keep mentioning that you want documentation for the VBA _language,_ but you're really looking for documentation for the library you're using.

For example, you mentioned something about a ListBox, which you would find here: Microsoft Forms Visual Basic Reference
The problem with that particular documentation is that it's a bit confusing because everything is considered a "control" and they share a lot of the same methods.


----------



## xenou (Mar 6, 2014)

Post a question about the practical problem you are facing  It's unclear exactly what kind of object you are working with - listbox in a userform?  In a worksheet?  Forms control or ActiveX control?  There are many objects in the Excel programming world.  From what it sounds like when you say "_For instance, right now I'm trying to figure out how to link a range to a listbox to populate it from_" you wouldn't even do this in code.  The simplest and most effective way is to pull up the Listbox properties and provide the range address.  But it makes a diffference what kind of listbox because Excel can use both "Forms" control (which are "native" excel widgets, so to speak), and ActiveX controls, which are "generic" forms controls and have a richer event model and can more easily be programmatically controlled.

Note that it is true as AngelJ says that you need to be very clear when you talk about VBA - the base language is not very complicated.  But it is enriched by the "host application" that is is being paired with - so suddenly at that point you have the whole range of complex applications such as databases, spreadsheet, email clients, userforms, and so on.  So that involves getting to know the "Object Model" of the host application.  Suddenly it's getting complicated!  I think that in general MS does have good object model references online, but I admit I have yet to browse even the complete Excel object model, and I've been programming to it for something like 7 years or more.

Note that I will agree with you that this is a very pathetic page (following AngelJ's link into the form controls objects to the listbox object): http://msdn.microsoft.com/en-us/library/office/gg251523(v=office.14).aspx
Basically it says "a listbox is a control that lists things" 

So I am not unsympathetic to your complaints - but it is possible to learn what you need to know.


----------



## RoryA (Mar 7, 2014)

The Object Browser has a search box at the top so you could just type in ListBox and look at the results. There's a brief highlights page here: Excel Matters » The Object Browser


----------



## AngelJ (Mar 7, 2014)

xenou said:


> Note that I will agree with you that this is a very pathetic page (following AngelJ's link into the form controls objects to the listbox object): ListBox Control
> Basically it says "a listbox is a control that lists things"


Yeah, I've always been so impressed with Microsoft's documentation so that was a real letdown.  I think they tried to make up for it with all the articles under "concepts", but that still doesn't really cut it.


----------



## UWMmakow (Mar 7, 2014)

Well guys I'm glad to see some discussion on this. I was pretty frustrated yesterday so today I'm thinking a bit more clearly. Thanks for bearing with me.

I'm sure that since I'm going at this "sideways", just trying to teach myself and brute force my way through everything, I've missed some core concepts and that's where my inaccurate language is coming from. And a good deal of my confusion, no doubt.

The problem that I'm working on now: I've got a listbox (Forms) on a sheet which, depending on which of 2 option buttons is selected, will either display data from one range or another. This might not even be the best way to accomplish what I"m trying to do:

This is a workbook for data entry by date. The option button is to choose between monthly or weekly. The listbox has the date options in it, so it'll either be linked to a range of months, or a range of 52 weeks. Now I could just put two listboxes on top of eachother and bring the correct one forward, but I feel like there should be a better way to do it.

Anyways I thought the correct code was something along the lines of 
	
	
	
	
	
	



```
Sheets("Input").MonthOrWeekList.RowSource = range
```
, but obviously not.


----------



## AngelJ (Mar 7, 2014)

In that case, it sounds like you need the List property of the ListBox object, and the Value property of the Range object.  Something like this:

```
Sheets("Input").MonthOrWeekList.List = Range("A2:A4").Value
```

Edit: Also, based on the libraries you're using, for documentation I would use the Object Browser and the Help feature (F1) as others have suggested, rather than the reference on MSDN.


----------



## UWMmakow (Mar 6, 2014)

My only programming experience is with Java, and with basic knowledge of coding and logic from that, I'm teaching myself VBA. My biggest, biggest, *biggest* frustration is that I have no clue where to look to learn the syntax and language! I can imagine the flow of code perfectly in my mind, but I just don't know how to write it out.

Ideally there would be some sort of document like this but I'm beginning to believe that doesn't exist for VBA. I've found this page but it doesn't seem to be complete.

For instance, right now I'm trying to figure out how to link a range to a listbox to populate it from, and I've been Googling around and trying every snippet of code I find to no avail. If there was just a page somewhere that listed all the properties and methods you can call on the listbox class, I'd be golden. But that just doesn't seem to be out there.

Am I just using the reference guide wrong? Are the answers I'm looking for hidden in that page? If not... where in the hell do people go to learn the language??

You can imagine how frustrated I'm getting. I really appreciate any direction on this.


----------



## xenou (Mar 7, 2014)

I could be wrong but I don't think you can set a _Forms _Listbox range in code.  You can set an _ActiveX _Listbox in code.  For a forms listbox you would want to use a strategy like a cascading combobox - set a range in the listbox that is a named range, with its reference containing an Indirect() formula that points to the range you want to use.  The range you want to use would depend on the option chosen, and this triggers either one named range or the other named range (months or weeks).  I'll see if I can cook up an example when I'm home tonight.  No code at all!  Conversely, use an ActiveX listbox and you can use code - as well as catch events such as the option button being clicked.

ξ

Note: by Forms controls, in an Excel context, I mean what you see when you go to the developer tab and click the Insert button.  There you have the choice of "Forms" controls, and "ActiveX" controls.  Forms controls here mean (really) "_Excel Forms Controls_".  The were developed specifically for use in and with Excel.  But these forms controls are *not* the type of forms that show up in the links we have been posting in earlier posts about Listbox objects.


----------



## RoryA (Mar 7, 2014)

You can certainly set a Forms control's properties in code. It's easier using the Activesheet.Listboxes("List Box 1") syntax to access them.


----------



## xenou (Mar 7, 2014)

Good to know.  Question: can we intercept the option button change if we are using Forms option buttons?  Or do we need to look for a worksheet change event or worksheet calculate event?

ξ


----------



## AngelJ (Mar 7, 2014)

xenou said:


> Good to know.  Question: can we intercept the option button change if we are using Forms option buttons?  Or do we need to look for a worksheet change event or worksheet calculate event?
> 
> ξ


The only way I could find was the "Assign Macro..." feature.  It only lets you deal with one event (click for buttons, change for listboxes), but off the top of my head I can't think of an instance where that wouldn't be enough.


----------



## xenou (Mar 7, 2014)

Hi,
Okay, as promised a sample file for a no-vba method attached (we just use plain vanilla forms controls and defined names.  Recommended!)  Also a vba version - intercept the calculate event to capture changes and use the method recommended by Rory to update the list box accordingly.  For the record, used the Object Browser and looked under OLEObject to get Listbox methods and properties.

SAMPLE FILE
sha256 checksum (zip file): 7acb449f6e718772a023028bec3e861d7fbf47581aaecd517b43ce7abea28e95

As you would expect, Book1.xlsx is the non-vba method and Book2.xlsm is the vba version.

EDIT:
I think I like AngelJ's suggestion better so for the VBA version here is a revision.  We attached a macro to the Radio Button (actually, to both of them, but the same macro):
SAMPLE FILE
sha256 checksum (zip file): 
54d02423ddcd6aa9fe4ac20824ede99d1efca8ca4cac4505610d0eb9fc19147c

```
Sub Change_Sheet1_Listbox_Source()
    
    If Sheet1.Range("_Option_Link") = 1 Then
        Sheet1.ListBoxes("List Box 4").ListFillRange = "Sheet1!_Data1"
    Else
        Sheet1.ListBoxes("List Box 4").ListFillRange = "Sheet1!_Data2"
    End If

End Sub
```


----------



## UWMmakow (Mar 10, 2014)

@AngelJ- The code you had in post #10 works if you do .ListBoxes("MonthOrWeekList"). So many thanks for that!!

@xenou- First thank you for the note in post #11, I'll have to keep that in mind as I learn more about the Excel object model and begin using the MSDN reference materials. Now the code that you've provided. Well I tried setting up a similar situation, naming the ranges how you did, and all I did was change [Sheet1.] to [Sheets("Input").] but that comes back with a syntax error. Very peculiar. I'm also wondering if there's any benefit to coding it like that rather than just the single line AngelJ provided?


Also, one more thing that struck me as really odd. There are two different pieces of code that I can use that work in two different ways:

```
Sheets("Input").ListBoxes("MonthOrWeekList").ListFillRange = range("RD5:RD15")

AND   

Sheets("Input").ListBoxes("MonthOrWeekList").List = range("RD5:RD15").Value
```

If I use the first option, the listbox populates one single row, corresponding to cell RD5 i.e. the listbox would just show January. Literally, the Input Range in Format Control for the listbox would be "January". If I changed the range to "RD4:RD15" and in RD4 I put "RD5:RD15" then it would populate correctly! So really now what I'm wondering is, what is the difference between .List, .ListFillRange, and .RowSource? Why does excel treat them all so differently?


----------



## RoryA (Mar 10, 2014)

List takes an array of values. ListFillRange and RowSource both require a String (the address of the range), so your first code should be:

```
Sheets("Input").ListBoxes("MonthOrWeekList").ListFillRange = "RD5:RD15"
```


----------

