Passing an array to a sub in VBA

Microsoft OFFICE

    Sponsored Links

    Next

  • 1. Picture compression in Excel 2007 - works in 2003
    Hi, I've written an application to manage a club membership directory with member pictures. Many of the pictures were taken with digital cameras and are megabytes in size. These must be compressed. This works in Excel 2003 but doesn't in Excel 2007.Sub CompressPics() Dim StrtSel As Range On Error GoTo CompressPicsERROR Set StrtSel = Selection With ActiveSheet .Unprotect .DrawingObjects.Select '<< The problem seems to be here somehow CommandBars.FindControl(ID:=6382).Execute StrtSel.Select .Protect End With Set StrtSel = Nothing Exit Sub CompressPicsERROR: ActiveSheet.Protect Set StrtSel = Nothing MsgBox "ERROR in CompressPics Routine." End Sub Any help will be appreciated, Walt
  • 2. Application.Options.BlueScreen replacement idea
    I need a replacement for the Application.Options.BlueScreen that has been removed in office 2007 word. I used this mode to indicate development environment. We have a menu choice that flips the active system to dev servers and databases. The bluescreen was great as a confirmation that we were not on live system. Is there some way to programatically change the look of 2007 word? Maybe change the titlebar and menu background colors? This needs to be tied to the Word application, not the current document as the open docs are often rapidly changed and handed off to other processes. Thanks Ken
  • 3. How do I write the code for an autofilter criteria obtained from .
    I am developing a program using Excel's Visual Basic for Applications and the main aim of the program is to filter certain keywords from a list on the excel sheet. However, I do not want to use the conventional Filter method because of the size of data I have. Alternatively, I prepared a userform with a textbox control that can enable me type any search criteria and a command button that executes the program. But I do not know how to formulate my code so that the text inputted in the textbox as my search criteria is filtered when I click the command button.

Passing an array to a sub in VBA

Postby WOMBAT » Sat, 29 Nov 2003 21:26:51 GMT

I want to pass an array to a subroutine: I tried some 
variants such as Call Output(K(*)) (used in some forms of 
HP BASIC), but could not find one that worked.  Any ideas 
anyone?
For t = 1 To NT		'put data in array
    For w = 1 To NW
        K(t, w) = t * w
    Next w
Next t

Call Output_data(Sheet1, K()) 'pass sheet & array to sub
	'this syntax is not accepted by VBA
'
End Sub
'
Sub Output_data(Sheet_name, K_temp())  
'this should allow various versions of K to be output to   
various sheets
    For t = 1 To NT
        For w = 1 To NW
            Sheet_name.Cells(1 + w, 1 + t) = K_temp(t, w)
        Next w
    Next t
End Sub


Re: Passing an array to a sub in VBA

Postby malcol.smith » Sat, 29 Nov 2003 21:49:00 GMT

This seems to work for me:

Sub ArrayPost()

  Dim K(5, 4) As Long
  Dim t As Long, w As Long


  For t = 1 To 4
    For w = 1 To 4
      K(t, w) = t * w
    Next w
  Next t

  ArrayGather K()

End Sub


Private Sub ArrayGather(anArray() As Long)
  
  Debug.Print anArray(4, 2)

End Sub


- Malc
  www.dragondrop.com
  
  

Similar Threads:

1.PASSING an array to a sub in VBA

I want to pass an array to a subroutine: I tried some 
variants such as 
Call Output(K()) or 
Call Output(K(*)) (used in some forms of HP BASIC), 
but could not find one that worked.  Any ideas anyone?

This is what I want to do
For t = 1 To NT	  'put data in array 
    For w = 1 To NW
        K(t, w) = t * w
    Next w
Next t
Call Output_dat(Sheet1, K()) 'pass sheetname & array to sub
	'this syntax is not accepted by VBA
End Sub
'
Sub Output_data(Sheet_name, K_temp())  
'this sub would allow various versions of K to be output 
to various sheets
    For t = 1 To NT
        For w = 1 To NW
            Sheet_name.Cells(1 + w, 1 + t) = K_temp(t, w)
        Next w
    Next t
End Sub

2.Passing array from 2nd sub back to first sub

I have a sub which calls a second sub.  The second sub 
loads an array.  I want to pass the entire array back up 
to the first sub when the second sub is completed.  How do 
I this assuming that I do not want to make the array a 
public array?


3.Passing Array Values to a Sub

I have a UserForm that contains functionality to collected multiple sets of 
data points for similar things; e.g. the details of parties to a contract or 
the details of rented goods. I collect the information in an array, which is 
declared as Public (because I follow good coding practices and use separate 
modules for UserForm-related and document-related operations and the data is 
used in both places).

An example of the code for doing this, which is called in the Click event of 
a button on the UserForm:

Private Sub AddCustomer()
    CustomerCount = CustomerCount + 1
    ReDim Preserve CustomersArray(1, 1 To CustomerCount) As Variant
    CustomersArray(0, CustomerCount) = Trim(txtCustomerName.Value)
    CustomersArray(1, CustomerCount) = Trim(txtCustomerNumber.Value)
End Sub

Further, I save the values from the arrays into document variables so I can 
retrieve them to rerun the code. An example of the code that does this:

