Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 14th, 2006, 07:32 AM
Authorized User
 
Join Date: Nov 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default passing references to a function

Hi,
am still unsure of how to reference objects etc to a function

with this function, I am trying to create a customview, after locating the desired area by search. The Sheet could be any of 4 sheets(Domestic,Business,Charity,Inactive)all structured the same.

Public Function ViewLatest(S As Sheets, R As Range) as long

Dim wb As Workbook, x, y
    Sheets(S).Select
    Range("B2").Select
    x = R.Value 'get the previous stored row number so can reduce search time

    If x > 10 Then ActiveCell.Offset(x - 10, 0).Select

   Call FindEmptyCell 'find first vacant row to add member data
   x = ActiveCell.Row
   R.Value = x ' store new row number for next time
   Set wb = ThisWorkbook
   y = "LATEST" & S ' establish customview name related to sheet name
  wb.CustomViews.Add y, False, True
  ViewLatest=x

End Function

Any assistance greatly appreciated.

regards Dennis


 
Old November 14th, 2006, 01:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Dennis,

Your fundamental problem is that you should be passing a WorkSheet object to the custom function rather than a Sheets collection. Having passed a WorkSheet object you then can interact with it slightly differently to the way in which you've coded up your example.

Clearly the code post is an extract from a wider bit of code, hence making it hard to tell if this will work for you, but the following should get the functionality you're after:

Code:
Option Explicit

Public Function ViewLatest(ByRef S As Worksheet, ByRef R As Range) As Long

Dim wb As Workbook
Dim x As Integer
Dim y As String

    ' Select the argument sheet
    S.Select
    S.Range("B2").Select

    ' Get the previous stored row number so can reduce search time
    ' Stored in the argument range R passed to the funtion
    x = R.Value

    ' Move (x - 10) rows down from cell B2 if greater than 10 ???
    If x > 10 Then ActiveCell.Offset(x - 10, 0).Select

    ' Find first vacant row to add member data
    Call FindEmptyCell

    ' Store new row number for next time
    x = ActiveCell.Row
    R.Value = x

'    ' NOT NEEDED
'    Set wb = ThisWorkbook

    ' Establish a CustomView name related to sheet name
    y = "LATEST " & S.Name
    ThisWorkbook.CustomViews.Add y, False, True

    ' Return a value to the function output
    ViewLatest = x

End Function

Private Sub FindEmptyCell()
' Guess at FindEmptyCell Routine

    Do Until ActiveCell.Value = ""
        ActiveCell.Offset(1, 0).Select
    Loop

End Sub
HTH,
Maccas

 
Old November 15th, 2006, 03:55 AM
Authorized User
 
Join Date: Nov 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks maccas for straightening out my code. I'm still in the bottom half of the learning curve.

just for interest, the search routine although not relevant to the question is -

Public Sub FindEmptyCell()

    While Len(ActiveCell.Value) > 0
        ActiveCell.Offset(1, 0).Select
    Wend

End Sub

The main concern for me now is how to call the function and correctly pass the sheet and range references.
sheet name is "Business" and the range is a single cell defined by name "nanB".

regards Dennis


 
Old November 15th, 2006, 06:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Dennis,

In which case you're passing string arguments into the routine not Worksheet and Range objects. The original code will work with minimal amendment as follows:

Code:
Public Function ViewLatest(strS As String, strR As String) As Long

Dim S As Worksheet
Dim R As Range
Dim x As Integer
Dim y As String

    ' Set the Range & Sheet variables
    Set S = ThisWorkbook.Sheets(strS)
    Set R = Range(strR)

    ' Select the argument sheet
    S.Select
    S.Range("B2").Select

    ' Get the previous stored row number so can reduce search time
    ' Stored in the argument range R passed to the funtion
    x = R.Value

    ' Move (x - 10) rows down from cell B2 if greater than 10 ???
    If x > 10 Then ActiveCell.Offset(x - 10, 0).Select

    ' Find first vacant row to add member data
    Call FindEmptyCell

    ' Store new row number for next time
    x = ActiveCell.Row
    R.Value = x

    ' Establish a CustomView name related to sheet name
    y = "LATEST " & S.Name
    ThisWorkbook.CustomViews.Add y, False, True

    ' Return a value to the function output
    ViewLatest = x

End Function

Public Sub FindEmptyCell()

    While Len(ActiveCell.Value) > 0
        ActiveCell.Offset(1, 0).Select
    Wend

End Sub
 
Old November 15th, 2006, 04:23 PM
Authorized User
 
Join Date: Nov 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks maccas for your help -

sub procedure works okay now

Sub ViewBusMems()

    Dim rng As String, S As String, x As Long
    S = "Business"
    rng = "nanB"
    x = ViewLatest(S, rng)

End Sub

and I've learnt something more....

regards Dennis







Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing an array to a Sub or Function donrafeal Access VBA 2 May 11th, 2006 09:39 AM
Passing Parameters to a function atcs2152 Ajax 4 December 22nd, 2005 09:12 AM
passing variable to function Boszky Pro PHP 2 December 15th, 2005 03:01 AM
Passing a value from one function to another PortGuy Javascript 4 October 17th, 2005 06:01 AM
Passing nodeset into position() function EstherMStrom XSLT 1 May 18th, 2005 04:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.