Pass Array Variables Between Modules

Jun 8, 2007

I am trying to pass a public variable to another module in the same workbook. On Module1 I declare at the top

Public stores
Sub Main()
Dim stores(1 To 100, 1 To 10, 1 To 10)

Reader

...which then calls the procedure Reader in Module2

Sub Reader()
let x=1
let y=1
let z=1
let stores (x,y,z)=activecell.value

I've left out the portions of code that seem irrelevant. When the macro runs, I get a type mismatch error on the "let stores" line. If I move the code from Reader into the procedure Main, it works, so it seems to be an issue with passing the variable. I haven't used multiple modules very often so this is probably a very basic issue.

View 3 Replies


ADVERTISEMENT

Pass Variable Between Modules

Dec 11, 2009

I created the following sub to signal when a macro in Module 1 is complete:


Public Sub Done()
Dim complete As Boolean
complete = True
End Sub
I placed this just before the end sub in the macro for which I am trying to detect that it has finished executing:


Call Done

End Sub
In the Sheet 1 Module, the code fails at the statement:


If complete = True Then
The error returned is "Variable not defined." All three subs are declared as Public. Why does the Sheet1 sub not recognize the variable "complete" from the Module 1 macro?

View 9 Replies View Related

UserForm Variables In Public Modules

Jan 13, 2008

I make a userform called Option and now i want to call some of the variables of the userform in a module. First i try to copy this variables in a cell with the next

ActiveSheet.Cells(2, 3) = Me.MediaP.Value

and it works. But now, i want to call to MediaP in a module. For do that I try to do this:

With Worksheets("Hoja2")
.Range("B1").Value = Option.MediaP.Value

but it doesn't work. I don't want to use public variables or copy/use the information of the cell(2,3), i only want to know how use the information on the textbox called MediaP in a module.

View 3 Replies View Related

Call Modules From Array?

Sep 14, 2012

I have a number of modules that I want to call is it possible to call them from an array

For example

myarray = Array("Outstanding_Per_Agent", "Unique_Records")

Call myarray

This doesn't work as it says it expects a sub function or property

Do I need to define "myarray" as something or is it even possible.

View 2 Replies View Related

Pass And Return Variables?

Mar 21, 2012

Today's question concerns one sub calling another, having the called sub do some stuff that then effects what the calling sub does.

Essentially

Code:
public sub sub1

dim x as integer
dim countCall as boolean

x = activesheet.cells(1,1).value
countCall = False

call sub2(x, countCall)

[code]....

Obviously the above is a very simplified version of what I'm trying to do, since everything there could of course be handled in a single sub. It is for illustration purposes only. My actual main sub needs to call the secondary sub repeatedly in some stacked loops and its in-feasible to but the second sub's code into the main thread.

The problem remains though, I know how to call a sub and pass variables to it, i just have no inkling on how to pass results back to the calling sub. I've tried a few things I've found around the web but I seem to be missing something important.

View 1 Replies View Related

Pass Variables Between Userforms

Jan 26, 2007

I have a userform with a button that wil run macro "sub1". If "sub1" encounters an error on a worksheet it wil show a custom
error messagebox(a userform) that contains the option "Don't ask me again". If true it sets a global variable. Do I need to use global variables to pass to the original procedure "sub1" for the ok/cancel buttons too or is there an other method?

View 3 Replies View Related

Reset Variables For Each Pass Through For Loop

Jun 27, 2014

I am having trouble defining a variable in a For loop. When I run the loop the first time everything seems to work fine, but when the loop goes back through the second time it carries its previous value with it. Here in lies the problem if the variable is supposed to be empty (not assigned a value). So, for example: 'Variable' in the code below may or may not be given a value depending on the conditions in the For i loop. If it is given a value then 'Variable' will still have that value on the next pass through the For j loop. And if none of the conditions are met for the If statement in the For i loop the second time through the For j loop, it should be an empty variable, but instead the code is reading it as having a value (from the previous pass through the For j loop).

[Code] .....

I have tried using:

[Code] .....

After the For j line, and instead of using:

[Code] ....

I tried using:

[Code] ....

and I've tried:

[Code] .....

I get an Object error with either line I use. I just want to be able to reset the variables each time through the For j loop so in the case of an empty variable, it doesn't try to calculate based on the previous value.

View 6 Replies View Related

Pass Variables Between UserForm & Macro

Sep 2, 2007

I am looking at using forms, as in the Userform... Not used them before, but would like to know, if you create a text box on it, is the value global, so any module can use the var? or how can I, so that value entered can be used on the whole book ?

View 3 Replies View Related

Pass Count By Condition To Variables

Jan 22, 2008

