How To Store A Worksheet Data Into An Array
How To Store A Worksheet Data Into An Array
(OP)
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.
RE: How To Store A Worksheet Data Into An Array
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.
RE: How To Store A Worksheet Data Into An Array
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
RE: How To Store A Worksheet Data Into An Array
DataSheet
A B C D
1 BeamType Hgt Wgt I
2 WF18 # # #
3 etc
Get info for a specific beam
'<><><><><><><><><><><><><><><><><><>
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:
'<><><><><><><><><><><><><><><><><><>
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
'<><><><><><><><><><><><><><><><><><>
I did not test this code, but I think that it looks OK.
Hope this helps!
RE: How To Store A Worksheet Data Into An Array
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.
RE: How To Store A Worksheet Data Into An Array
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
RE: How To Store A Worksheet Data Into An Array
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!
RE: How To Store A Worksheet Data Into An Array
RE: How To Store A Worksheet Data Into An Array
RE: How To Store A Worksheet Data Into An Array
Thanks for the response fellows!
RE: How To Store A Worksheet Data Into An Array
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.
RE: How To Store A Worksheet Data Into An Array
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
RE: How To Store A Worksheet Data Into An Array
You can buy the data for US steel shapes in Excel format from AISC (www.aisc.org). This will save you a lot of time typing and error checking.
Imagineer
RE: How To Store A Worksheet Data Into An Array
RE: How To Store A Worksheet Data Into An Array