how to input data from a spreadsheet into a macro

Hi @David Hill ,

You can read the cell value directly in VBA.

If the month number is always in a fixed cell, for example A1 on Sheet1, use:

Sub TestMacro()
    Dim monthNumber As Long

    monthNumber = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

    MsgBox "Month number is " & monthNumber
End Sub

Change "Sheet1" and "A1" to match your actual sheet and cell.

If you want the macro to use the currently selected cell instead, use:

Sub TestMacro()
    Dim monthNumber As Long

    monthNumber = ActiveCell.Value

    MsgBox "Month number is " & monthNumber
End Sub

For a simple month number, .Value is enough. This works in Excel 2024 as well; it does not require Microsoft 365.

You can check these Microsoft VBA references for more information: Range.Value property and Working with the active cell.

If you found my response helpful or informative, I would greatly appreciate it if you could follow this guidance. Thank you.

Hi, I'm John! I will help you with this.

The simplest way is to read the cell value directly in the VBA macro.

For example, if the month number is in cell A1:

Sub TestMacro()
    Dim monthNumber As Integer
    monthNumber = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
    MsgBox "Month number is " & monthNumber
End Sub

Just replace Sheet1 and A1 with the actual sheet name and cell you are using.

Excel 2024 can still use VBA macros, so this should work as long as macros are enabled.

Can you confirm where the number is entered? For example, is it in a fixed cell like A1, or do you want the macro to use the currently selected cell?

Hi @David Hill

Sub Test()
    Dim v As Variant
    v = Range("G7").Value   'Reads cell G7
    MsgBox v
End Sub

Thanks.