Here in the code wbResults is a Workbook present in my present folder. In the Sheet1 and Column "I", the result of test cases is entered. The Result of the TEST CASES can be PASS or FAIL. So i am trying to get the total number of PASS test cases and Total FAILED test cases. BUT with the code i have written, i am not able to fetch the right data. Is the method of comparing wrong? or what else? i have tried to debug but nothing comes out.

Do While Not i = 1008
If (wbResults.Worksheets("Sheet1"). Cells(i, 8) = "PASS") Then
PASS = PASS + 1
End If
i = i +1
Loop

View 4 Replies View Related

Linking Macros & Pass Values/Variables

Oct 18, 2006

I have a worksheet which I've set to read-only, I am trying to get it to pull in information from external worksheets. I've created a Macro that will automatically bring in the data however it will only go into a pre-defined cell/row. I have another Macro which looks up the next available row (which is where I want the data to go) but can't get it to link into the macro's. In short, Macro1 brings in data, Macro2 finds next available row, Macro3 brings in another worksheet, Macro4 finds next available row etc etc.

Sub Macro3()
With ActiveSheet.QueryTables.Add(Connection:= Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsjspencerDesktopExcelAMMH.xl" _
, _
"s;Mode=Share Deny Write;Extended Properties=""HDR=NO;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking................

View 2 Replies View Related

Pass Variables From Userform To Public Procedures

Nov 17, 2007

When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?

View 3 Replies View Related

Populate Multiple Array Variables With Same Code By Dynamically Changing Array Name

Sep 9, 2012

I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.

1
2
3
4
5

6
7
8
9
10

11
12
13
14
15

16
17
18
19
20

21
22
23
24
25

Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer

[Code]....

The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.

View 6 Replies View Related

Use Vba Pass Array To C++ Dll

Jan 21, 2007

I want use vba pass array to c++ dll , and return another array back to vba example is below,but I return value always 0 , how should I do? ...

View 9 Replies View Related

Pass Array Byval

Nov 19, 2007

Is it possible to pass a typed array byval into a sub of function?

i.e.

Sub test()

Dim arr() As Integer

Call testfunc(arr)

End Sub

Function testfunc(ByVal arr As Integer)

End Function

View 9 Replies View Related

Pass Array To A Class

Nov 29, 2007

I have a class

Private MemoryArray() As Variant

Private Sub Class_Initialize()
Redim MemoryArray(0) As Variant
End Sub

Public Sub ReplaceMemory(GivenArray() As Variant)
Redim MemoryArray(UBound(GivenArray)) As Variant

For Index = LBound(GivenArray) To UBound(GivenArray)
MemoryArray(Index) = GivenArray(Index)
Next Index
End Sub

that I am specificall passing an array to the replacememory sub. So in the program I have a global array doved criteria:

Dim Criteria() As String

Sub Product2()
'fill criteria with various entries
'do whatever in macro sub

Set MemoryCriteria = New Memory
MemoryCriteria.ReplaceMemory (Criteria)

and on the last line I get an error message:

Compile Error:
Type mismatch: array or user-defined type expected

View 9 Replies View Related

Pass Array Values To Range

Oct 27, 2008

i have two arrays that I want to use in a trend function. I don't think i can just use the array as is in the fucntion so my guess is that I need to pass the array into a range of data, and help on how I can do this? (also this is in VBA, fyi)

View 9 Replies View Related

Pass Cell Values To VBA Array

Sep 18, 2009

i'm trying to do my homework which requires me to pass values from an array in excel worksheet to VBA and print it in a msg box

i've tried

dim arr as variant
arr = range("A1:A6").value
msgbox arr

but it didn't work.

that's the first step of the homework the second is i have 2 list of array

year:1999 - 2002
profit:10,20,30,40

i have to find the max profit and get the year when it occurs

View 9 Replies View Related

Pass Values From An Array To A Range

Nov 21, 2008

You have an array and a range of the same size and you have to put the array values into the range, something like this:

Dim i As Integer
Dim myCell As Range
Dim myArray(10) As Double

i=0

For Each myCell In Range("A")

myCell.Value = myArray(i)
i = i + 1

Next myCell

except that this code looks a bit awkward to me.

View 2 Replies View Related

Pass Listbox List Into Array

Sep 4, 2007

I know you can read a range of data into a ListBox with a single command. can you read the contents of a ListBox into an array with a single line, and if so what is the syntax?

View 2 Replies View Related

Pass Variable Into Array As Element

Oct 2, 2007

I am trying to pass information that is filled by user in a userform into an excel sheet. Let's say a user would click on a control button in a userform and Macro would ask him what value to store for the first variable. If user clicks one more time then Macro would identify that it was a second click and ask what value to set for a second variable. It is easy to do with limited number of variables, but is it possible that the variable which stores a number of clicks would become a number for variable to store the value?

1 click - a1 = ..
2 click - a2 = ..
....
n click - an = ..

If not possible - which way to search a solution?

View 5 Replies View Related

Pass A Built Array Back To Formula

Nov 11, 2009

I’m trying to send an array of values INTO a user defined function, do a little math on it, and then send the resulting array back to the caller. The caller in this case is an array formula in an excel cell.

I can get it to build an array after the math, but I can’t get it to pass the resulting array back to the formula. The following snippet is a simple version of the code. Here I am building the incoming array in the macro, but same difference at the end. The outgoing Oil_spgr variable never seems to contain the full, final array.

View 4 Replies View Related

Pass The Result To A Sub Which Takes An Array Of Dates As A Parameter

Dec 27, 2008

I delcare an array of dates and assign it with a function that returns an array of dates. Then I want to pass the result to a sub which takes an array of dates as a parameter. However, I am getting a type mismatch error on this line

View 2 Replies View Related

Store/Pass Filtered List Values To An Array

Aug 30, 2006

how can i store the values of an autofilter's list in a array using VBA.

View 4 Replies View Related

Pass Array Elements To Private UserForm Module

Mar 28, 2008

I am trying to pass a string array into a form. I have added a member string array to the form, and a property to "Let" the array in the the member array.

Private sFormString() As String

Property Let FormString(value() As String)
sFormString = value
End Property

I can pass a string in using a procedure:

Sub StringArrayTest1()

Dim TestString() As String
Dim frmString As FString

but I cannot "modulate" the code, or else I get an internal error (error 51). I.e. this code doesn't work:


Sub StringArrayTest2Mod(TestString() As String, frmString As FString)

frmString.FormString = TestString

End Sub

Sub StringArrayTest2()

Dim TestString() As String
Dim frmString As FString

Set frmString = New FString
Redim TestString(1 To 2)
TestString(1) = "Cat"
TestString(2) = "Dog"

Call StringArrayTest2Mod(TestString, frmString)

End Sub

Does anyone know why this happens? Obviously, in the example code its not an issue, but the application I'm using this for is more complex, and some modulation here would be good.

View 9 Replies View Related

Array Variables In VBA

May 5, 2006

Is there any way to add together a portion of an array without using a loop or for statement. i.e.) I have a array variable: arrayvar = array ( 1, 2, 3, 4, 5)

