VBA help: Using Subs in Excel

[dbrown]

Hi,

Wondering if anybody can help me with the following:

I have 2 subs (procedures) that are macros that I can run
successfully when I embed them in the code area for
the worksheets. The subs do some post processing on the
worksheet data.

However, I would like to create a new sub that calls both
of these sub modules. When I paste the the code for the
3 subs into the code area for the workbook and try to run
the top-level macro, it doesn't work. Sometimes the run-time
environment complains that the 2 sub modules are undefined.
How do I forward declare these subs so that they are visible
to the top-level one? Am I doing this in the right way?

dbrown

[Oorang]

Hello Mr/Ms. Brown,
Welcome to the board Please post the code in question so we can see what might be the issue. Thanks!

[dbrown]

Hello Mr/Ms. Brown,
Welcome to the board Please post the code in question so we can see what might be the issue. Thanks!

Code posted below:
**************************

[VBA]Sub Step1_Sort_Data_NoLoad_Data()
Dim Counter3
Dim CurrentRefDes
Dim CurrentAssy
Dim CurrentComp
Dim CurrentQty
Dim CurrentClass
Dim NewComp
Sheets("Intermediate Results").Range("A").Delete
Sheets("Intermediate Results").Cells(1, 1).Value = "ASSEMBLY"
Sheets("Intermediate Results").Cells(1, 2).Value = "COMPONENT"
Sheets("Intermediate Results").Cells(1, 3).Value = "QTY"
Sheets("Intermediate Results").Cells(1, 4).Value = "REFDES"
Counter3 = 2
CurrentRefDes = Sheets("Extracted Input").Cells(Counter3, 4).Value
While Not (CurrentRefDes = "")
CurrentAssy = Sheets("Extracted Input").Cells(Counter3, 1).Value
CurrentComp = Sheets("Extracted Input").Cells(Counter3, 2).Value
CurrentQty = Sheets("Extracted Input").Cells(Counter3, 3).Value
CurrentClass = Sheets("Extracted Input").Cells(Counter3, 5).Value
If (CurrentClass = "NOLOAD") Then
NewComp = "NO LOAD"
Else
NewComp = CurrentComp
End If
Sheets("Intermediate Results").Cells(Counter3, 1).Value = CurrentAssy
Sheets("Intermediate Results").Cells(Counter3, 2).Value = NewComp
Sheets("Intermediate Results").Cells(Counter3, 3).Value = CurrentQty
Sheets("Intermediate Results").Cells(Counter3, 4).Value = CurrentRefDes
Counter3 = Counter3 + 1
CurrentRefDes = Sheets("Extracted Input").Cells(Counter3, 4).Value
Wend

Sheets("Intermediate Results").Columns(1).ColumnWidth = 15
Sheets("Intermediate Results").Columns(2).ColumnWidth = 15
Sheets("Intermediate Results").Columns(3).ColumnWidth = 15
Sheets("Intermediate Results").Columns(4).ColumnWidth = 15
Sheets("Intermediate Results").Columns.WrapText = False
Sheets("Intermediate Results").Columns("A").Select
Sheets("Intermediate Results").Sort.SortFields.Clear
Sheets("Intermediate Results").Sort.SortFields.Add Key:=Range( _
"B2:B1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With Sheets("Intermediate Results").Sort
.SetRange Range("A")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Response = MsgBox("Finished with Step 1 of BOM Consolidate", vbOKOnly, "BOM Consolidate Step 1", "", 0)
End Sub

Sub Step2_ConsolidateRefDes()
Dim Counter1
Dim Counter2
Dim CurrentComp
Dim CurrentAssy
Dim CurrentQty
Dim PriorComp
Dim NewRefDes
Dim PriorQty
Dim NewQty
Dim Response
Sheets("Results").Range("A:E").Delete
Sheets("Results").Cells(1, 1).Value = "ASSEMBLY"
Sheets("Results").Cells(1, 2).Value = "COMPONENT"
Sheets("Results").Cells(1, 3).Value = "QTY"
Sheets("Results").Cells(1, 4).Value = "REFDES"
Sheets("Results").Cells(1, 5).Value = "FINDNUM"
Counter1 = 2
Counter2 = 2
CurrentComp = Sheets("Intermediate Results").Cells(Counter1, 2).Value
While Not (CurrentComp = "")
CurrentAssy = Sheets("Intermediate Results").Cells(Counter1, 1).Value
CurrentQty = Sheets("Intermediate Results").Cells(Counter1, 3).Value
PriorComp = CurrentComp
NewQty = 0
While CurrentComp = PriorComp
NewRefDes = NewRefDes & Sheets("Intermediate Results").Cells(Counter1, 4).Value & ", "
Counter1 = Counter1 + 1
PriorComp = CurrentComp
NewQty = NewQty + CurrentQty
CurrentComp = Sheets("Intermediate Results").Cells(Counter1, 2).Value
CurrentQty = Sheets("Intermediate Results").Cells(Counter1, 3).Value
Wend
If Not IsNull(NewRefDes) Then
NewRefDes = Left(NewRefDes, Len(NewRefDes) - 2)
End If
Sheets("Results").Cells(Counter2, 1).Value = CurrentAssy
Sheets("Results").Cells(Counter2, 2).Value = PriorComp
Sheets("Results").Cells(Counter2, 3).Value = NewQty
Sheets("Results").Cells(Counter2, 4).Value = NewRefDes
Sheets("Results").Cells(Counter2, 5).Value = Counter2 - 1
NewRefDes = Null
Counter2 = Counter2 + 1
Wend
Sheets("Results").Columns(1).ColumnWidth = 15
Sheets("Results").Columns(2).ColumnWidth = 15
Sheets("Results").Columns(3).ColumnWidth = 15
Sheets("Results").Columns(4).ColumnWidth = 15
Sheets("Results").Columns(5).ColumnWidth = 15
Sheets("Results").Columns.WrapText = False
Response = MsgBox("Finished with Step 2 of BOM Consolidate", vbOKOnly, "BOM Consolidate Step 2", "", 0)
End Sub[/VBA]

[Oorang]

Thanks

These subs have no scope declaration, which in VBA means they are Public (by default). In principle as long as you paste these into a standard module (not a worksheet, workbook, form, or class module) they should be visible to you via the Macro menu. Is it possible you might have pasted them into the worksheet/workbook's module?

By the way, these look are for Excel, but this is the Outlook forum... You aren't trying to use these from Outlook are you?