MS Excel VBA experts ?

[JesperMP]

Hi.

I have a task of creating checklists for a plant that contain many standard items.
My idea is to do this in Excel, whereby in one sheet I define the "types of devices" something like this:

TypeIdentifier Device description            Checks required
01_pump      Pump 200 liter per hour       On/off, leakage, label
02_pump      Pump 500 liter per hour       On/off, leakage, label
03_pump      Pump 1000 liter per hour      On/off, leakage, label
05_valve     Valve DN20                    On/off, leakage, label
06_valve     Valve DN40                    On/off, leakage, label
10_conv      Conveyor 20 tons per hour     On/off, guards in place, E-stop
11_conv      Conveyor 40 tons per hour     On/off, guards in place, E-stop

In another sheet I will have the actually used devices listed. Something like this:

Device name TypeIdentifier Device description
M20              01_pump      Main pump
M21              01_pump      Refill pump
M22              02_pump      Backup pump
M30              05_valve     Fill valve
M31              05_valve     Empty valve
M32              06_valve     Clean valve
W11              10_conv      Material Transport

Based on the common TypeIdentifier, I want to generate yet another sheet with the checks needed with each line one check, something like this:

Device name   Device description Checks required
M20               Main Pump            on/off        
M20               Main Pump            leakage 
M20               Main Pump            label 
M21               Refill Pump          on/off        
M21               Refill Pump          leakage 
M21               Refill Pump          label 
M30               Fill valve           on/off
M30               Fill valve           leakage
M30               Fill valve           label
W11               Material Transport   on/off
W11               Material Transport   guards in place
W11               Material Transport   E-stop

I have managed to get almost there with using VLOOKUP and INDEX MATCH functions. But working with individual cells is really clunky and time consuming.
Therefore to the question .. any Excel macro guru has something similar, or good hints on how to achieve the above. I have googled a lot, and found plenty of advice, but I dont have the energy to learn another language, and another programming environment, and all the quirks that go with it. So please....

Btw, I had problems getting anything to work with the VLOOKUP and INDEX MATCH until I decided to add a unique number to beginning of the TypeIdentifier, and presto it worked - after hours of trying stuff and searching on the internet for explanations in vain.

 

[drbitboy]

not what you are specifically asking for, but I think this would enable the same thing: doesn't odbc or something like that have a driver that treats a spreadsheet (or csv) as a database table, and then sql queries can be run against it?

OT rant:

Oh dear, I hate when eXcel is the solution; it's a solution in search of a problem; when all you have/know is eXcel, everything looks like a spreadsheet.

There is a research institution that has a finite element winding model e.g. for winding paper onto a roll. the model is implemented in eXcel.

 

[JesperMP]

I am definitely not going to da anything with ODBC or SQL. The idea is that it is a document that anyone can open up and work with. Hence the idea to use Excel.

I agree that Excel is highly unsatisfactory. But keeping it as simple as possible, it ought to be possible to get something working.

No takers .. ?

 

[drbitboy]

Maybe it will help, maybe it won't, but here is a link to a .ZIP archive that contains an old workbook I made some years ago: https://github.com/brandon-rhodes/pyephem/files/1731971/Horizon***cel.zip (I am surprised I found it; I don't have a copy and thought it was lost).

That workbook, Horizon***cel/201802_Horizons_Beta.xls, does read data from cells, then mung those data, and finally write those munged data to other cells. I suspect that reading and writing cells will be the messiest parts of what you want to do in VBA; hopefully I bothered to include comments in the code. The meat of what OP wants to do, comparing members of elements of one set of cells (array) against another, should not be very complicated.

In that workbook, that "meat" is connecting to the Horizons telnet interface at the Jet Propulsion Library using sockets to obtain the ephemerides (positions vs. time) of a solar system body.

I don't have eXcel any more, so I cannot offer any practical support, although if you post some code (I can't even look at the VBA in that workbook), I could probably make sarcastic, but helpful, comments. I don't know if that workbook would even work any more; it is .xls not .xlsx.

TL;DR

Not everyone has Office/eXcel (gasp!).

If we are assuming Windows 10, then everyone has access to WSL (Windows System for Linux), and this exercise is trivial in that environment (gawk, Python, etc., even BASH).

Outside of WSL and Office/eXcel, almost everyone has a web browser, so this could probably be done with Javascript.

I am not saying eXcel/VBA won't work, just that getting VBA to read and modify eXcel data is ugly, and in that context not much different from ODBC, and there are alternatives.

 

[drbitboy]

I am definitely not going to da anything with ODBC or SQL. The idea is that it is a document that anyone can open up and work with. Hence the idea to use Excel.