is there a way to sum the last two numbers in the array without using

arrayvar (3) + arrayvar (4) or a loop or for statement.

I am thinking something along the lines of worksheetfunction.sum( arrayvar( 3 to 4)) I know that this does not work but I hope you can see what I am trying to do

View 4 Replies View Related

Array Name Loop Through Variables

Jun 17, 2013

I am trying to loop through different SlicerCaches but it doesn't seem to work.

Here is the code, I am trying,

VB:
Test_Name = Array("[Test - Test Allocation]", "[Test 2]")
For i = LBound(Test_Name) To UBound(Test_Name)

ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")

The code returns a mismatch 13 error.

When I try it without an array, it works fine.

VB:
Test_Name = "[Test - Test Allocation]"
ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")

View 3 Replies View Related

Putting Variables In Array

May 7, 2012

I want to know if I can populate an array with variables..

For example, imagine I've declared 3 variables a, b, c.. and each variable has been assigned a value (which can change)

now I want to say this:

myArray = (a, b, c)

then I want to be able to loop through myArray and retrieve the value attached to each variable..

View 6 Replies View Related

How To Compare Array Variables In VBA

May 22, 2012

I have 2 array variables in a block of code which I need to compare to check if the items in the arrays are exactly the same. The arrays are exactly the same size and I can see the contents of the arrays in the watch window.

When I try to run and IF statement as in: IF VARIABLE1 = VARIABLE2 THEN - I get a compile error saying "type mismatch" on the equal sign.

View 3 Replies View Related

Array Of Variables Want To Match Them

Feb 21, 2008

What would be the best approach/funcvtion to use in excel if I had an array of variables and wanted to match them?

E.g.

1,2,3,4,5,6,7

I would like to lookup up a column and if either of these numbers are in that row to say "yes otherwise "No"

View 9 Replies View Related

Vlookup, 9 Different Variables, 9 Different Table Array

Jun 11, 2009

I need to be able to do a look up in 9 different table arrays using 9 different ranges.

I have attached a worksheet to help explain. My problem is that I can't have 9 IF statements in one formula.

I am not sure how to make this smaller or work.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved