How to create a reusable array

marcusja2002

Board Regular
Joined
Apr 27, 2010
Messages
107
I have a combo box that will change depending on the choice made in another combo box.

I have multiple list arrays already created. How do I name them, so I dont have to copy and past the array lists for each combobox set?

Here is the code: i want to turn those arrays into ideally a single word I can put in its place that VBA will recognize.

Private Sub cmbxfs1_Click()
Dim x As Integer


x = cmbxfs1.ListIndex


Select Case x
Case Is = 0
cmbxfd1.List = Array("CHECK VALVE STAMPED ARROW WRONG", "DID NOT SPRING CHANGE", "FUEL GOVENOR SPRINGS DEFECTIVE", "FUEL INJECTION PUMP NOT SUPPLYING FUEL", "FUEL LEAK AT INJECTION PUMP", "FUEL LEAKING AT FITTING ON BLOCK", "FUEL LINE BLEW OFF, CAUGHT FIRE IN DYNO", "FUEL LINES LOOSE, FUEL LEAK", "FUEL PUMP FAILURE", "FUEL RAIL SENSOR FAILED", "FUEL SOLENOID FAILED IN GOVENOR", "INJECTION PUMP FAILURE", "INJECTOR LEAKING FUEL", "INJECTOR LEAKING OIL", "RESET GOVENOR SETTING", "SILVER SOLDER HAD HOLE IN FUEL LINE", "SPRING CHANGE DONE INCORRECTLY", "SPRING CHANGE DONE INCORRECTLY - WOULD NOT SHUT DOWN", "SPRING CHANGE NOT DONE", "STOP LEVEER IN INJECTOR PUMP NOT SHUTING ENGINE DOWN", "WRONG SPRING INSTALLED")
Case Is = 1
cmbxfd1.List = Array("5-BLINKS ON PANEL, REPLACE", "ALTERNATOR EXCITOR WIRE DIODE BACKWARDS", "ALTERNATOR WAS NOT CHARGING", "ALTERNATOR WIRED INCORRECTLY, EXCITER WIRE ON WRONG POST", "AUX EXT ENGINE SHUT OFF CODE", "BATTERY CABLE HAD BROKEN WIRES ", "BATTERY ISOLATOR SHORTED OUT", "BATTERY ISOLATOR WIRED BACKWARDS", "CANNON PLUG ON VALVE COVER PULLED OUT", "CODES STUCK IN PANEL", "COOLING LOOP TEMP SENSOR OVER TIGHTENED", "DIODE BACKWARDS IN EXCITOR WIRE", "ECU HARNESS CODE", "ENGINE WOULD NOT STOP ON AUTO SIDE IN PANEL", "FUEL PRESSURE SWITCH FAILED", "GROUND CABLE CRUSHED BEHIND ECUS", "GROUND STUD AND GROUND CABLE NOT INSTALLED", "HARNESS BAD CRIMP", "HARNESS HAD A SHORT ", "HARNESS HAS PINS THAT WILL NOT LOCK IN ", _
"HARNESS MADE INCORRECTLY", "HARNESS NOT MAKING CONNECTION AT FUEL PUMP", "HIGH WATER TEMP CURCUIT BAD ON BOARD", "LIGHT IN PANEL FAILURE", "LOOP ALARM HARNESS AND BOARD WIRED INCORRECTLY", "LOOP ALARM SWITCH BAD", "LOW AND HIGH WATER TEMP WIRED INCORRECTLY IN PANEL", "LOW OIL PRESSURE STUCK ON IN PANEL", "LOW WATER TEMP SENORS WIRED WRONG IN PANEL", "MAG PICK-UP ADJUSTED WRONG FROM FACTORY", "MECAB FAILURE", "MECAB WIRED BACKWARDS", "MISSING CURCUIT BOARD", "MISSING GROUND BRACKET", _
"NO ECU DATA ON PANEL", "OIL PRESSURE SENSOR FAILED", "OIL SENDER WIRED BACKWARDS", "OVERSPEED LIGHTS FLASHING ON PANEL", "PANEL FAILURE (UNKNOWN)", "PANEL MISSING TERMINALS", "PANEL THROWING AN ERROR FOR INJECTOR", "PANEL WOULD NOT ALLOW ENGINE TO SHUTDOWN", "PANEL WOULD NOT SWITCH ECUS", "PANEL, RUN STOP WIRE ETR NOT ETS", "PINS PUSHED OUT AT ECU", "PINS PUSHED OUT AT SENSOR", "PINS PUSHED OUT OF HARNESS CONNECTORS", "PINS PUSHED OUT OF WIRING HARNESS", "PINS PUSHED OUT ON PANEL CONNECTOR", _
"PLATE IN PANEL LOOSE", "POWER BUTTONS STUCK IN", "POWER FAILED AT THE END OF TEST", "POWER VIEW BACKLIGHT FAILED", "PRI 32 HARNESS CODE", "RED 32 HARNESS CODE", "RED SIDE OF PANEL NOT COMMUNICATING", "SHUT OFF WIRE INCORRECT", "SOLENOIDS WIRED BACKWARDS", "STARTER WIRE NOT CONNECTED", "STARTER WIRES REVERSED", "STOP SOLENOID BAD, WOULD NOT STOP IN AUTO MODE", "TACH FAILURE", "TEMP SENSOR FAILED, PANEL LIGHTS WOULD NOT GO OUT", _
"TEMP SENSOR IN LOOP OVERTIGHTENED", "TERMINAL END CRIMPED ON BACKWARDS IN THE PLUG", "THERMOSTAT CRUSHED", "TRANSDUCER HARNESS HAD WIRES CROSSED", "WIRE PINCHED UNDER HOSE CLAMP CAUSING COOLANT LEAK", "WIRE PINCHED UNDER WASHER", "WIRES SWAPPED IN CANNON PLUG TO PANEL", "WRONG PANEL INSTALLED ON ENGINE")
Case Is = 3
cmbxfd1.List = Array()
Case Is = 4
cmbxfd1.List = Array()
Case Is = 5
cmbxfd1.List = Array()
Case Is = 6
cmbxfd1.List = Array()
End Select
End Sub
 

