How to use Names in VBA

[kurb]

In Excel you can Name columns and rows and use the names to reference a
cell. eg.
= rowName columnName, gives the value of the cell where the row and
colum intersect.


Is there anyway I can do this with Arrays. I can do this by
substituting the column number to represent column Name. Everytime the
program sees a Name it has torkout the colum number which is inefficient
for the application. Since the Names are variables. the question is how
can the program assign a number value to a variable Name when its first
encountered, so that when the user uses that Name again the program uses
the associated number value

Thank for any suggestions

Kurb

 

[Tom Ogilvy]

Not for arrays. Perhaps you want to use a collection or a dictionary
object.

 

[kurb]

Thank you very much.
The dictionary object looks to be useful.

Is there a similar object or method for using multiple keys to add or
retrieve an item. Something like
Object.Add key1,key2,key3, item

Where may find a list of objects that might be useful for creating a
relational database in Excel.

Thanks for any suggestions

kurb

 

[Tom Ogilvy]

Multiple keys - not that I am aware of.

Excel interfaces with Access and other databases - so it might be best to
use a relational database to do relational database work.

 

[Jamie Collins]

Multiple keys - not that I am aware of.

Latest instalment in my continuing series, 'You Can Use ADO For This':

Sub test()

' Fabricate a disconnected ADO recordset
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

With rs

.CursorLocation = 3 ' 3=adUseClient

' Item field to hold varient values/objects:
' 12=adVariant
.Fields.Append "Item", 12

' Key fields:
' 129=adChar, 35=MaxCharWidth
.Fields.Append "Key1", 129, 35
.Fields.Append "Key2", 129, 35
.Fields.Append "Key3", 129, 35

.Open

' Create sample data
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Tinatotac", "Name1", "Name2", "Name3")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Livehulas", "Name2", "Name3", "Name4")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Regisaver", "Name3", "Name4", "Name5")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Hevitoxic", "Name4", "Name5", "Name6")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Luxasonic", "Name5", "Name6", "Name7")
.AddNew _
Array("Item", "Key1", "Key2", "Key3"), _
Array("Katewudes", "Name6", "Name7", "Name8")

' Locate items where Key is 'Name3'
.Filter = "Key1='Name3' OR Key2='Name3' OR Key3='Name3'"
MsgBox "Matching items:" & vbCrLf & .GetString

End With

End Sub

Excel interfaces with Access and other databases - so it might be best to
use a relational database to do relational database work.

And, if the OP means create a relational database from Excel ... yep, ADO again:

Sub Test2()

Dim Cat As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String
Dim strSql2 As String

' Amend the following constants to suit
Const PATH As String = "" & _
"C:\"
Const FILENAME_JET As String = "" & _
"New_Jet_DB.mdb"

' Do not amend following constants
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>"

' Build connection string
strConJet = CONN_STRING_JET
strConJet = Replace(strConJet, "<PATH>", PATH)
strConJet = Replace(strConJet, "<FILENAME>", FILENAME_JET)

' Build sql statements
strSql1 = ""
strSql1 = strSql1 & "CREATE TABLE Pilots ("
strSql1 = strSql1 & " ID INTEGER NOT NULL PRIMARY KEY,"
strSql1 = strSql1 & " lname VARCHAR(35) NOT NULL,"
strSql1 = strSql1 & " fname VARCHAR(35) NOT NULL,"
strSql1 = strSql1 & " mname VARCHAR(35) NOT NULL"
strSql1 = strSql1 & " DEFAULT '{{NA}}'"
strSql1 = strSql1 & ");"

strSql2 = ""
strSql2 = strSql2 & "SELECT ID, lname, fname"
strSql2 = strSql2 & " FROM Pilots;"

' Create new Jet database
Set Cat = CreateObject("ADOX.Catalog")
Cat.CREATE strConJet

With Cat.ActiveConnection

' Create tables
.Execute strSql1

' Create some sample data
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (1, 'Livehulas', 'A')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (2, 'Katewudes', 'B')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (3, 'Hevitoxic', 'C')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (4, 'Norarules', 'D')"

' Query data
Set rs = .Execute(strSql2)
MsgBox rs.GetString

End With

End Sub


Jamie.

--

 

[kurb]

Hello
Thank you very much for responses.
I'm not ready for this yet.
What's a good reference book (for an eternal novice programmer) for
using ADO with Excel

I'm wondering if there's a solution here for a problem I currently have
with Excel

I retrieve/store lots of data between multiple
workbooks/worksheets/columns and Arrays . Is there an efficient way of
accessing and chaning the contents of Ranges in workbooks without
opening and closing the many workbook/worksheets which takes an awfully
long time to

Thanks for the suggestions
kurb

 

[Jamie Collins]

I retrieve/store lots of data between multiple
workbooks/worksheets/columns and Arrays . Is there an efficient way of
accessing and chaning the contents of Ranges in workbooks without
opening and closing the many workbook/worksheets which takes an awfully
long time

Yes, ADO is generally much faster than opening a workbook.

What's a good reference book (for an eternal novice programmer) for
using ADO with Excel

I don't do 'paper <g> but you'll find lots of resources and examples
online e.g. MSDN:

How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q257819

(see additional links at the bottom of the article).

Also, search the Usernet archive e.g.

http://groups.google.com/groups?q=a...meta=group=microsoft.public.excel.programming

Jamie.

--

 

[kurb]

Thank you very much for the references.

In addition to transferring data back and forth between multiple
workbooks and the Array, I also need to manipulate the worksheets via
VBA e.g insert rows, copy and paste formulas. Is this possible (and
safe to do) via ADO?

kurb