going to Class Modules missing something big here 3

[imarosel]

Hello,

I'm trying to move to class modules and start doing more OO programing. I'm missing something big here on my first try. I thought I would make a simple class called list with a method that looked for a some data in a spreadsheet and who's attributes included the first and last row of the data.

Here is my class:
************************************************************
Option Explicit

Dim name As String
Dim header As String
Dim workbook As String
Dim spreadsheet As String
Dim counterstart As String
Dim counterend As String

Public Sub create(vHeader As Variant, sWorkbook As String, sSpreadsheet As String)
'for now the spreadsheet that contains the list needs to be open
'no spaces after the header row

Dim iHeaderRow As Integer
Dim iHeaderColumn As Integer
Dim iEndRow As Integer

Workbooks(sWorkbook).Sheets(sSpreadsheet).Activate
Workbooks(sWorkbook).Sheets(sSpreadsheet).Select

Cells.Find(What:=vHeader, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

iHeaderRow = Selection.Row
iHeaderColumn = Selection.Column
iEndRow = LastUsedRowInColumn(iHeaderColumn)

header = vHeader
workbook = sWorkbook
spreadsheet = sSpreadsheet
counterstart = iHeaderRow
counterend = iEndRow

End Sub

**********************************************************

Here is the code in a module trying to create an object:
***********************************************************
Sub testsub()

Dim testlist As List

Set testlist = New List

testlist.create("lookforthisword", "MasterSheet.xls", "Functions")

End Sub
**********************************************************

The error I'm getting is it says "compile error = expected" when I type the code and when I compile the code I get a "compile error, syntax error"

I'm sure its a basic concept I'm missing here, I'm just starting to get my feet wet with this OO stuff.

Would somebody throw me a bone?

Thanks,
Austin

 

[WinblowsME]

Standard Module

Sub Test_Sub()
   Dim testlist As New List

   Call testlist.Search_Excel("HI", "Temp.xls", "Sheet1")
   
   MsgBox "ROW:  " & testlist.Get_Row & Chr(13) & "COL:  " & testlist.Get_Column
End Sub

Class Module: List

Option Explicit

Private iHeaderRow As Long
Private iHeaderColumn As Long

Private Sub Class_Initialize()
   iHeaderRow = 0
   iHeaderColumn = 0
End Sub

Public Sub Search_Excel(vHeader As String, sWorkbook As String, sSpreadsheet As String)
   Workbooks(sWorkbook).Sheets(sSpreadsheet).Activate
    
   On Error GoTo Exit_Sub
   
   Cells.Find(What:=vHeader, After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
      xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
        
   iHeaderRow = Selection.Row
   iHeaderColumn = Selection.Column
Exit_Sub:
End Sub

Public Property Get Get_Row() As Long
   Get_Row = iHeaderRow
End Property

Public Property Get Get_Column() As Long
   Get_Column = iHeaderColumn
End Property

 

[WinblowsME]

It's probably better to do the following.

   On Error GoTo Exit_Sub

   Workbooks(sWorkbook).Sheets(sSpreadsheet).Activate

 

[imarosel]

Thanks WinblowsME. I'm going to have to stew on this some when I get some free time today. Does it seem like I'm going down the right path? I want testlist to be an object of the class list, the attributes of which header, workbook,
spreadsheet, counterstart and counterend.

 

[imarosel]

Any advice or code review would be appreciated.

Standard Module:

Sub testsub()

Dim testlist As List

Set testlist = New List
Call testlist.create("Function Name", "MasterSheet.xls", "Functions")

MsgBox testlist.counterstart

End Sub

Class Modlue

Option Explicit

Private vHeader As Variant
Private sWorkbook As String
Private sSpreadsheet As String
Private iHeaderRow As Integer
Private iHeaderColumn As Integer
Private iEndRow As Integer

Public Sub create(vHeader As Variant, sWorkbook As String, sSpreadsheet As String)
'for now the spreadsheet that contains the list needs to be open
'no spaces after the header row

Workbooks(sWorkbook).Sheets(sSpreadsheet).Activate
Workbooks(sWorkbook).Sheets(sSpreadsheet).Select

Cells.Find(What:=vHeader, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Select
    
iHeaderRow = Selection.Row
iHeaderColumn = Selection.Column
iEndRow = LastUsedRowInColumn(iHeaderColumn)

End Sub

Public Property Get header() As Variant
    header = vHeader
End Property

Public Property Get Workbook() As String
    Workbook = sWorkbook
End Property

Public Property Get spreadsheet() As String
    spreadsheet = sSpreadsheet
End Property

Public Property Get counterstart() As Integer
    counterstart = iHeaderRow
End Property

Public Property Get counterend() As Integer
    counterend = iEndRow
End Property

 

[WinblowsME]

Here's a good class reference.

http://www.cpearson.com/excel/Classes.aspx

 

[imarosel]

Thanks again, that is a good reference for the basics.

 

[firefytr]

Why select again? Please, please, no selecting unless in dire straights...

Workbooks(sWorkbook).Sheets(sSpreadsheet).Activate
Workbooks(sWorkbook).Sheets(sSpreadsheet).Select

Cells.Find(What:=vHeader, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Select

iHeaderRow = Selection.Row
iHeaderColumn = Selection.Column
iEndRow = LastUsedRowInColumn(iHeaderColumn)

.. change to ..

'add another variable...
Dim c As Range

With Workbooks(sWorkbook).Sheets(sSpreadsheet)
    set c = .Cells.Find(What:=vHeader, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
End With

iHeaderRow = c.Row
iHeaderColumn = c.Column
iEndRow = LastUsedRowInColumn(iHeaderColumn)

And you could always add an Optional variable to the routine for the workbook path, test if it is open, if not open, test the path/file existence, if good, open. You should use a function to test if it is open..

Function WbOpen(wbName as string) as boolean
'originally found by Jake Marx
    on error resume next
    wbopen = len(workbooks(wbname).name)
end function

Call like this (and set to variable)...

Dim wb as workbook
'...

if wbopen("name.xls") = true then
    set wb = workbooks("name.xls")
else
    set wb = workbooks.open("C:\path\name.xls")
end if

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[firefytr]

Oh, and forgot to add to part of this code...

'add another variable...
Dim c As Range

With Workbooks(sWorkbook).Sheets(sSpreadsheet)
    set c = .Cells.Find(What:=vHeader, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
End With

iHeaderRow = c.Row
iHeaderColumn = c.Column
iEndRow = LastUsedRowInColumn(iHeaderColumn)

.. don't forget to test for your variable..

if not c is nothing then..

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[imarosel]

So no select unless in dire straights huh? Why is that?

 

[firefytr]

Because it is inefficient. There is no need to select or activate in almost all cases. It ties up resources, slows your code down and just plain looks ugly IMHO. You can access objects by referencing them explicitly. This is the same reason why recorded macro code is inefficient. The more you cut out these actions the better off you'll be. Plus, doing so will make you less prone to errors.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[imarosel]

Great firefytr, thanks. I'm always looking for tips on performance as well. So much to do, so much to learn, so few people I can find to "show me the ropes" and share their experience.

 

[PHV]

Have a look here:
faq707-4105

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[imarosel]

thanks PHV. Does anyone have a example of a more intricate class? the link Winblows refers to is good for the "noun" part of a class but not the action parts. It seems to me that OO programming with VB in excel and access doesn't have a lot of reference material. When I go to the bookstore the excel and access books are about the basics and the OO books are all VB.net. Any advice?

 

[firefytr]

Excel 2003 VBA Programmer's Reference
Professional Excel Development
Excel 2003 Power Programming with VBA
Writing Excel Macros with VBA
Excel VBA Programming for Dummies
VBA and Macros for Microsoft Excel

All good books. Also, there are many online resources. Take a look here for some good links:

http://www.vbaexpress.com/resources.php#Excel

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[imarosel]

this will keep me busy for a while. Thanks.