Excel VBA UDFs for QUEST BCL (wow, enough acronyms?)

If you have any familiarity with Excel VBA, you've probably made at least one User Defined Function (UDF) for use in an Excel spreadsheet. If you're not familiar with it, you basically just create a function within an Excel VBA standard module that may or may not take arguments, and returns something that can be displayed in Excel. For example, here is a simple function that just returns the sum of two arguments.

Function MySum(arg1 As Variant, arg2 As Variant)

MySum = arg1 + arg2

End Function

In this function, arg1 and arg2 are of Variant types, meaning the user is able to pass in a literal number or a selected Excel cell (called a Range). By defining variables as Variants, we can assume that whatever the user passes into the function can be cast into the variable type required by the operators we are using (in this case the addition/concatentation operator +).

What some people don't necessarily think of though, is to use VBA to create string concatenating UDFs. Here's an example that just puts two pieces of text together:

Function MyConcat(arg1 As Variant, arg2 As Variant)

MyConcat = CStr(arg1) & CStr(arg2)

End Function

You can see it's similar to MySum, except that we are using the & operator which is just for string concatenation in this use (where the + operator can be addition or concatenation). Also, we are making use of the built-in VBA function CStr to cast our variant data into strings. This is because the & operator requires strings as operands.

Now to the BCL part. We can use something similar to MyConcat to make it so we can easily create BCL commands by simply passing one or more arguments into specially created UDFs. Here's an example for creating a cycle process:

Public Function create_cycle_process(process_name As Variant) As String

create_cycle_process = "CREATE CYCLE PROCESS '" & process_name & "'"

End Function

So a user only has to pass in the name of the process they want to create, and VBA returns a properly formatted BCL command to execute in QUEST. This can be pretty handy.

Even better though, is that VBA allows optional arguments in UDFs, so we can easily account for the optional arguments in many BCL commands. A good example of this is the CREATE ELEMENT CLASS command, which requires that you provide the element class type and the element class name, but allows you to also specify the number of elements and the geometry file path to use for the class display. Without optional arguments you would have to write four different UDFs to be able to build a CREATE ELEMENT CLASS command with all the possible arguments. But with VBA we can do it one. Here's how:

Public Function create_element_class(class_name, elem_type, Optional num_elem, Optional geo_name)

Dim Result

Result = "CREATE " & elem_type & " CLASS '" & class_name & "'"

If Not IsMissing(num_elem) Then

Result = Result & " NUMBER OF ELEMENTS " & CStr(num_elem)

End If

If Not IsMissing(geo_name) Then

Result = Result & " GEO '" & geo_name & "'"

End If

create_element_class = Result

End Function

This function starts by creating the base BCL command using the required arguments, class_name and elem_type. Then, it uses the VBA function IsMissing to see if our optional arguments were omitted in the function call. If not, they are used to add to the BCL command. Finally, the BCL command is returned to the user. Now the user no longer needs to remember the proper syntax for creating an element class BCL command.

To make it even easier on the user, we can provide higher level functions that provide the proper elem_type for them, so they don't have to remember those either:

Function create_mach_class(class_name , Optional num_elem , Optional geo_name )

create_mach_class = create_element_class(class_name, "MACHINE", num_elem, geo_name)

End Function

Function create_source_class(class_name , Optional num_elem , Optional geo_name )

create_source_class = create_element_class(class_name, "SOURCE", num_elem, geo_name)

End Function

Function create_sink_class(class_name , Optional num_elem , Optional geo_name )

create_sink_class = create_element_class(class_name, "SINK", num_elem, geo_name)

End Function

Function create_buffer_class(class_name , Optional num_elem , Optional geo_name )

create_buffer_class = create_element_class(class_name, "BUFFER", num_elem, geo_name)

End Function

Function create_conveyor_class(class_name , Optional num_elem , Optional geo_name )

create_conveyor_class = create_element_class(class_name, "CONVEYOR", num_elem, geo_name)

End Function

Function create_accessory_class(class_name , Optional num_elem , Optional geo_name )

create_accessory_class = create_element_class(class_name, "ACCESSORY", num_elem, geo_name)

End Function

Function create_agv_controller_class(class_name , Optional num_elem , Optional geo_name )

create_agv_controller_class = create_element_class(class_name, "AGV_CONTROLLER", num_elem, geo_name)

End Function

Function create_labor_controller_class(class_name , Optional num_elem , Optional geo_name )

create_labor_controller_class = create_element_class(class_name, "LABOR_CONTROLLER", num_elem, geo_name)

End Function

Function create_carrier_class(class_name , Optional num_elem , Optional geo_name )

create_carrier_class = create_element_class(class_name, "CARRIER", num_elem, geo_name)

End Function

Function create_agv_class(class_name , Optional num_elem , Optional geo_name )

create_agv_class = create_element_class(class_name, "AGV", num_elem, geo_name)

End Function

Function create_labor_class(class_name , Optional num_elem , Optional geo_name )

create_labor_class = create_element_class(class_name, "LABOR", num_elem, geo_name)

End Function

Function create_path_system_class(class_name , Optional num_elem , Optional geo_name )

create_path_system_class = create_element_class(class_name, "PATH SYSTEM", num_elem, geo_name)

End Function

Hopefully this has given you a good primer on using VBA UDFs to create QUEST BCL commands in Excel, so you can smarter BCL scripts that will be easier to modify and to execute in QUEST.