CODEWITHSUNDEEP
excelvba
OCL
OCL

Reputation: 1

Am I missing a declaration or a worksheet object header?

I'm setting up a loop to calculate the numbers of paper layers on a roll when you are only given linear footage and the thickness of the paper. The loop represents the sum of a series, and only stops when the sum of equals the linear footage. It reads variables from the worksheet and writes the result to another cell in the worksheet. I believe I have the loop set up correctly and I have declared everything correctly. I have tried to run my code but it gives me a

Compile Error: Object Required" and highlights "Sub NumLayers()

in the debugger. I don't know what object or declaration I am missing.

This is for a 2007 version of excel. I have checked my syntax and looking for missing headers or declarations on google but cannot seem to find any leads

Sub NumLayers()

    Dim Layer As Integer
    Dim radius As Double
    Dim Footage As Integer
    Dim Thick As Double
    Dim FootSum As Double
    'Declared my variables

    Dim ws As Excel.Worksheet
    Set ws = ActiveWorkbook.Sheets("Equation")
    'initiated and set the worksheet object

    Set Thick = ws.Range("D35").Value
    Set radius = ws.Range("D27").Value
    Set Footage = ws.Range("C16").Value
    'Set variable values to values from cells in worksheet

    Set Layer = 1
    'Set counter variable at 1
    Set FootSum = 2 * 3.14159265 * radius
    'Set initial value before loop

    Do Until FootSum >= Footage
        FootSum = FootSum + (2 * 3.14159265 * (radius + (Counter * Thick)))
        Layer = Layer + 1

    Loop

    ws.Range("D37").Value = Layer
    'Write total amount of layers needed to achieve linear footage, to a cell
End Sub

I expect the loop to run and give me the number of iterations it takes for the calculation to be equal to or greater than the linear footage given, as an Integer.

Upvotes: 0

Views: 33

Answers (1)

Blake Daniel
Blake Daniel

Reputation: 84

I'm not sure on the version of excel if you need to Dim ws as excel.worksheet I usually use:

Dim wb as Workbook, ws as Worksheet
set wb = ActiveWorkbook
set ws = wb.worksheets("Equation")

Your main issue is you only "Set" objects = to something. Define your variables like this (Without the 'Set'):

Thick = ws.Range("D35").Value
radius = ws.Range("D27").Value
Footage = ws.Range("C16").Value
'variable values to values from cells in worksheet

Layer = 1
'counter variable at 1
FootSum = 2 * 3.14159265 * radius
'initial value before loop

Upvotes: 1

Related Questions