Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Change names of files in a folder to match names in Excel Column | Excel Programming |