Solved: Need a replacement for sub underneath sub

[nkechifesie]

I have a VBA Program that has a sub embedded in a sub, i feel there is a better way to do this but cant find it. Can you please help me this is a part of the code


Sub Daily_Alerts()
................
Selection.End(xlUp).Select
ActiveCell.Offset(0, 1).Select
ActiveCell = "BSC"
Cd = 4
Cc = Selection.Column
FHeader
Filling
Selection.End(xlUp).Select
ActiveCell.Offset(0, 1).Select
ActiveCell = "CELLS"
Cd = 1
Cc = Selection.Column
FHeader
Filling
................
end sub

Sub Filling()
For k = 2 To Ce
Sheets("Daily Alerts").Cells(k, Cc).Value = Sheets("Matrix sheet").Cells(k, Cd).Value
Next k
Columns(Cc).Select
Columns(Cc).AutoFit
End Sub

Sub FHeader()
ActiveCell.BorderAround Weight:=xlThick
ActiveCell.Font.Bold = True
If b = 5 Then
ActiveCell.Interior.ColorIndex = 4
Else
ActiveCell.Interior.ColorIndex = 10
End If
End Sub

[Bob Phillips]

There is nothing wron in principle with your approach, but I do notice that you are using Cd one second, and Ce the next. As you don't declare the variables, it is difficult to see what they really are. And what is variable b?

Personally, I would pass the values to subroutines as parameters.

[mdmackillop]

FYI, when you post code, select it and click the VBA button to format it as shown.

[Norie]

Ehm, as far as I'm aware you can't 'embed' a sub within a sub.:)

What the code is doing is calling other subs.

But as has been said it isn't particularly clear what the purpose of the code is.

Also I don't see any reason why you have seperate subs in the first place.

[mdmackillop]

Hi Norie,
Separate subs here looks good practice to me, especially if the parameters are passed as Bob suggested.
To avoid the loop try

Sheets("Daily Alerts").Cells(2, Cc).Resize(ce - 1) = _
Sheets("Matrix sheet").Cells(2, Cd).Resize(ce - 1).Value

[Bob Phillips]

And it is always worth breaking code up into functionally discrete subroutines, rather than having large monolithic subs, unless there is a critical performance hit. Makes maintemamce far easier.

[Norie]

I agree seperate subs can be useful, but the OP doesn't actually seem to be passing anything to the other subs.

[johnske]

I agree seperate subs can be useful, but the OP doesn't actually seem to be passing anything to the other subs.He's using public variables to pass the values I'd guess. The basic structure/concept looks ok (and in keeping with MS's advocacy of 'atomic' procedures) but the devil is in the detail (or lack of it in this case)

[Norie]

Public variables to pass values?

I don't see any variables, public or otherwise declared or passed in the OP's posted code.

Am I missing something?

[Bob Phillips]

b, Cc, Ce?

Anyway, you don't just create subs to pass values to them, you do it to modularise the code.

[mdmackillop]

this is a part of the code
Well part of the code is missing, but we can make reasonable assumptions.

[Norie]

xld

There are no variables declared in the OP's code.

[mdmackillop]

Hi Norie,
I takes about 30 seconds to add the missing elements to make the OPs code viable and assist with his question. Have you any positive suggestions to help him?

[johnske]

Norie,
He's using public variables to pass the values I'd guess. ... the devil is in the detail (or lack of it in this case)As the OP hasn't posted all the code I can only guess, the details (including any variable declarations of any kind) are missing so we can only assume they're declared as public in another module if we accept that the code's already working :)

[nkechifesie]

I think, it seems like I cant put them into another. You know when runnig the macro, each of those sub, you see them separately, meanwhile you run one which calls the other as it runs. I thought there was a way to embed them into the main code so when you do alt-f8, you dont see them as separate macros.
I have attached to this mail , the Flowchart of what I want.

[Bob Phillips]

Well, that makes it as clear as mud to me.

[johnske]

If you don't want them to appear in the macro dialog box make them Private subs e.g.

Private Sub Filling()
For k = 2 To Ce
Sheets("Daily Alerts").Cells(k, Cc).Value = Sheets("Matrix sheet").Cells(k, Cd).Value
Next k
Columns(Cc).Select
Columns(Cc).AutoFit
End Sub

Private Sub FHeader()
ActiveCell.BorderAround Weight:=xlThick
ActiveCell.Font.Bold = True
If b = 5 Then
ActiveCell.Interior.ColorIndex = 4
Else
ActiveCell.Interior.ColorIndex = 10
End If
End Sub

[mdmackillop]

I thought there was a way to embed them into the main code so when you do alt-f8, you dont see them as separate macros.
If the "embedded" sub contains parameters, you won't see it listed.

[johnske]

sic iustus quis est voluntas of vestri novus subcriptio Bob? (is does non reddo valde puteus)

[Bob Phillips]

subcriptio , ego operor non subscribe (congruo)

[nkechifesie]

Thank you for your help. Putting the sub as private removes it from showing