organise vba project

[Robert]

Hello,

I have a great amount of code behind "Sheet1". I would like to create
parts.

I tried putting it into modules, but then this code doesnt work.

This is the first part of the code..


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

x_t_0 = 0
Cells(13, "B").Value = x_t_0
'nivo's boven de gaten

N_0 = Cells(7, "K").Value
N_1 = Cells(6, "K").Value
N_2 = Cells(5, "K").Value
N_3 = Cells(4, "K").Value
N_4 = Cells(3, "K").Value

'=MAX($K$7-B17;0
H_0_0 = Application.WorksheetFunction.Max((N_0 - x_t_0), 0)
Cells(13, "C").Value = H_0_0

H_1_0 = Application.WorksheetFunction.Max((N_1 - x_t_0), 0)
Cells(13, "D").Value = H_1_0

H_2_0 = Application.WorksheetFunction.Max((N_2 - x_t_0), 0)
Cells(13, "E").Value = H_2_0

H_3_0 = Application.WorksheetFunction.Max((N_3 - x_t_0), 0)
Cells(13, "F").Value = H_3_0

H_4_0 = Application.WorksheetFunction.Max((N_4 - x_t_0), 0)
Cells(13, "G").Value = H_4_0

etc

etc

etc...

Is it possible to split this into more modules are what so ever and
that it just keeps on working like it does now? I want to do this
because I am losing track and get tired of scrolling.

Robert.

 

[Keith74]

As you've moved the code out of a specific worksheet you need to tell
excel in which worksheet you want to update the cells. Prefix your
"Cells" with something like

Worksheets("sheet1").Cells.......insert cell ref here

or you could use

sheet1.activate at the beginning of your sub instead.

hth

Keith

 

[Jon Peltier]

An event procedure like Worksheet_SelectionChange cannot be moved to a
regular module, because changing the selection will not run the procedure.
You could create a class module to handle it, but I think that's more than
you need.

The thing to do is put the working parts of the event procedure into a
separate procedure in a module somewhere, and call it from the event
procedure. So the event procedure in the code behind the sheet you have
this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Activate ' Me = this worksheet
Call ProcessSelectionChange
End Sub

and in a regular module, you have the following:

Sub ProcessSelectionChange()
x_t_0 = 0
ActiveSheet.Cells(13, "B").Value = x_t_0
'nivo's boven de gaten

N_0 = ActiveSheet.Cells(7, "K").Value
N_1 = ActiveSheet.Cells(6, "K").Value
N_2 = ActiveSheet.Cells(5, "K").Value
N_3 = ActiveSheet.Cells(4, "K").Value
N_4 = ActiveSheet.Cells(3, "K").Value

' etc.
End Sub

As Keith points out, and as I have shown, you have to make sure the
procedure knows which sheet the cells are on. Usually it picks the active
sheet, but you need to make sure.

- Jon

 

[Tom Ogilvy]

And to add, the selectionchange event must be in the sheet module. You can
have the event call a separate subroutine located in the general module that
contains teh voluminous code that you show.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myprocedure
End Sub

in a general module
Sub MyProcedure()
x_t_0 = 0
Cells(13, "B").Value = x_t_0
'nivo's boven de gaten

N_0 = Cells(7, "K").Value
N_1 = Cells(6, "K").Value
N_2 = Cells(5, "K").Value
N_3 = Cells(4, "K").Value
N_4 = Cells(3, "K").Value

'=MAX($K$7-B17;0
H_0_0 = Application.WorksheetFunction.Max((N_0 - x_t_0), 0)
Cells(13, "C").Value = H_0_0

H_1_0 = Application.WorksheetFunction.Max((N_1 - x_t_0), 0)
Cells(13, "D").Value = H_1_0

H_2_0 = Application.WorksheetFunction.Max((N_2 - x_t_0), 0)
Cells(13, "E").Value = H_2_0

H_3_0 = Application.WorksheetFunction.Max((N_3 - x_t_0), 0)
Cells(13, "F").Value = H_3_0

H_4_0 = Application.WorksheetFunction.Max((N_4 - x_t_0), 0)
Cells(13, "G").Value = H_4_0

etc

etc

etc...

End Sub

The advice to qualify this with the worksheet is sound, but since the
routine is called from the selectionchange event, the appropriate sheet will
be the activesheet and it isn't essential.