Last-modified: 22-Aug-95

The following symbols indicates new & updated topics:

• [++] means topic is updated in this issue
• [**] means topic is new in this issue

Hope this makes it easier for Our Regular Readers ;-)

• 1. Any tips for VB/Win 3 programmers moving to VBA?
• 2. Does VBA support VBXs?
• 3. How do I access properties on my dialog boxes in VBA?
• 4. How do I use database routines from Excel VBA?

I. VISUAL BASIC FOR APPLICATIONS (VBA)

1. Any tips for VB/Win 3 programmers moving to VBA?

You are in for some surprises. VBA is more unlike VB 3 than most people thought. Especially the development environment is very different, and the language puts more emphasis on objects. The latter is a trend you can get used to for VB also. For Excel 5 VBA, be aware that the environment is based on the "workbook" idea Microsoft stole from Borland. Your controls will be placed in one sheet, and the code will be in another. Doubleclicking on the control to open the code window doesn't help. You have to use the "Tools|Assign Macro" menu option.

Also, be aware that the list of events is nowhere close to what VB3 supports! No GotFocus, no MouseMove, no nothing. You'll be very confused if you try to look for "events" in the VBA docs!

[Top of Page][Table of Contents]

---

2. Does VBA support VBXs?

No. If Microsoft have its way, VBX is a dead end. There will never be 32-bit VBXs, but OCXs using OLE 2. VBA is more a subset of VB 4 than VB 3, but it does not fully support OCX yet. It will, though.

[Top of Page][Table of Contents]

---

3. How do I access properties on my dialog boxes in VBA?

As noted above, VBA is a culture chock to VB programmers. If you create a textbox in VBA, call it txName and try to

cMyVar=txName.Text

the impolite interpreter will give you a "variable not defined" error. The magic is objects. You have to

Dim txName as Object

Set txName = DialogSheets("NameDialog".EditBoxes("txName"))

And then you can access your properties like you used to in good ol' VB 3. (Anyone volunteer to beat senseless the guy who thought out this?)

[Top of Page][Table of Contents]

---

4. How do I use database routines from Excel VBA?

The documentation is somewhere between sparse and inexistant on this topic. Any info on VBA and SQL would be much appreciated. Here Microsoft breaks the tradition and you *can't* use database objects, at least not the way you do in VB. Also, forget dynasets. I know nothing about databases in VBA. I just bring on the following tips from various magazines:

• Both SQLOpen and QueryGetData require a 'connection string'. That's about what the doc's say about the parameter. What is it? The doc is also tragically void of useful examples. Someone dug up the following example:

  "DSN=My data file;DBQ=c:\access\data.mdb;FIL=RedISAM;"
  
  

  which is about as understandable as it looks. If you use an empty string, you get a dialog which also can give you the string into a spreadsheet cell.

• Also, search for SQLREQUEST in the *main* help file for Excel 5 (not the VBA help!) for these examples of connection_string's:

  dBASE		DSN=NWind;PWD=test
  
  SQL Server	DSN=MyServer;UID=dbayer;PWE=123;Database=Pubs
  
  ORACLE		DNS=My Oracle Data Source;DBQ=MYSER VER; UID=JohnS;PWD=Sesame
  
  

  There's a KnowledgeBase on Excel 5 on ftp.microsoft.com. Last time I looked, it was void of database stuff. Still, it may be a good idea to download it as the situation may have changed now.

[Top of Page][Table of Contents]

[Previous Section]