Pre-commissioning tasks often involve identifying which check-sheet form corresponds to specific work scopes. Fortunately, this can be streamlined by extracting loop data from SPI (SmartPlant Instrumentation). However, manually populating this information can be laborious. In this guide, we’ll explore how to set up a VBA macro to automate this process, making your life easier.
To begin, we require two essential components: a lookup master list or register containing all assigned form check-sheets to tag numbers (referred to as “MASTER_CS_REGISTER” for this post), and SPI extracted data. Initially, we ensure that the MASTER_CS_REGISTER is assigned to the loop, a one-time process. Once confirmed, all tags associated with the loop name, as per SPI, are established. With this groundwork laid, we can proceed to develop our VBA code.
Crafting the VBA code is a straightforward endeavor. We start by specifying the source worksheet and the loop for which we aim to generate the checklist. Subsequently, the code checks if a worksheet for the generated tags already exists; if it does, the code deletes it. Next, we iterate through the selected range, searching for every loop name defined in the MASTER_CS_REGISTER. Named ranges, such as “ALL_QVD,” streamline this process. Upon locating a match, the code gathers the required data horizontally by iterating through the columns. Below is the complete code snippet (you can freely copy and paste it, all comments is self-explanatory):
Before proceeding, ensure that developer mode is activated in Microsoft Excel using the following steps (see snapshot below).
Should you encounter any queries along the way, feel free to reach out.
Leave a Reply