lhartono

Board Regular
Joined
Nov 15, 2017
Messages
103
You can create a Function and put it in Module.
There's also several ways to prepare an array, but if you have long list, if possible just put in worksheet (case 4).

Code:
[COLOR=#0000cd]Function getArray(paramIndex As Long)
Const arrSource As String = "Sheet2"

Select Case paramIndex
    Case 0
    getArray = Array([/COLOR][COLOR=#a9a9a9]"CHECK VALVE STAMPED ARROW WRONG", "DID NOT SPRING CHANGE", "FUEL GOVENOR SPRINGS DEFECTIVE", "FUEL INJECTION PUMP NOT SUPPLYING FUEL", "FUEL LEAK AT INJECTION PUMP", "FUEL LEAKING AT FITTING ON BLOCK", "FUEL LINE BLEW OFF, CAUGHT FIRE IN DYNO", "FUEL LINES LOOSE, FUEL LEAK", "FUEL PUMP FAILURE", "FUEL RAIL SENSOR FAILED", "FUEL SOLENOID FAILED IN GOVENOR", "INJECTION PUMP FAILURE", "INJECTOR LEAKING FUEL", "INJECTOR LEAKING OIL", "RESET GOVENOR SETTING", "SILVER SOLDER HAD HOLE IN FUEL LINE", "SPRING CHANGE DONE INCORRECTLY", "SPRING CHANGE DONE INCORRECTLY - WOULD NOT SHUT DOWN", "SPRING CHANGE NOT DONE", "STOP LEVEER IN INJECTOR PUMP NOT SHUTING ENGINE DOWN", "WRONG SPRING INSTALLED"[/COLOR][COLOR=#0000cd])
    Case 1
    getArray = Array([/COLOR][COLOR=#a9a9a9]"5-BLINKS ON PANEL, REPLACE", "ALTERNATOR EXCITOR WIRE DIODE BACKWARDS", "ALTERNATOR WAS NOT CHARGING", "ALTERNATOR          WIRED INCORRECTLY, EXCITER WIRE ON WRONG POST", "AUX EXT ENGINE SHUT OFF CODE", "BATTERY CABLE HAD BROKEN WIRES ", "BATTERY ISOLATOR SHORTED OUT", "BATTERY ISOLATOR WIRED BACKWARDS", "CANNON PLUG ON VALVE COVER PULLED OUT", "CODES STUCK IN PANEL", "COOLING LOOP TEMP SENSOR OVER TIGHTENED", "DIODE BACKWARDS IN EXCITOR WIRE", "ECU HARNESS CODE", "ENGINE WOULD NOT STOP ON AUTO SIDE IN PANEL", "FUEL PRESSURE SWITCH FAILED", "GROUND CABLE CRUSHED BEHIND ECUS", "GROUND STUD AND GROUND CABLE NOT INSTALLED", "HARNESS BAD CRIMP", "HARNESS HAD A SHORT ", "HARNESS HAS PINS THAT WILL NOT LOCK IN ", _
                     "HARNESS MADE INCORRECTLY", "HARNESS NOT MAKING CONNECTION AT FUEL PUMP", "HIGH WATER TEMP CURCUIT BAD ON BOARD", "LIGHT IN PANEL FAILURE", "LOOP ALARM HARNESS AND BOARD WIRED INCORRECTLY", "LOOP ALARM SWITCH BAD", "LOW AND HIGH WATER TEMP WIRED INCORRECTLY IN PANEL", "LOW OIL PRESSURE STUCK ON IN PANEL", "LOW WATER TEMP SENORS WIRED WRONG IN PANEL", "MAG PICK-UP ADJUSTED WRONG FROM FACTORY", "MECAB FAILURE", "MECAB WIRED BACKWARDS", "MISSING CURCUIT BOARD", "MISSING GROUND BRACKET", _
                     "NO ECU DATA ON PANEL", "OIL PRESSURE SENSOR FAILED", "OIL SENDER WIRED BACKWARDS", "OVERSPEED LIGHTS FLASHING ON PANEL", "PANEL FAILURE (UNKNOWN)", "PANEL MISSING TERMINALS", "PANEL THROWING AN ERROR FOR INJECTOR", "PANEL WOULD NOT ALLOW ENGINE TO SHUTDOWN", "PANEL WOULD NOT SWITCH ECUS", "PANEL, RUN STOP WIRE ETR NOT ETS", "PINS PUSHED OUT AT ECU", "PINS PUSHED OUT AT SENSOR", "PINS PUSHED OUT OF HARNESS CONNECTORS", "PINS PUSHED OUT OF WIRING HARNESS", "PINS PUSHED OUT ON PANEL CONNECTOR", _
                     "PLATE IN PANEL LOOSE", "POWER BUTTONS STUCK IN", "POWER FAILED AT THE END OF TEST", "POWER VIEW BACKLIGHT FAILED", "PRI 32 HARNESS CODE", "RED 32 HARNESS CODE", "RED SIDE OF PANEL NOT COMMUNICATING", "SHUT OFF WIRE INCORRECT", "SOLENOIDS WIRED BACKWARDS", "STARTER WIRE NOT CONNECTED", "STARTER WIRES REVERSED", "STOP SOLENOID BAD, WOULD NOT STOP IN AUTO MODE", "TACH FAILURE", "TEMP SENSOR FAILED, PANEL LIGHTS WOULD NOT GO OUT", _
                     "TEMP SENSOR IN LOOP OVERTIGHTENED", "TERMINAL END CRIMPED ON BACKWARDS IN THE PLUG", "THERMOSTAT CRUSHED", "TRANSDUCER HARNESS HAD WIRES CROSSED", "WIRE PINCHED UNDER HOSE CLAMP CAUSING COOLANT LEAK", "WIRE PINCHED UNDER WASHER", "WIRES SWAPPED IN CANNON PLUG TO PANEL", "WRONG PANEL INSTALLED ON ENGINE"[/COLOR][COLOR=#0000cd])
    Case 2

    Case 3
    getArray = Split([/COLOR][COLOR=#a9a9a9]"ASTON MARTIN,BMW,CHRYSLER[/COLOR][COLOR=#0000cd]", ",")
    Case 4
    getArray = (Sheets(arrSource).Range("A2:A" & Sheets(arrSource).Cells(Sheets(arrSource).Rows.Count, "A").End(xlUp).Row))
    Case 5
    
    Case 6
    
End Select

End Function

Private Sub cmbxfs1_Click()
Dim x As Integer

x = cmbxfs1.ListIndex

cmbxfd1.Clear

Select Case x
Case Is = 0
cmbxfd1.List = getArray(0)
Case Is = 1
cmbxfd1.List = getArray(1)
Case Is = 3
cmbxfd1.List = getArray(3)
Case Is = 4
cmbxfd1.List = getArray(4)
Case Is = 5
cmbxfd1.List = getArray(5)
Case Is = 6
cmbxfd1.List = getArray(6)
End Select

End Sub[/COLOR]
 
Last edited:

Forum statistics

Threads
1,077,784
Messages
5,336,327
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top