Results 1 to 1 of 1

Calling other subs

Threaded View

  1. #1
    Registered User
    Join Date
    06-23-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    28

    Calling other subs

    I wanted to know of a way to call another sub to produce a message box. Example:

    I have a script that pulls information of products and prices combines then produces output in two other columns. I then have a message box that tell how many different products there are in these two columns.

    I want to have the message box in another sub (to call upon) with the information. It works but want product the number correctly. Code below:

    Option Explicit
    Option Base 1
    
    Sub ProductSales()
        ' Call clear sub.
        Call Clear
    End Sub
    
    Public Function Sales()
    
        ' These are inputs: the number of transactions, the product code for each
        ' sale, and the dollar amount of each sale.
        Dim nSales As Integer
        Dim productCodesData() As Integer
        Dim dollarsData() As Single
        
        ' The following are outputs: the product codes found, the number of transactions
        ' for each product code found, and total dollar amount for each of them.
        Dim productCodesFound() As Integer
        Dim transactionsCount() As Integer
        Dim dollarsTotal() As Single
        
        ' Variables used in finding unique product codes.
        Dim isNewProduct As Boolean
        Dim nFound As Integer
        
        ' Counters.
        Dim i As Integer
        Dim j As Integer
        
        ' Find number of sales in the data set, redimension the productCodesData and
        ' dollarsData arrays, and fill them with the data in columns A and C.
        With Range("A2")
            nSales = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
            ReDim productCodesData(nSales)
            ReDim dollarsData(nSales)
            For i = 1 To nSales
                productCodesData(i) = .Offset(i, 0).Value
                dollarsData(i) = .Offset(i, 2).Value
            Next
        End With
    
        ' Initialize the number of product codes found to 0.
        nFound = 0
    
        ' Loop through all transactions.
        For i = 1 To nSales
            
            ' Set the Boolean isNewProduct to True, and change it to False only
            ' if the current product code is one already found.
            isNewProduct = True
            If nFound > 0 Then
                ' Loop through all product codes already found and compare them
                ' to the current product code.
                For j = 1 To nFound
                    If productCodesData(i) = productCodesFound(j) Then
                        ' The current product code is not a new one, so update
                        ' its transactionsCount and dollarsTotal values appropriately, and
                        ' exit this inner loop.
                        isNewProduct = False
                        transactionsCount(j) = transactionsCount(j) + 1
                        dollarsTotal(j) = dollarsTotal(j) + dollarsData(i)
                        Exit For
                    End If
                Next
            End If
            
            If isNewProduct Then
                ' The current product code is a new one, so update the list of
                ' codes found so far, and initialize the transactionsCount and dollarsTotal
                ' values for this new product.
                nFound = nFound + 1
                ReDim Preserve productCodesFound(nFound)
                ReDim Preserve transactionsCount(nFound)
                ReDim Preserve dollarsTotal(nFound)
                productCodesFound(nFound) = productCodesData(i)
                transactionsCount(nFound) = 1
                dollarsTotal(nFound) = dollarsData(i)
            End If
        Next
     
        ' Place the results in columns E to G.
        For j = 1 To nFound
            With Range("E2")
                .Offset(j, 0).Value = productCodesFound(j)
                .Offset(j, 1).Value = transactionsCount(j)
                .Offset(j, 2).Value = dollarsTotal(j)
            End With
        Next
        
        ' Sort on column G in descending order, and display an appropriate message.
        Range("E3").Sort Key1:=Range("G3"), Order1:=xlDescending, Header:=xlYes
        MsgBox "There are " & nFound & " different products that have been sold."
    
    End Function
    
    Sub Clear()
    
        ' Clear any old results in columns E to G.
        With Range("E2")
            Range(.Offset(1, 0), .Offset(0, 2).End(xlDown)).ClearContents
        End With
        
    Call Sales
    
    End Sub
    Last edited by noodle48; 06-30-2011 at 01:08 PM. Reason: Solved my own problem

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1