How To Store A Worksheet Data Into An Array 2

[IJR]

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.

 

[dsi]

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.

 

[IJR]

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

 

[dsi]

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.

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(&quot;BeamData&quot;)
    
    iRow = 2    'first row of data
    Do While Len(dataSheet.Range(&quot;A&quot; &amp; iRow).Text) > 0
        If dataSheet.Range(&quot;A&quot; &amp; iRow).Text Like sType Then
            rBeamHgt = CSng(dataSheet.Range(&quot;B&quot; &amp; iRow).Text)
            rBeamWPF = CSng(dataSheet.Range(&quot;C&quot; &amp; iRow).Text)
            rI = CSng(dataSheet.Range(&quot;D&quot; &amp; iRow).Text)
        End If
    
        iRow = iRow + 1
    Loop
    
    'Warn user if not found
    If rBeamHgt <= 0.01 Then
        MsgBox &quot;Could not find selected beam: &quot; &amp; sType
    End If
    
    'Clean up
    Set dataSheet = Nothing
End Sub

Then, just fill in the global variables with a simple call:

Call GetBeamInfo(&quot;WF18&quot;)
'<><><><><><><><><><><><><><><><><><>

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(&quot;BeamData&quot;)
    
    ReDim BeamData(1)
    
    iRow = 2    'first row of data
    iIdx = 1    'first array index
    
    Do While Len(dataSheet.Range(&quot;A&quot; &amp; iRow).Text) > 0
        ReDim Preserve BeamData(iIdx)
            
        BeamData(iIdx).Desc = dataSheet.Range(&quot;A&quot; &amp; iRow).Text
        BeamData(iIdx).Hgt = CSng(dataSheet.Range(&quot;B&quot; &amp; iRow).Text)
        BeamData(iIdx).WPF = CSng(dataSheet.Range(&quot;C&quot; &amp; iRow).Text)
        BeamData(iIdx).Moment = CSng(dataSheet.Range(&quot;D&quot; &amp; 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!

 

[dsi]

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.

 

[Qshake]

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

 

[dsi]

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!

 

[IJR]

Thanks dsi for it all and thanks Qshake for joining. Nothing seems more fun to me than sharing info.

 

[IJR]

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)

 

[Qshake]

I have found the text Excel 97 Annoyances from O'Reily Publishing to be useful but have yet to have some time to sit down and get into it. Can't wait though, its like a new world opening up.

Thanks for the response fellows!

 

[IJR]

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.

 

[Imagineer]

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

 

[Imagineer]

Also,
You can buy the data for US steel shapes in Excel format from AISC (

http://www.aisc.org).

This will save you a lot of time typing and error checking.



Imagineer

 

[Qshake]

I use many of the functions quite a bit but the idea of programing and automating is extremely appealing!

 

[IJR]

Thanks Imageneer.