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


ADVERTISEMENT

Macro Sub Pass Parameter

Aug 21, 2008

I've written a function to delete the charts on a worksheet: ....

View 9 Replies View Related

Pass A Sheet As A Parameter To A Sub

Feb 1, 2010

From Macro1, I want to pass a reference to a sheet. In Macro 2, I want to select that sheet. Here's what i have so far but I'm getting a "subscript out of range" error

Sub Macro1()
Macro2 "Sheet1"
End Sub

Sub Macro2(sheet As String)
Worksheets(sheet).Select
End Sub

View 9 Replies View Related

VBA To Pass Command Line Parameter

Oct 17, 2008

I found the following link that got me started [url], so now I have my code for reading/analyzing comand line parameters. Here is an example of the parm I'm passing in ....

View 9 Replies View Related

Pass Combobox As Parameter To Other Functions

Mar 13, 2008

Can I pass Combobox as a parameter to other function. If yes then how? I can pass Combobox1. Name as a parameter to other function but inside that function I cannot use combobox("Comboboz1.Name"). I don't know the syntax to work on the combobox whose name is passed as a parameter.

View 2 Replies View Related

Excel 2003 :: How To Pass A Parameter In The Input Range

Mar 21, 2014

I have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!$B$1:$B$10

View 2 Replies View Related

Passing Array To OFFSET - Array Height Parameter

Aug 5, 2014

I want to pass an array to Offset in the "Height" parameter, without having to type the array.

{=MAX(SUBTOTAL(9,(OFFSET(A1,ROW(1:5),,{1,2,3,4,5}))))}

I can't seem to figure out how to build the {1,2,3,4,5}. I've tried another ROW(1:5) and have tried nesting that like N(ROW(1:5)) but nothing works.

How I can get the {1,2,3,4,5} without having to type it out (so that I can expand this to a larger list)??

View 8 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Pass Control Dates To Cells As Dates Not Text

Feb 13, 2008

I have a WAY to complicated excel form that I'm going to move to Access but I need to have this working in the interim. The form has a dynamic element, the user can choose the number of items they are ordering and it unhides the required number of text boxes. To keep the scripting down a did a little work-around to update the database using a loop through the form controls:

'Save the main row
MainOrderRow = ActiveCell.Row
LastRow = MainOrderRow + (ExtraOrders - 1) ' Calculate the last row
ExtraOrderNo = 1

For DBRow = MainOrderRow To LastRow

' Save the text box name with the extra order number
k = "txtDescriptionMul" & ExtraOrderNo & ""
l = "txtQuantityMul" & ExtraOrderNo & ""
m = "txtTotalValueMul" & ExtraOrderNo & ""
n = "ComboBoxUnitMul" & ExtraOrderNo & ""...................

View 2 Replies View Related

Pass Function Result Between Subs

Oct 11, 2006

I have a simple/dumb question... How do you "capture" a value that is returned by a custom function.

I have tried searching the forums for this & I know that I should by all rights know how to do this by now... but I just can't figure it out.

Here is a Function that I copied from Ktrasler in this thread:
Week Numbers

Public Function MyWeek(DateArg As Date) As Byte

Const BaseDate = "30/12/2001"

DateArg = CDate(DateArg) - (Weekday(DateArg) - 1)
MyWeek = Int(((DateArg - CDate(BaseDate)) / 7) Mod 52)
If MyWeek = 0 Then MyWeek = 52

End Function

I know how to pass variables to Functions, and tried this one out & it worked splendidly for my needs, but how do I take the value of "MyWeek" and use it in the subroutine that I am calling it from?

View 3 Replies View Related

Pass Formula Result To Variable

Jul 26, 2007

I have come up with the following formula to pull sums of data by month. I am using this because it is inconvenient to use the DSUM formula (I can't put all the criteria together in a simple way):

{=SUM(IF('Sheet1'!$B$2:$B$20="Person1",IF( DATE(YEAR('Sheet1'!$A$2:$A$20),MONTH('Sheet1'!$A$2:$A$20)+1,)=C2,'Sheet1'!$D$2:$D$20,0),0))}

I would like to, in a macro, assign a variable to this result - but cannot figure out the VBA code for it.

View 5 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

Setting Parameter For One Column And If True Then Check Line Above And Below For Another Parameter

Jul 31, 2013

I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:

Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order

[Code] ........

I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.

The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.

View 2 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 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 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

Automatically Updating Dates Once They Pass?

Apr 4, 2014

setting the dates so that they update by 30 days once they have passed.

For example I need a cell that has an upcoming date to add 30 days to it once it has reached the upcoming date, so it is April 4th today, and I need it to change a cell that reads 'April 14th' to 'May 14th' once it is April 14th.

View 1 Replies View Related

Pass Range Through UDF By Matching Dates

Nov 11, 2013

I'm trying to create a UDF that would be able to accept a column as an input range.

Match dates contained in two other cells and return the addreses so I can perform a function between the two addresses.

like Function multiplyrange(B2,B3,A:A)

So
B2= Start Date
B3= End Date
Column A is contains all the dates

sheet set up:

A
B

1
Start Date
1/3/2013

2
End Date
1/7/2013

[Code] ........

So it basically just multiplied all the values between the dates found.

View 7 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

Multiple A Logical Result Array With A Text Array And Return Text

Aug 6, 2009

I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.

View 3 Replies View Related

Pass Vlookup Result Of TextBox To Another TextBox

Feb 13, 2008

I am trying to create a user form that has a series of text boxes that will all have a VLOOKUP function in them based off of input from a the first text box. If I can just get the code for the first one, I think I can figure out the rest. We will say that the file that the user form is contained in is 'Agent Administration' and the file that I want the VLOOKUP to pull from is called ' Roster for Auto Population'.

View 7 Replies View Related

Push Result Into Array

Jul 5, 2014

[Code] ...

How to push strResult into an Array? And how to set and use something like an id?

strResult.id = n

I would like to archive something like:

MsgBox (strResult(0))
MsgBox (strResult(1))
MsgBox (strResult(this.id))
etc

View 13 Replies View Related

See Array In Formula As Result

Jul 4, 2009

Iīm not sure if this is possible: if I have an array in a formula, for example ={A,23,1,O,6} is there a way that I can put "A,23,1,O,6" in the cell where the formula is? so that the result of the formula is a concatenation of the elements of the array?

View 9 Replies View Related







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