> I have used the following code successfully but somehow feel that it's all a
> bit messy. I have been using VBA quite a bit lately but think I might be
> missing something as far as writing tidy code goes. Has anybody got any
> sugestions? (I seem to forever use
> Workbooks("xxxx").Worksheets("zzzz").Cells(x,y).value. Is there a better
> way?)
> Also, is it correct to store certain variables that are required for later
> use in cells in a worksheet that the user can't have access to? Or should I
> use another method?
>  
>  
>     Do While IsEmpty(Workbooks("W17.XLS").Worksheets(1).Cells(1, 1).Value) =
> False 'Options & supplements
>         x = 1
>         serialNo = Workbooks("W07.XLS").Worksheets(1).Cells(1, 3).Value
>         buildNo = Workbooks("W07.XLS").Worksheets(1).Cells(1, 1).Text
>         optionValue = Workbooks("W17.XLS").Worksheets(1).Cells(1, 4).Value
>  
>         Do While IsEmpty(Workbooks("W07.XLS").Worksheets(1).Cells(1,
> 1).Value) = False 'Machine transfers
>             If Workbooks("W07.XLS").Worksheets(1).Cells(x, 3).Value =
> serialNo And _
>                 Workbooks("W07.XLS").Worksheets(1).Cells(x, 1).Text =
> buildNo Then
>                     Workbooks("W07.XLS").Worksheets(1).Cells(x, 4).Value = _
>                         Workbooks("W07.XLS").WorkSheets(1).Cells(x, 4).Value
> + optionValue
>                     Workbooks("W17.XLS").Worksheets(1).Rows(1).Delete
>                     Exit Do
>             End If
>             x = x + 1
>         Loop
>     Loop
>  
> Thanks
> Richard
>



There are two constructions you can use to cut down the repetition of 
references (and also make your code run faster). You can create object 
variables with the Set statement and you can use With/End With blocks.

For example, the first few lines of your code could be:

    Set wks1 = Workbooks("W17.XLS").Worksheets(1)
    Set wks2 = Workbooks("W07.XLS").Worksheets(1)
    Do While IsEmpty(wks1.Cells(1, 1).Value) = False 'Options & supplements
        x = 1
        serialNo = wks2.Cells(1, 3).Value
        buildNo = wks2.Cells(1, 1).Text
        optionValue = wks2.Cells(1, 4).Value
        
or you could use:

    Set wks1 = Workbooks("W17.XLS").Worksheets(1)
    With Workbooks("W07.XLS").Worksheets(1)
      Do While IsEmpty(wks1.Cells(1, 1).Value) = False 'Options & supplements
          x = 1
          serialNo = .Cells(1, 3).Value
          buildNo = .Cells(1, 1).Text
          optionValue = .Cells(1, 4).Value
        
        .....
        
     End With
     
If you want to store values between Excel sessions, hidden worksheet cells are 
fine. You can also use names (preferably hidden). The following subs store and 
retrieve variable values (the data could equally well be numeric):

Sub StoreString()
    Dim strString As String
    strString = "Thing to store"
    ThisWorkbook.Names.Add Name:="StoredThing", _
        RefersTo:=strString, Visible:=False
End Sub

Sub GetString()
    Dim strString As String
    strString = [ThisWorkbook.Names("StoredThing")]
    MsgBox strString
End Sub