I'm going to apologize right now if this question is not formatted correctly as I'm new to this site.
A colleague wrote all the code and started the array to which I finished (the array) to create the UDF below to reduce the number of formulas that I was using before to find the material density of different metals and modify the formula as needed depending on the shape of the part in order to calculate the weight.
The problem I'm having is this UDF is used in multiple worksheets (45 sheets) within one workbook which all the worksheets total up to a summary tab. In order to get all the worksheet formula's to calculate properly I either have to double click on each individual cell to force it to recalculate or I highlight all the cells and go to Data>Text to Columns> and click Finish to force all the highlighted cells to recalculate.
I have tried to use shortcuts (i.e. Ctrl+ALT+Shift+F9 to force the UDF to recalculate) as well as Application.Volatile at the beginning of the code however they only recalculate one worksheet and not all of them. The sheets that it doesn't recalculate it shows #value! in the cell.
As I'm not the only one who uses this document this creates a headache for myself.
Here's the code. The colleague who wrote it put in some text to help me with troubleshooting it in the future.
' We want our function to do the following:
' 1) Create new function called "MATWEIGHT" and the output I want is a Double (a decimal number)
' 2) The inputs I want to bring into the function are "size" and "matl" which are a double and a string respectively.
Public Function MatType(matl As String) As Double
'Dim outputWeight As Double 'This is a variable declaration - it's good to assign our output to a temporary variable
'outputWeight = size * 2 'Take the size and multiply by 2
'MATWEIGHT = outputWeight & matl 'Set the function name to our output to give us our answer
Dim MatType1(98, 1) As String
Dim flag As Integer
flag = 0
Dim matlDes As String
matlDes = ""
MatType1(0, 0) = "304SS - ANGLE"
MatType1(1, 0) = "304SS - CHANNEL"
MatType1(2, 0) = "304SS - FLAT BAR"
MatType1(3, 0) = "304SS - HSS"
MatType1(4, 0) = "304SS - I-BEAM"
MatType1(5, 0) = "304SS - PIPE"
MatType1(6, 0) = "304SS - PLATE"
MatType1(7, 0) = "304SS - ROUND BAR"
MatType1(8, 0) = "304SS - SQUARE BAR"
MatType1(9, 0) = "304SS - WF BEAM"
MatType1(10, 0) = "410SS - FLAT BAR"
MatType1(11, 0) = "410SS - PLATE"
MatType1(12, 0) = "410SS - ROUND BAR"
MatType1(13, 0) = "420SS - FLAT BAR"
MatType1(14, 0) = "420SS - PLATE"
MatType1(15, 0) = "420SS - ROUND BAR"
MatType1(16, 0) = "44W - PLATE"
MatType1(17, 0) = "44W CAT 1 - PLATE"
MatType1(18, 0) = "50W - ANGLE"
MatType1(19, 0) = "50W - BAR GRATING"
MatType1(20, 0) = "50W - CHANNEL"
MatType1(21, 0) = "50W - MC CHANNEL"
MatType1(22, 0) = "50W - FLAT BAR"
MatType1(23, 0) = "50W - HSS"
MatType1(24, 0) = "50W - I-BEAM < 8in"
MatType1(25, 0) = "50W - I-BEAM > 10in"
MatType1(26, 0) = "50W - PIPE"
MatType1(27, 0) = "50W - PLATE"
MatType1(28, 0) = "50W - ROUND BAR"
MatType1(29, 0) = "50W - SQUARE BAR"
MatType1(30, 0) = "50W - WF BEAM"
MatType1(31, 0) = "50W CAT 3 - PLATE"
MatType1(32, 0) = "630SS - FLAT BAR"
MatType1(33, 0) = "630SS - PLATE"
MatType1(34, 0) = "630SS - ROUND BAR"
MatType1(35, 0) = "700QT - PLATE"
MatType1(36, 0) = "ALUMINUM - ANGLE"
MatType1(37, 0) = "ALUMINUM - CHANNEL"
MatType1(38, 0) = "ALUMINUM - FLAT BAR"
MatType1(39, 0) = "ALUMINUM - HSS"
MatType1(40, 0) = "ALUMINUM - I-BEAM"
MatType1(41, 0) = "ALUMINUM - PIPE"
MatType1(42, 0) = "ALUMINUM - PLATE"
MatType1(43, 0) = "ALUMINUM - ROUND BAR"
MatType1(44, 0) = "ALUMINUM - SQUARE BAR"
MatType1(45, 0) = "ALUMINUM - WF BEAM"
MatType1(46, 0) = "BRASS - PIPE"
MatType1(47, 0) = "BRASS - PLATE"
MatType1(48, 0) = "BRASS - ROUND BAR"
MatType1(49, 0) = "BRONZE - PIPE"
MatType1(50, 0) = "BRONZE - PLATE"
MatType1(51, 0) = "BRONZE - ROUND BAR"
MatType1(52, 0) = "NYLATRON - PLATE"
MatType1(53, 0) = "NYLATRON - ROUND BAR"
MatType1(54, 0) = "UHMW - PLATE"
MatType1(55, 0) = "UHMW - ROUND BAR"
MatType1(56, 0) = "ANCHORS"
MatType1(57, 0) = "BUSHINGS"
MatType1(58, 0) = "ROLLER - AXLE"
MatType1(59, 0) = "ROLLER - BEARING"
MatType1(60, 0) = "ROLLER - SEALS"
MatType1(61, 0) = "ROLLER - WHEEL"
MatType1(62, 0) = "ROLLER ASS'Y, 210 DIA."
MatType1(63, 0) = "ROLLER ASS'Y, 270 DIA."
MatType1(64, 0) = "ROLLER ASS'Y, 380 DIA."
MatType1(65, 0) = "ROLLER ASS'Y, 440 DIA."
MatType1(66, 0) = "ROLLER ASS'Y, 660 DIA."
MatType1(67, 0) = "ROLLER ASS'Y, 228 DIA."
MatType1(68, 0) = "ROLLER ASS'Y, 250 DIA."
MatType1(69, 0) = "ROLLER ASS'Y, 381 DIA."
MatType1(70, 0) = "ROLLER ASS'Y, 457 DIA."
MatType1(71, 0) = "ROLLER ASS'Y, 530 DIA."
MatType1(72, 0) = "GUIDE WHEEL ASSEMBLIES"
MatType1(73, 0) = "GUIDE WHEEL ASSEMBLIES W/ SPRINGS"
MatType1(74, 0) = "HARDWARE"
MatType1(75, 0) = "HEATERS, GAIN"
MatType1(76, 0) = "MACHINING INSERTS"
MatType1(77, 0) = "MISC"
MatType1(78, 0) = "NAME PLATES"
MatType1(79, 0) = "PAINT - CHEAP"
MatType1(80, 0) = "PAINT - MIDDLE"
MatType1(81, 0) = "PAINT - EXPENSIVE"
MatType1(82, 0) = "RIGGING, SHACKLES"
MatType1(83, 0) = "RIGGING, WIRE ROPE SLINGS"
MatType1(84, 0) = "RUBBER"
MatType1(85, 0) = "SEALS - FRAME"
MatType1(86, 0) = "SEALS - BULB"
MatType1(87, 0) = "SEALS - BOTTOM"
MatType1(88, 0) = "SEALS - WIND"
MatType1(89, 0) = "SHEAVE BUSHING"
MatType1(90, 0) = "SHEAVE PIN"
MatType1(91, 0) = "SHEAVES"
MatType1(92, 0) = "SPRINGS"
MatType1(93, 0) = "STAIR TREADS"
MatType1(94, 0) = "TBD"
MatType1(95, 0) = "WELDING WIRE - MILD STEEL"
MatType1(96, 0) = "WELDING WIRE - STAINLESS"
MatType1(97, 0) = "WOOD"
MatType1(98, 0) = "GUIDE ROLLER - WHEEL"
MatType1(0, 1) = "Structural"
MatType1(1, 1) = "Structural"
MatType1(2, 1) = "Plate / Flat Bar"
MatType1(3, 1) = "Structural"
MatType1(4, 1) = "Structural"
MatType1(5, 1) = "Structural"
MatType1(6, 1) = "Plate / Flat Bar"
MatType1(7, 1) = "Round Bar"
MatType1(8, 1) = "Plate / Flat Bar"
MatType1(9, 1) = "Structural"
MatType1(10, 1) = "Plate / Flat Bar"
MatType1(11, 1) = "Plate / Flat Bar"
MatType1(12, 1) = "Round Bar"
MatType1(13, 1) = "Plate / Flat Bar"
MatType1(14, 1) = "Plate / Flat Bar"
MatType1(15, 1) = "Round Bar"
MatType1(16, 1) = "Plate / Flat Bar"
MatType1(17, 1) = "Plate / Flat Bar"
MatType1(18, 1) = "Structural"
MatType1(19, 1) = "Bar Grating"
MatType1(20, 1) = "Structural"
MatType1(21, 1) = "Structural" = mat
MatType1(22, 1) = "Plate / Flat Bar"
MatType1(23, 1) = "Structural"
MatType1(24, 1) = "Structural"
MatType1(25, 1) = "Structural"
MatType1(26, 1) = "Structural"
MatType1(27, 1) = "Plate / Flat Bar"
MatType1(28, 1) = "Round Bar"
MatType1(29, 1) = "Plate / Flat Bar"
MatType1(30, 1) = "Structural"
MatType1(31, 1) = "Plate / Flat Bar"
MatType1(32, 1) = "Plate / Flat Bar"
MatType1(33, 1) = "Plate / Flat Bar"
MatType1(34, 1) = "Round Bar"
MatType1(35, 1) = "Plate / Flat Bar"
MatType1(36, 1) = "Structural"
MatType1(37, 1) = "Structural"
MatType1(38, 1) = "Aluminum Plate / Flat Bar"
MatType1(39, 1) = "Structural"
MatType1(40, 1) = "Structural"
MatType1(41, 1) = "Structural"
MatType1(42, 1) = "Aluminum Plate / Flat Bar"
MatType1(43, 1) = "Aluminum Round Bar"
MatType1(44, 1) = "Aluminum Plate / Flat Bar"
MatType1(45, 1) = "Structural"
MatType1(46, 1) = "Brass Round Bar"
MatType1(47, 1) = "Brass Plate / Flat Bar"
MatType1(48, 1) = "Brass Round Bar"
MatType1(49, 1) = "Brass Round Bar"
MatType1(50, 1) = "Brass Plate / Flat Bar"
MatType1(51, 1) = "Brass Round Bar"
MatType1(52, 1) = "UHMW Plate / Flat Bar"
MatType1(53, 1) = "UHMW Round Bar"
MatType1(54, 1) = "UHMW Plate / Flat Bar"
MatType1(55, 1) = "UHMW Round Bar"
MatType1(56, 1) = "Everything Else"
MatType1(57, 1) = "Everything Else"
MatType1(58, 1) = "Round Bar"
MatType1(59, 1) = "Everything Else"
MatType1(60, 1) = "Everything Else"
MatType1(61, 1) = "Everything Else"
MatType1(62, 1) = "Everything Else"
MatType1(63, 1) = "Everything Else"
MatType1(64, 1) = "Everything Else"
MatType1(65, 1) = "Everything Else"
MatType1(66, 1) = "Everything Else"
MatType1(67, 1) = "Everything Else"
MatType1(68, 1) = "Everything Else"
MatType1(69, 1) = "Everything Else"
MatType1(70, 1) = "Everything Else"
MatType1(71, 1) = "Everything Else"
MatType1(72, 1) = "Everything Else"
MatType1(73, 1) = "Everything Else"
MatType1(74, 1) = "Everything Else"
MatType1(75, 1) = "Everything Else"
MatType1(76, 1) = "Everything Else"
MatType1(77, 1) = "Everything Else"
MatType1(78, 1) = "Everything Else"
MatType1(79, 1) = "Everything Else"
MatType1(80, 1) = "Everything Else"
MatType1(81, 1) = "Everything Else"
MatType1(82, 1) = "Everything Else"
MatType1(83, 1) = "Everything Else"
MatType1(84, 1) = "Everything Else"
MatType1(85, 1) = "Everything Else"
MatType1(86, 1) = "Seals"
MatType1(87, 1) = "Seals"
MatType1(88, 1) = "Seals"
MatType1(89, 1) = "Everything Else"
MatType1(90, 1) = "Round Bar"
MatType1(91, 1) = "Everything Else"
MatType1(92, 1) = "Everything Else"
MatType1(93, 1) = "Everything Else"
MatType1(94, 1) = "Everything Else"
MatType1(95, 1) = "Everything Else"
MatType1(96, 1) = "Everything Else"
MatType1(97, 1) = "Everything Else"
MatType1(98, 1) = "Everything Else"
' Find the address of the caller (the cell where the address is)
Dim clr As Range
Set clr = Application.Caller
' Find the row only of the caller
Dim FRow As String
FRow = clr.Row
' Search the range A1 to Z1 for vA, vC, vD, and vE
Dim rng As Range
Set rng = Range("A1:Z1")
Dim vA As String
Dim vC As String
Dim vD As String
Dim vE As String
Dim lA As String
Dim lC As String
Dim lD As String
Dim lE As String
vA = "Quantity"
vC = "Thick. / Dia."
vD = "Width / #-ft"
vE = "Length"
For Each c In Range("A1:Z1").Cells
If c.Value = vA Then
lA = Left(Replace(c.Address, "$", ""), 1)
End If
If c.Value = vC Then
lC = Left(Replace(c.Address, "$", ""), 1)
End If
If c.Value = vD Then
lD = Left(Replace(c.Address, "$", ""), 1)
End If
If c.Value = vE Then
lE = Left(Replace(c.Address, "$", ""), 1)
End If
Next c
' Setting the values from the corresponding ranges to the following variables
Dim qty As String
Dim thk As String
Dim width As String
Dim length As String
qty = Range(lA & FRow).Value
thk = Range(lC & FRow).Value
width = Range(lD & FRow).Value
length = Range(lE & FRow).Value
For i = 0 To UBound(MatType1, 1)
If matl = MatType1(i, 0) Then
flag = 1
matl = MatType1(i, 0)
matlDes = CStr(MatType1(i, 1))
End If
Next i
If flag = 1 Then
If matlDes = "Plate / Flat Bar" Then
MatType = Round(thk * width * length * 0.0000174 * qty, 2)
End If
If matlDes = "Structural" Then
MatType = Round((length / 304.8) * width * qty, 2)
End If
If matlDes = "Round Bar" Then
MatType = Round(((3.14 * (length * (thk / 2) * (thk / 2))) * 0.0000174) * qty, 2)
End If
If matlDes = "Aluminum Plate / Flat Bar" Then
MatType = Round(((thk * width * length) * 0.00000595) * qty, 2)
End If
If matlDes = "Aluminum Round Bar" Then
MatType = Round(((3.14 * (length * (thk / 2) * (thk / 2))) * 0.00000595) * qty, 2)
End If
If matlDes = "Brass Round Bar" Then
MatType = Round(((3.14 * (length * (thk / 2) * (thk / 2))) * 0.00001851883) * qty, 2)
End If
If matlDes = "Brass Plate / Flat Bar" Then
MatType = Round(((thk * width * length) * 0.00001851883) * qty, 2)
End If
If matlDes = "UHMW Plate / Flat Bar" Then
MatType = Round(thk * width * length * 0.03 * qty, 2)
End If
If matlDes = "UHMW Round Bar" Then
MatType = Round((3.14 * (length / 25.4) * (((thk / 2) / 25.4) * ((thk / 2) / 25.4)) * qty) * 0.03, 2)
End If
If matlDes = "Seals" Then
MatType = Round((length / 304.8) * qty, 2)
End If
If matlDes = "Bar Grating" Then
MatType = Round((thk * (width / 304.8) * (length / 304.8)) * qty, 2)
End If
If matlDes = "Everything Else" Then
MatType = Round(qty)
End If
End If
End Function
Any help in getting all the tabs to recalculate when a cell's value changes and/or opening the workbook would be greatly appreciated.
Thanks in advance.
