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
ADVERTISEMENT
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
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
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
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
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
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
View Related
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
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
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
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
Aug 30, 2006
how can i store the values of an autofilter's list in a array using VBA.
View 4 Replies
View Related
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
Jan 18, 2012
I'm creating a simple bit of code which needs to include an array formula - but the code below fails;
Selection.FormulaArray = _
"=IF($L3="","",IF(ISERROR(INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1)),0))),0,INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1))))))"
"Runtime error 1004: Unable to set the FormulaArray property of the range class"
What am I doing wrong? The formula is fine when typed in.
View 7 Replies
View Related
Dec 30, 2009
I'm having difficulty creating an array formula. In a multi-column sheet, I am looking at a column with classes and a column with a date (in the format 7-Oct-09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes:
=COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and
=SUM((A4:A2500="AA")*(H4:H2500="??-Oct-??")) which gives me 0. Maybe an array formula is not the way to do this.
View 9 Replies
View Related
Mar 3, 2014
I'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. I still get the same error.
[Code] ............
View 1 Replies
View Related
Jul 23, 2009
i've reached a point where i would like to learn more about class modules but i have no resources. When i create a Class object can i visually see it? i want to know as i'm looking to use them within userforms if possible
View 4 Replies
View Related
Nov 25, 2008
I've created a class called CStock. It has 5 standard properties (with Get/Let methods) as explained in that article above. Nothing fancy here.
I also want to create a CPortfolio class that has 3 properties: pName, pNumberofPositions, and pHoldings. the first 2 are stardard, but the last one is different. I want it to somehow keep a collection of stocks.
View 10 Replies
View Related
May 24, 2006
I've created two modules, card and pack. Card has three variables (value, name, suit) and pack is made up of an array of 52 cards. Referencing it from a test module (NOT a class module) I expected to be able to use debug.print pack.card(32).suit to return the suit of the 32nd card. Instead I get an error message "Object or With variable not set". What am I doing wrong
Private p_strSuit As String
Private p_strName As String
Private p_iValue As Integer
Public Property Let Suit(strSuit As String)
p_strSuit = strSuit
End Property
Public Property Get Suit() As String
Suit = p_strSuit
End Property
Public Property Let Name(strName As String)
p_strName = strName
End Property......................................
View 7 Replies
View Related
May 11, 2007
Iam trying to pass a date value in a query to fetch data from a excel sheet and write the output in a different sheet.Iam able to get the output if I pass the data directly but unable to store the value and pass it in the string.
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & XLS_Path & _
"PZN-MTD-Opened_Header_Extract.xls;Extended Properties=Excel 8.0;"
CursorLocation = adUseClient
.Open
End With
Set rs = New ADODB.Recordset ........................
View 9 Replies
View Related
Jul 19, 2007
The second subroutine below selects a range of cells, in this case a column. It selects a range that ends with the last cell that has data and starts with either the first cell that has data or the second cell has data (needed for instance if the first cell is just the heading and doesn't contain data). You'll notice I have hardcoded the "includeFirst" option right now. I don't know how to pass this as an argument when calling this subroutine.
In the first subroutine updateAll() I need to call the second subroutine and want to pass an argument to it. Maybe my approach using subs and application.run is the wrong way to do this?
Sub updateAll()
includeHeader = False
Application.Run "SelectColumn"
' need something like:
' Application.Run "SelectColumn(includeHeader)"
' where subroutine below has something like:
' Sub SelectColumn(includeFirst)
End Sub
Sub SelectColumn()
'Select a Column or Row of Cells in a Used Range Quickly
'http://msdn2.microsoft.com/En-US/library/aa155432(office.10).aspx
includeFirst = False
'includeFirst = True
Dim UpBound As Range
Dim LowBound As Range
If ActiveCell.Row > 1 Then
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Set UpBound = ActiveCell........................
View 3 Replies
View Related
Jul 1, 2014
How can I pass data from a specific cell in one workbook to another worksheet using a hypertext link or command button. For example How would I be able to pass the following data from Workbook 1 R1C1 - Test Data to the same cell in Workbook 2.
View 1 Replies
View Related
Jul 10, 2014
I have tried to pass the worksheets to another workbook by copying the sheets and passing it to the other workbook with Excel's own tool. Whenever I try this I get an error with the API XML request.
View 8 Replies
View Related
Aug 8, 2008
I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.
This code works:
View 9 Replies
View Related
Jun 11, 2009
I am attempting to call a Function from a cell and pass it a range and an Integer. The Integer works fine, but I cannot get the Range to pass into the function correctly.
View 8 Replies
View Related
Oct 20, 2005
I have code in a userform that shows another userform with a text box. When the user types text there and presses OK, I want that userform to close (which it does fine), and return back to the original userform (which it does fine), but I want the typed text to be stored in a variable that the code in the original userform can use.
I tried declaring a public variable, "Rresponse1", in each userform's procedure, but that doesn't work. I test for the content of the variable afterward in the original userform and it is empty.
In the original userform I have:
SaveList.Show '(this is the second userform)
MsgBox "This is response1: " & Response1 '(to test if the variable is being passed)
In the userform SaveList, in the Private Sub CommandButton1_Click() procedure, I have:
Response1 = SaveList.ListName.Text
Application.ScreenUpdating = True
MsgBox Response1 '(to test the variable, here it shows the text fine)
Unload SaveList
How do I get the Response1 variable to keep its content when control is passed back to the original userform?
View 6 Replies
View Related
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
Jul 3, 2014
I am trying to pass a string to a function requiring a ParamArray. See below
Code:
Declare Function LibFunc1 Lib "FuncAddin" (ByVal vtSheetName, ParamArray Mylist() As Variant) As Long
Sub testsub()
Year = 2013
Period = December
MyStr = "Year#" & MyYear & """", "Period#" & MyPeriod & """"
LibFunc1("Sheet1", MyStr) 'how to declare string so it can be passed as ParaArray?
End Sub
View 1 Replies
View Related
Aug 15, 2007
I have one worksheet with a macro which calls a macro in another worksheet. I would like to pass a variable from the first worksheet to the second worksheet.
Worksheet1 macro includes the following line of code to call Macro3 in worksheet2:
Application.Run "'Worksheet2'!Macro3"
Worksheet2 has the following code in Macro3:
Do While response 1 And response 2
response = InputBox("Would you like to print your report on one page or two pages?", "Select number of pages", 1)
[rest of code]
I would like to be able to pass a value of '1' to Macro3 and bypass the message box when Macro3 is called from Worksheet1. However, if the user has Worksheet2 open, macro3 should display the message box.
View 9 Replies
View Related
Oct 25, 2007
Is it possible to write a VBA macro that passes keystrokes to another program? I have a spreadsheet with over 650 UPC codes for new products that have to be entered into our inventory system. I have downloaded and tried a few macro recorders that record keystrokes and mouse movements, but find them to be unreliable at best.
When I use the macro recorder to automate a task in Excel, (such as moving data from one sheet to another or importing data from a text file) I always go in after I'm done recording and add additional code to bullet-proof the macro (make sure the focus is set properly, etc.). I need to have this kind of control for this task and a macro recorder doesn't give me that (at least not these lower-end shareware programs I have been trying). I know that there are some higher-end macro recorders that include scripting that you can write and edit, but this our "slow" season business-wise and my supervisor has made it clear that he doesn't want to spend any money right now.
My task is simple (just very repetitive, which is why I don't want to spend all day sitting in front of my PC doing this):
1. Open Excel spreadsheet, go to starting data cell
2. Open 3rd party application and go to the appropriate data entry screen
3. Write a macro that does the following:
- checks to make sure the sheet is the active sheet
- copies contents of the active cell
- moves focus to other app and pastes data from active cell
- moves focus back to spreadsheet, copies the contents of the cell to the right (using ActiveCell.Offset?)
- makes other app active and pastes data
- repeat copy/paste for the next cell to right (in spreadsheet)
- press twice to save record before returning focus to the spreadsheet
- move cursor down one line, repeat entire process until active cell is blank
Is this possible to do? The third party program is not able to listed as a reference in VBA, but I didn't know if it was possible to at least pass keystrokes to it. It is actually a terminal emulator (Anzio Lite). I guess the full-blown version of the program has some scripting capabilities (from the documentation I've read), but the Lite version does not.
View 9 Replies
View Related