Collecting data from sheet and then passing it to other modules

  • Currently I have one massive module with 800 lines of code & I want to seperate it into seperate manageable chunks. First step is to get data from the inputs sheet. I'm trying to make the variables public so they can be used in other modules.


    [VBA]
    Option Explicit
    Public FD_Ratio As Double
    Public gears As Double
    Public TM As Double
    Public Wheel_Diameter As Double
    Public F0 As Double
    Public F1 As Double
    Public F2 As Double
    Public Kr As Double
    Public SM As Double
    Public PI As Double
    With ThisWorkbook.Sheets("Inputs")
    gears = .Cells(9, Columns.Count).End(xlToLeft).Column - 10
    End With


    Public Gear_ratios(gears) As Double[/VBA]


    The problem here is the length of some variables ie Gear_ratios is defined by variables on the sheet. How can I get around this?

  • maybe so

    Code
    Public Gear_ratios ()


    And in the procedure where the array is first used

    Code
    With ThisWorkbook.Sheets("Inputs")
        gears = .Cells(9, Columns.Count).End(xlToLeft).Column - 10
    End With
    ReDim Gear_ratios(gears)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!