The purpose of RAM DataAccess (RAM DA) is to allow you to read, add, and delete data in the RAM Structural System (RAM SS) database. This is accomplished through the use of the COM component, RAMDataAccess.dll. This file installs with RAM SS and its information is registered on your computer. A Microsoft COM (Component Object Model) component aids in software communication. The RAMDataAccess.dll component contains functions and a way for an application to call the functions (interfaces). The great thing about COM is that the programming language you use for your applications does not need to be the same programming language that the component was written in. Hence, we can use DA with VBA, C++, or any other programming language that supports COM.
Installing RAM DA
As stated previously, the RAMDataAccess.dll file installs with RAM SS. The version of the .dll file is specific to the version of RAM SS that is installed. Therefore, in order to use RAM DA, you must have RAM SS installed on the computer and the model that is being accessed must be the same version. It is good practice to open the model in RAM SS and run all of the various modules. This ensures the model will be converted to the version that is installed on the computer and that all results are available through RAM DA.
Setting up Your VBA Project in Excel
If you create a new Excel workbook, there are a few things that you need to setup so that DA can run in the application.
Make sure the ‘Developer’ tab is visible. If it is not shown, click on the Office button in the upper left hand corner of Excel. Click on the ‘Excel Options’ button and select the ‘Popular’options. Then, select ‘Display Developer Tab in the Ribbon’ and click OK.
Update your security settings. Open Excel and go to Developer – Macro Security. In the ‘Trusted Locations’ review/add the appropriate path of where the Excel file is or will be saved. This is critical or the macros will be probably be disabled when the file is opened. Under ‘Macro Settings’, select ‘Trust access to the VBA project object model.’ Please note that this is an Excel setting and is not a setting tied to the file. If you are running the DA program on another computer, that computer must have acceptable security settings as well.
When you are in the Visual Basic Editor, you need to add references to DA library. Open Excel and go to Developer – Visual Basic. Once you are in the editor, go to Tools – References and select ‘RAMDataAccess 2.0 Type Library’ from the ‘References – VBAProject’ dialog box. Adding this reference to your application creates a library of the functions and constants that are available in RAM DA. If you hit F2 on your keyboard, the Visual Basic Editor will bring up your libraries.
Creating a Procedure in a New Module
Within the Visual Basic Editor, create a new module in the VBA Project by going to Insert – Module. By creating a new module in our project, we will be able to execute our program from Excel. Next, create a Sub Procedure in the module, by going to Insert – Procedure. A Sub Procedure is a named sequence of statements that will be executed as a unit. We would use a Function Procedure if we wanted to return a value. The scope of the procedure (Public or Private) defines the accessibility. For a simple program like we are creating, it is not necessary to define the scope of the procedure. Once the module and procedure have been created, you can enter your code.
Interfaces are COM objects that allow you to access a group of related methods for an entity. A method is a routine that gets or sets information in the interface. When DataAccess was originally created, its main purpose was to retrieve information from the Structural System database. These interfaces are referred to as the “Original” or “Old” DA interfaces. These interfaces have broad groups of methods for information like model data, structure geometry, and member forces. This structure does not work well when the data needs to be modified. So, a new set of interfaces referred to as the “Object Model” or “New” interfaces were created. These interfaces group methods for specific Structural System entities like a floor type, beam, or deck. There’s no reason why you couldn’t create an entire program using only the old interfaces if you were only interested in getting information from the database. However, it is encouraged that you use the new object model interfaces as much as possible because the old interfaces will not be updated and eventually may be replaced.
You create an instance of DA by defining an object variable and setting a new reference to the main DA interface (RAMDataAccess1) in the COM object.
Dim RAMDataAcc1 As RAMDATAACCESSLib.RamDataAccess1
Set RAMDataAcc1 = New RAMDATAACCESSLib.RamDataAccess1
Now, with the main interface established, we can access the next interface, either an old DA interface or the main object model interface.
Using the Old DA Interfaces
Using the old interfaces is fairly straight forward. First, you define an object variable. Then, you set the object variable equal to an interface pointer from the main DA interface (RAMDataAcc1).
Dim RAMDataAccIDBIO As RAMDATAACCESSLib.IDBIO
Set RAMDataAccIDBIO = RAMDataAcc1.GetDispInterfacePointerByEnum(IDBIO1_INT)
Now, with the interface established, you can access its methods and data. The above interface, RAMDataAccIDBIO, contains several methods (functions and sub routines) needed to open, close, save, create, etc. a RAM SS database. For example, if we now want to load a RAM SS database, we could call the LoadDataBase method from the interface.
The LoadDataBase method expects a string variable (OpenFile) that represents the database name. All of the other old interfaces have a similar structure to that described above. Data is always returned by calling methods from an interface.
Using the New Object Model Interfaces
The object model interfaces are structured similarly to the structure in RAM SS. For example, to model a beam in RAM SS, you first need be in a model and have a floor type defined. Then you can go to Layout – Beams and add a beam. The structure of the object model interfaces are similar in that it is a “layered” process. First, you need to reference the main object model interface, then the floor type interface, and then the beams interface, before you can add a new beam to the collection of beams. Data that you want to retrieve from the database is typically stored as a property of the object in the object model interfaces. This differs from the old interfaces in that you don’t directly call a method to retrieve a particular piece of information. For example, if you wanted to know the size of a particular beam using the old DA interfaces, you would call the GetMemberSize method from the IMemberData1 interface, where the unique member ID number (lMemberID) is supplied to the function and the size of the member (pbstrSize) is returned.
Dim IMemberData1 As Object
Set IMemberData1 = RAMDataAcc1.GetDispInterfacePointerByEnum(IMemberData_INT) IMemberData1.GetMemberSize lMemberID, pbstrSize
Using the object model interfaces, a reference to a particular beam object in the IBeam interface would be set from the main object model interface by calling the GetBeam method for a unique member ID (lMemberID). Then, you can assign the value of the strSectionLabel property to a variable that you defined (strSize).
Dim IModel as RAMDATAACCESSLib.IModel
Set IModel = RAMDataAcc1.GetDispInterfacePointerByEnum(IModel_INT)
Dim IBeam as RAMDATAACCESSLib.IBeam
Set IBeam = IModel.GetBeam(lMemberID)
strSize = IBeam.strSectionLabel
A Practical Example
You may or may not know that within RAM SS you have the ability to customize the master steel table and the column/beam design tables. One use for manipulating the tables is to reduce the number of steel sections that are selected from during the optimization process to gain economy through repetition. With RAM DA, you could create a program to “find and replace” particular sizes that are assigned in the model. Instead of using the beam/column design tables to pare down the sections available during and optimization, it might be easier to have an extensive list of available shapes to see which sizes occur frequently during the optimization and then replace the uncommon shapes with a heavier size that is used more often in the model. The following program can be used to replace steel shapes in a model for beams, columns, horizontal braces, and/or vertical braces.
Microsoft Excel is used as an input table for the Visual Basic for Applications program. The four check boxes on the left allow the user to select which members types should be changed. Once the “Find and Replace” button is clicked, the program will ask the user to open a RAM SS model and all instances for the desired member type with the assigned size in the “Find” cell will be replaced with the size in the “Replace With” cell.
A module and sub procedure were created as described previously. The declaration section exists at the module level outside of our sub procedure. Although it is not necessary to have a declaration section, it can be useful to define variables that appear in multiple procedures within the module. If you include the statement “Option Explicit” in the declaration sections, then all variables must be defined before you use them in a procedure. It is a great way to ensure that you have not misspelled a variable name. Prior to creating the module, if you had toggled the “Require Variable Declaration” from Tool – Options, the “Option Explicit” statement would have automatically appeared in the declarations section.
Since we have to define every variable in our procedure, it is not a bad idea to define all of your variables right away in groups based on the data type. A data type is the characteristic of a variable that determines what kind data of it can hold. There are several data types such as object, long, double, and string. The “Dim” statement is used to declare a variable as a particular data type and allocate storage space. By defining our interface objects (i.e. IModel) with specific references to RAMDATAACCESSLib instead of a generic “Object”, we force early binding. The main advantage in declaring a variable this way is that when you write code to access methods or properties of the interfaces, you’ll be able to see what methods/properties are available for the interface. For example, once the period is typed after IVerticalBrace, the menu pops up and the appropriate property can be selected. Had I simply defined IVerticalBrace as an “Object” instead of a specific reference to RAMDATAACCESSLib.IVerticalBrace, a menu would not have popped up.
After the variables have been declared, we start to get into the meat of our code. This portion of the code clears old data in the Excel worksheet, sets variables equal to the values (true is toggled) of the worksheet check boxes, and saves the worksheet find and replace shapes as strings.
Next, we can open our RAM SS model. Prior to opening the model in DA, you will want your Structural System modules to be analyzed and/or designed so that all of the appropriate member data and forces are available. Also, you need to be out of the model when accessing it through DA. Although the model path can be hard coded, it is convenient to use the VBA GetOpenFilename function to select the model through a graphical user interface. This function will return a string for the file name that includes the path. Then, we can initialize DA and load the database through the IDBIO interface. Finally, we initialize the main object model interface.
In this program, the user inputs the find and replace steel shape name. As a precaution, it is probably a good idea to verify that the sections exist in the model master steel table. To accomplish this, we will need to locate the master steel table and read data from it. Starting in v14.06, the master steel table is saved in the .rss file. This file will have a .tab extension and can be accessed from the working directory when the model is open. Therefore, we need to construct a path using the IDBIO interface to retrieve the Working directory and the IModelData1 interface to get the model name without the path where the model is saved. VBA recongnizes file system objects that can be used to read from a text file. First, we use the Create Object function to return a FileSystemObject. Then, the OpenTextFile method is used to create our master steel table as a TextStream object. The code loops through each line of the TextStream and uses the VBA Split function with a space delimiter to split a line of text into an array of strings. The master steel table is formatted such that the section label will always be the first element in the array. By default, the first element of an array is index 0 in VBA. If the first element in the array matches the find or replace section, a value is added to the counter. If both sections are not found, the value of the counter is used to determine which error message is displayed and the program will terminate.
The intent of this program was to find and replace all steel shapes for the desired member type(s). Therefore, it is necessary to look at every applicable member on each story and see if the size matches the “Find” size. First, we determine the number of stories in the model so we can loop through each story. The “GetStories” method of the IModel interface returns a pointer to an IStories collection interface that represents all of the stories in the model. A collection is simply a group of objects and a collection interface is simply a way to pass around the collection. The IStories collection interface has a property “GetCount” that is equal to the number stories in the model. Once we set a variable equal to this property, we can then create a “For” loop that steps through each story. It is important to understand that the stories are numbered starting with zero. Therefore, our loop should run from zero to the total number of stories minus 1.
The IStory interface represents a single story in the model. To get at the particular story we are interested in, we can use the “GetAt” method of the IStories collection interface. This method takes an index number and returns an interface for the story represented by that index. Then, we can get at the properties and methods for our specific story. For example, the “strLabel” property returns the story label and the “GetBeams” method returns a pointer to an IBeams collection interface. The number of objects in the collection can then be reduced by using the “Filter” function in the IBeams collection interface. Currently, you can filter for material or frame type. In this instance, the function is used to eliminate beams in the collection that are not a steel material type. Similarly, the columns, horizontal braces, and vertical braces collection interfaces can all be returned from the IStory interface and filtered for steel material types.
Now that we have the filtered collections, we can look at each member, see if the size matches our “Find” criteria, and then change the size to match our “Replace” criteria. The block of code nested in the “If” statement will only be performed if the “Beam” was toggled in our input spread sheet. As with the IStories collection interface, the number of beams in the IBeams collection interface can be determined from the GetCount property. Then, we can look at each beam one by one in a “For” loop. With the IBeam interface set to a particular beam, we can retrieve the section label and member number. If the section label property matches the “Find” size, it will be updated to match the “Replace” size and the eAnalyze flag is set. The eAnalyze flag is analogous to freezing the design in RAM Steel Beam. Then, the story label, member type, and member number, are stored in the appropriate cells in the output table. Although this is not necessary, it may be desired for record keeping. A similar process would take place for columns, horizontal braces, and vertical braces. Then, the program will go to the next story and repeat the whole process.
Finally, once the all of the changes have been made, the database can be saved and closed. By setting RAMDataAcc1 equal to “nothing”, the object reference is released from the memory. The End Sub statement signals the end of the routine.