Macro to change macro or module level const that is calculated

[Ren]

Is it possible to use a macro to change another macro.

I have two Module level constant that i use

Const numCol As Integer = 19
Const numWS As Integer = 3

Ideally, these number can change automatically. It won't work if i delcare a
const with something such as Const numWS As Integer =
Application.WorksheetFunction.CountA(Worksheets("Sheet1").Range("G:G"))

How can I make this work? I am thinking that maybe calling a macro in
another module can change this.

Steven

 

[Per Jessen]

Is it possible to use a macro to change another macro.

I have two Module level constant that i use

Const numCol As Integer = 19
Const numWS As Integer = 3

Ideally, these number can change automatically. It won't work if i delcarea
const with something such as Const numWS As Integer =
Application.WorksheetFunction.CountA(Worksheets("Sheet1").Range("G:G"))

How can I make this work? I am thinking that maybe calling a macro in
another module can change this.

Steven

Hi Steven

You can not change a constant, so you have to declare a variable.

I don´t know what you want to achive, but maybe you can use an event.

Dim numCol as Integer

Private Sub Worksheet_Change(ByVal Target As Range)
numCol =
Application.WorksheetFunction.CountA(Worksheets("Sheet1").Range("G:G"))
'Rest of the code

End Sub


Regards,

Per