I don't think you understood my suggestion.

The data would reside within the eXcel document only, so it would be consistent with your idea that i"it is a document that anyone (with eXcel) can open up and work with," which is why I suggested it.

Specifically it would not involve a separate SQL server. or SQL database, or any ODBC driver, or any SQL functionality, that is not already native to that document and VBA/eXcel.

 

[JesperMP]

Hah ! I managed to get it to work without too much trouble.

One important hurdle, was that the Type Designator must be in cells formatted as texts, but the content must be a number i.e. "02" not a text "pump" only or a number plus text "02_pump". Only then would the search for the type designator work reliably.

Here is the script code if anyone is curious

Sub Generate_checklist()

Dim TypeSheet As Worksheet, ItemSheet As Worksheet, ChecklistSheet As Worksheet
Dim valueToSearch As String
Dim i As Integer, j As Integer, k As Integer

Set TypeSheet = Worksheets("TYPES")
Set ItemSheet = Worksheets("ITEMS")
Set ChecklistSheet = Worksheets("CHECKLIST")

'get the number of the last row with data in TYPES and ITEMS
lastRowTypesheet = TypeSheet.Cells(Rows.Count, "A").End(xlUp).Row
lastRowItemsheet = ItemSheet.Cells(Rows.Count, "A").End(xlUp).Row

ChecklistRowcount = 1
'for every value in column A ("Item Type") of ITEMS
For i = 2 To lastRowItemsheet
     'get the Item Type from ITEMS
     valueToSearch = ItemSheet.Cells(i, 1)
     'for every value in column A ("Type designator") of TYPES
     For j = 2 To lastRowTypesheet
       'Test against the value in column A ("Type designator") of TYPES
        If TypeSheet.Cells(j, 1) = valueToSearch Then
            'if found a match, proceed to create rows of checklist data
            lastColumnTypesheet = TypeSheet.Cells(j, Columns.Count).End(xlToLeft).Column
            For k = 1 To (lastColumnTypesheet - 3)
                ChecklistRowcount = ChecklistRowcount + 1
                'Column 1 is "Item ID" from ITEMS column 2
                ChecklistSheet.Cells(ChecklistRowcount, 1).Value = ItemSheet.Cells(i, 2).Value
                'Column 2 is "decription" from ITEMS column 3
                ChecklistSheet.Cells(ChecklistRowcount, 2).Value = ItemSheet.Cells(i, 3).Value
                'Column 3 is "Check" that is fetched from TYPES column 4 and onwards
                ChecklistSheet.Cells(ChecklistRowcount, 3).Value = TypeSheet.Cells(j, 3 + k).Value
                'Uncomment to output Debug iinfo in columns D and E
                'ChecklistSheet.Cells(ChecklistRowcount, 4).Value = valueToSearch
                'ChecklistSheet.Cells(ChecklistRowcount, 5).Value = lastColumnTypesheet
            Next k
            Exit For
        End If
     Next j
Next i

End Sub

Not bad for my first go at VBA in Excel !
Feel free to criticise. But do understand that before yesterday I hadnt written a line of VBA ever.
Now I only need to add scripts to merge identical cells to clean it up to look nice.

 

[JesperMP]

I appreciate the comments, and yes Excel isnt the non-plus-ultra, but everyone has Excel in my organisation, and I needed to get something down quick-and-dirty.

 

[ndzied1]

This is a site I have found has a lot of good Excel Information
https://chandoo.org/wp/

 

[drbitboy]

well done!

 

[JesperMP]

A screenshot of the final product.
Imagine there are hundreds of items, each has 10-20 necessary checks.
The time saved not having to do this manually !!

 

[Remrun]

I appreciate the comments, and yes Excel isnt the non-plus-ultra, but everyone has Excel in my organisation, and I needed to get something down quick-and-dirty.

+1 Jesper.
That's one of the biggest reasons I use VBA. If I build something at work everybody here has Excel. So no mucking around trying to get whatever "cool and more efficient" language to run on the other guys machine. Then of course there is dealing with IT to allow whatever you wrote to run..
At the end of the day Excel generally gets the job done.

 

[JesperMP]

VBA scares me a bit though.
Why does this line
TypeSheet.Cells(j, 1) = valueToSearch
not retun TRUE when both
TypeSheet.Cells(j, 1) and valueToSearch contain a "Conveyor_2", but do return true when they both contain a "02" ?

When I dabbled with the VLOOKUP and INDEX MATCH I also got differing results for
"Conv", "Conv_1", "1Conv", and "1". The results were all different, and no matter what these two functions didnt reliably find matches, even if the cell contents definitely where identical.
Makes me not want to risk me career on Excel.