Private Sub SaveCustomerVariables()
Dim i As Long
Dim n As Long
Dim myVariable As String
    For i = 1 To UBound(CustomersArray, 2)
        For n = 0 To UBound(CustomersArray, 1)
            myVariable = "Customer" & i & "Variable" & n
            SaveVariableValue myVariable, CustomersArray(n, i)
        Next n
    Next i
End Sub

And an example of the code that loads them back into the UserForm on a rerun:

Private Sub LoadCustomerVariables()
Dim i As Long
Dim n As Long
Dim myVariable As String
    ReDim CustomersArray(1, 1 To CustomerCount) As Variant
    For i = 1 To CustomerCount
        For n = 1 To 2
            myVariable = "Customer" & i & "Variable" & n
            CustomersArray(n - 1, i) = fcnLoadTextVariableValue(myVariable)
        Next n
    Next i
    CheckCustomerButtons
    LoadCustomersList
End Sub

And if the template is being rerun, I also have code that deletes any 
existing doc vars prior to saving the new ones. An example of the code that 
does this:

Private Sub DeleteCustomerVariables()
Dim PreviousCustomerCount As Long
Dim i As Long
Dim n As Long
Dim myVariable As String
    With myDoc
        If fcnFindVariable("CustomerCount") = True Then
            PreviousCustomerCount = 
fcnLoadNumericVariableValue("CustomerCount")
            For i = 1 To PreviousCustomerCount
                For n = 1 To 2
                    myVariable = "Customer" & i & "Variable" & n
                    DeleteVariable myVariable
                Next n
            Next i
        End If
    End With
End Sub

Now here's where things get fun.

Any one template may have as many as five (or more) instances of similar 
functionality, which means five arrays and - more importantly - five separate 
routines each for saving, retrieving and deleting the doc vars related to 
each instance of the functionality. Obviously these are prime candidates for 
using a 'generic' routine that accepts arguments so as to cut down on the 
number of lines of code and make maintenance easier. And to that end, I've 
developed such a routine for deleting the variables, which looks like this:

Public Sub DeleteArrayVariables(myName As String, myCount As Long)
Dim CountVariableName As String
Dim PreviousCount As Long
Dim i As Long
Dim n As Long
Dim myVariableName As String
    CountVariableName = myName & "Count"
    With myDoc
        If fcnFindVariable(CountVariableName) = True Then
            PreviousCount = fcnLoadNumericVariableValue(CountVariableName )
            For i = 1 To PreviousCount
                For n = 0 To myCount
                    myVariableName = myName & i & "Variable" & n
                    DeleteVariable myVariableName
                Next n
            Next i
        End If
    End With
End Sub

and gets called like this:

Private Sub DeleteCustomerVariables()
    DeleteArrayVariables "Customer", 1
End Sub

However, what I'm struggling with is the routines for saving and retrieving 
the array values - especially around how to identify the array used in the 
routine. It seems to me that since the arrays are declared as Public, there 
should be a way to just pass in (or build) the name of the array rather than 
pass in the entire array. Suggestions?
-- 
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no 
membership required!

4.Passing Arrays to a Sub

I have an array of strings (sIssue) made up of different strings concatanated 
together. The way sIssue will be constructed will be used repeatedly in my 
program so what I would like to do is write a subroutine to construct this 
array for the main program. I'm just not sure how to do this. I would like 
for the code to work something like below

Sub Main()

' Do stuff
dim sIssue (1 to 8) as string

GetString(sIssue())

Range("B1").Select
for i = 1 to 8
   ActiveCell.Offset(i,0).Value = sIssue(i)
next

End
-----------------
sub GetString(Byval sIssue as string)

' do stuff
for i = 1 to 8
   sIssue(i) = ActiveCell.Offset(i,0) & ActiveCell.Offset(i,2)
next
End

Thanks in advance

end sub


5.Passing Arrays to SUBs

I have code which seems to work properly, but I'm slightly nervous as I 
haven't seen it actually written anywhere how this works.  Basically the 
question is how do arrays get passed as parameters to a SUB() ?

I've set up a toy routine that near the top says:  Dim TestVector(10) As 
Long

And later I pass it to a SUB like this:  Call TestRoutine(TestVector)

Apparently the TestRoutine gets compiled as if it also had that same 
typing/sizing for TestVector.  In fact if I try to force it to some 
conflicting typing it compiles ok, but crashes with an error as the CALL 
gets executed.

From putzing around with this toy setup a bit I've sort of concluded that 
VBA passes parameters to it's SUBs using "call by reference" rather than 
"call by value".  Is this true?  Am I safe to pass arrays to subroutines as 
in the CALL example above without specifying typing or sizing anywhere? 
Those things magically pass themselves?  And if it were a large array, then 
all the data cells aren't really getting passed but rather only the 
reference to the array?

I figured I should ask about this before I dig myself too big a hole and 
THEN find out there's a gotcha somewhere in there.  Thanks...

Bill 

6. public function having array passing out to a sub dropdown

7. How to pass a dynamic array FROM a function TO a sub

8. How to pass array variables in sub procedure



Return to Microsoft OFFICE

 

Who is online

Users browsing this forum: No registered users and 85 guest