ExcelBanter - How to use Names in VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - How to use Names in VBA (https://www.excelbanter.com/excel-programming/313101-how-use-names-vba.html)

How to use Names in VBA

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

How to use Names in VBA

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

--
Regards,
Tom Ogilvy

"kurb" wrote in message
. ..

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

How to use Names in VBA

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 wrote:

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

How to use Names in VBA

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.

--
Regards,
Tom Ogilvy

"kurb" wrote in message
. ..
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 wrote:

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

How to use Names in VBA

"Tom Ogilvy" wrote ...

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

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

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

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.

--

How to use Names in VBA

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 wrote:

"Tom Ogilvy" wrote ...

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

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

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

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.

--

How to use Names in VBA

kurb wrote ...

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...;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=ad...el.programming

Jamie.

--

Manipulate worksheets via ADO

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

Jamie Collins wrote:

kurb wrote ...

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...;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=ad...el.programming

Jamie.

--