I am going to put a big data in an Excel worksheet. And later on I am planning to retrieve whatever data I need using one reference, say a user-input bolt size. I know I could simply use vlookup and the like but since I want to use code I prefer to read the entire data into a 2 dimensional array. Any fast guide on this?. Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
How To Store A Worksheet Data Into An Array 2
- Thread starter IJR
- Start date
-
2
- #2
This depends on the type of info you are storing. It sounds like you have 2 columns of data. What do they contain? Text? Numbers? ...
I don't think that you want to import an entire list so that the user can select one from the array for use. That is not a very efficient method, seeing as how you can easily cross reference the data within the sheet.
If you can be a little more specific about your data and your goals, I am sure that we can come up with a solution.
I don't think that you want to import an entire list so that the user can select one from the array for use. That is not a very efficient method, seeing as how you can easily cross reference the data within the sheet.
If you can be a little more specific about your data and your goals, I am sure that we can come up with a solution.
- Thread starter
- #3
Thanks dsi
I have a table of about 50 rows and roughly 10columns. The first column will contain name for a steel profile, and the other columns will contain various properties, say unit weight, moment of inertia. I will use the properties randomly in my other worksheets and I figured out that if I can store the data in an array lik A(i,j) then i could use i for profile name and j for the property I desire, for example example if user selects wide flange beam WF18 and I a want the unit weight for it, I can assign, say, i=5 for WF18 and j=9 for unit weight and retrieve off A(5,9)=unit wt. I may even use 3 dimensional array etc.
If you know of a better way please comment
Additional thanks
I have a table of about 50 rows and roughly 10columns. The first column will contain name for a steel profile, and the other columns will contain various properties, say unit weight, moment of inertia. I will use the properties randomly in my other worksheets and I figured out that if I can store the data in an array lik A(i,j) then i could use i for profile name and j for the property I desire, for example example if user selects wide flange beam WF18 and I a want the unit weight for it, I can assign, say, i=5 for WF18 and j=9 for unit weight and retrieve off A(5,9)=unit wt. I may even use 3 dimensional array etc.
If you know of a better way please comment
Additional thanks
Note that 10 columns of data will translate into a 10 dimensional array! Here are a couple of examples of what you can do to get the data.
Get info for a specific beam
Or, if you really want to import all of the data into variables:
I did not test this code, but I think that it looks OK.
Hope this helps!
Code:
DataSheet
A B C D
1 BeamType Hgt Wgt I
2 WF18 # # #
3 etc
Code:
'<><><><><><><><><><><><><><><><><><>
Option Explicit
Public rBeamHgt As Single
Public rBeamWPF As Single
Public rI As Single
Sub GetBeamInfo(sType As String)
Dim dataSheet As Worksheet
Dim iRow As Long
'Reset values
rBeamHgt = 0
rBeamWPF = 0
rI = 0
'Beam data on sheet called BeamData
Set dataSheet = Sheets("BeamData")
iRow = 2 'first row of data
Do While Len(dataSheet.Range("A" & iRow).Text) > 0
If dataSheet.Range("A" & iRow).Text Like sType Then
rBeamHgt = CSng(dataSheet.Range("B" & iRow).Text)
rBeamWPF = CSng(dataSheet.Range("C" & iRow).Text)
rI = CSng(dataSheet.Range("D" & iRow).Text)
End If
iRow = iRow + 1
Loop
'Warn user if not found
If rBeamHgt <= 0.01 Then
MsgBox "Could not find selected beam: " & sType
End If
'Clean up
Set dataSheet = Nothing
End Sub
Then, just fill in the global variables with a simple call:
Call GetBeamInfo("WF18")
'<><><><><><><><><><><><><><><><><><>Or, if you really want to import all of the data into variables:
Code:
'<><><><><><><><><><><><><><><><><><>
Option Explicit
Option Base 1
Public Type tBeamData
Desc As String
Hgt As Single
WPF As Single
Moment As Single
End Type
Public BeamData() As tBeamData
Sub LoadBeamInfo()
Dim dataSheet As Worksheet
Dim iRow As Long
Dim iIdx As Long
'Beam data on sheet called BeamData
Set dataSheet = Sheets("BeamData")
ReDim BeamData(1)
iRow = 2 'first row of data
iIdx = 1 'first array index
Do While Len(dataSheet.Range("A" & iRow).Text) > 0
ReDim Preserve BeamData(iIdx)
BeamData(iIdx).Desc = dataSheet.Range("A" & iRow).Text
BeamData(iIdx).Hgt = CSng(dataSheet.Range("B" & iRow).Text)
BeamData(iIdx).WPF = CSng(dataSheet.Range("C" & iRow).Text)
BeamData(iIdx).Moment = CSng(dataSheet.Range("D" & iRow).Text)
iIdx = iIdx + 1
iRow = iRow + 1
Loop
'Clean up
Set dataSheet = Nothing
End Sub
'Samples of use:
'Load Data
Call LoadBeamInfo
'Use the weight per ft of the 4th beam
rBeamWgt = rBeamLen * BeamData(4).WPF
'<><><><><><><><><><><><><><><><><><>Hope this helps!
10 dimensional array? What was I thinking?
Unless you use a varient array, you can only put all of the info in that 2-D array if they are the same type. If column A stores a string representation of the beam description, you can not put numerical values in the remainder of that array. I would not suggest putting info of different types into arrays. This is not a good programming method.
Unless you use a varient array, you can only put all of the info in that 2-D array if they are the same type. If column A stores a string representation of the beam description, you can not put numerical values in the remainder of that array. I would not suggest putting info of different types into arrays. This is not a good programming method.
DSI,
Much of what you've written is very helpful but seems to eb beyond the scope of many of the texts available for spreadsheet usage. I've always tried to use the spreadsheet to its maximum (limited only by what available in text) but I'm curious as to where you've learned this programming and application. I would appreciate it very much if you would list the (or all) texts which have this information and you may want to consider writing a frequently asked question on this topic for this forum.
Thanks
Qshake
Much of what you've written is very helpful but seems to eb beyond the scope of many of the texts available for spreadsheet usage. I've always tried to use the spreadsheet to its maximum (limited only by what available in text) but I'm curious as to where you've learned this programming and application. I would appreciate it very much if you would list the (or all) texts which have this information and you may want to consider writing a frequently asked question on this topic for this forum.
Thanks
Qshake
Texts? Although some may be available, I don't have any books on the Excel VBA. I have just struggled through the object library for several years. I am sure that there are books out there that can show you the basics.
The best place to start is the Excel help files. Go to Help > Contents & Index. Near the bottom, you will find a topic called Microsoft Excel Visual Basic Reference. Open that topic and select Visual Basic Reference. There is your VBA help guide. It contains all you really need. I also think that Excel comes with some sample files, although they may not be geared towards a first time user.
I have been doing this for quite some time now, so I may inadvertently make it seem easy. VBA is an extremely powerful tool that is incorporated in many applications. For example, one application I wrote gets basic info from the draftsman, creates up to 22 completely detailed AutoCAD drawing, opens Excel to create an engineering spec sheet, and opens Word to create a complete instruction book. I have also written similar programs that creates 3D parts, assemblies and detail drawings in SolidWorks.
I will write up some FAQ's when I have some time. I would like to wait and see what types of common tasks people are looking for since there are so many ways to accomplish the same task.
Happy hunting!
The best place to start is the Excel help files. Go to Help > Contents & Index. Near the bottom, you will find a topic called Microsoft Excel Visual Basic Reference. Open that topic and select Visual Basic Reference. There is your VBA help guide. It contains all you really need. I also think that Excel comes with some sample files, although they may not be geared towards a first time user.
I have been doing this for quite some time now, so I may inadvertently make it seem easy. VBA is an extremely powerful tool that is incorporated in many applications. For example, one application I wrote gets basic info from the draftsman, creates up to 22 completely detailed AutoCAD drawing, opens Excel to create an engineering spec sheet, and opens Word to create a complete instruction book. I have also written similar programs that creates 3D parts, assemblies and detail drawings in SolidWorks.
I will write up some FAQ's when I have some time. I would like to wait and see what types of common tasks people are looking for since there are so many ways to accomplish the same task.
Happy hunting!
- Thread starter
- #8
- Thread starter
- #9
And Qshake, it will not harm if you try any Visual Basic book only to try and get a feel for objects, properties and methods. As dsi mentioned, the VBA help in Excel may not be very entertaining to a beginner with tight time schedule(in case you are a beginner of course)
- Thread starter
- #11
And Qshake here is something to warm you up. DSI has used subroutines but you can also use functions. Try this: Go to any cell and type =rootplus1(5), Excel will assume rootplus1 to be a function which uses argument 5 and returns value as rootplus1(5). Then go to Visual basic code area and under GENERAL type
function rootplus1(x as single)
dim rootplus1 as single
rootplus1=sqrt(x)+1
end sub
Its been long time since I used functions but this one will probably work. The principle is correct anyway.
function rootplus1(x as single)
dim rootplus1 as single
rootplus1=sqrt(x)+1
end sub
Its been long time since I used functions but this one will probably work. The principle is correct anyway.
You might also use the built-in lookup functions, i.e. lookup, hlookup, vlookup, match, etc. You can even store the data in a seperate workbook (say shapes.xls) and reference them in. Vlookup is the most commonly used function for your application. See Excel's help for usage.
I HIGHLY recommend that you load all of the help files onto your machine. If you are in a corporate environment, these usually don't get loaded. They are actually very good (although the quality has been going down since Excel 4.0). When all else fails, RFTM (Read The Fine
Manual). 
Imagineer
I HIGHLY recommend that you load all of the help files onto your machine. If you are in a corporate environment, these usually don't get loaded. They are actually very good (although the quality has been going down since Excel 4.0). When all else fails, RFTM (Read The Fine
Manual). Imagineer
Also,
You can buy the data for US steel shapes in Excel format from AISC ( This will save you a lot of time typing and error checking.
Imagineer
You can buy the data for US steel shapes in Excel format from AISC ( This will save you a lot of time typing and error checking.
Imagineer
- Thread starter
- #15
- Status
Similar threads
- Locked
- Question
- Locked
- Question
