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)??
Passing Array to VBA. I have the following
View 2 Replies View RelatedI 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 RelatedI'm trying to pass an array of UDT from Excel to a C++ dll but I have no idea how. My UDT looks like this
Private Type Student
Age As Integer
Name As String
StudentID As String
End Type
And my dll function declaration..
Declare Function RegisterStudent Lib "SimpleDLL.dll" _
(arr() as Student) As String
.. which will return a unique Id in string
I'm trying to pass an array from one userform to another. When I run the script, it says: "Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module". It explains further that you cant make an array public. How can I pass my array between forms? It suggests using an varient that contains an array, I tried doing this, heres my
Dim multiAry(15) As Range
Public vmultiAry As Variant
'... and later:
vmultiAry = multiAry
'... and on the next userform:
Dim multiAry(15) As Range
multiAry = UserForm1.vmultiAry
With this, I get an error saying "Compiler error: Cant assign to array" with "multiAry =" selected. Am I doing it totally wrong? How can I pass an array between forms?
Okay, I made an epic fail on a previous post that i turned to Solved, a real error on my part of not fully testing before i posted Solved, basically i was so excited that i'd solved it myself, well partially.
I have the following code in the first sub routine that collects which option button has been selected.
[Code] .....
The second sub routine is per following.
[Code] .......
Unfortunately when i tested this with only one variable myRev(1) it worked fine but when i added more myRev variables 2 thru 4 if failed, so is there a trick to passing an array variable between sub routines?
What I am trying to do is to automatically build a "tree" diagram representing the links in a huge model which is dynamically configured. What I have a problem with is the following:
The tree consists of layers, I start off with the top layer and for each entry in the top layer I can add all its subsidiary layers and draw links between them, this uses a function which takes as its arguments the node name and its layer number (how far down the tree it is) and the number of items in that layer so far.
So I start at the first item in Layer 1 and there are as yet no layers below it. I start at the first one and add the first item in layer 2 then I kick the function off again and that adds the first item connected to item 1 in layer 2 in the layer below (3) starting at the first one, and so on. When I reach the bottom I go up one layer and add the second item in the bottom layer and so on. When I have added all the connctions to the first item in the next to bottom layer I go up one layer and add the second item connected to the first item in that layer and then add all the items connected to it and so on and so on.
In this way I build up the network exhaustively (to make things more complex more than one item in a layer may connect to the same item in the layer below).
I can do almost all that, the issue I am struggling with is I need to keep track of how many items there are in each layer (as some layer 1 items connect to 1, 2, 3 ...8 layer 2 items and so on). my idea is to keep a running total of these in an array LevelCount(1), LevelCount(2) etc. so when I add a new item to a layer I know where to put it. However I cant workout how to do this final step.
Currently I have a function that draws the nodes below a specified node this and takes the correct value from the array LevelCount(n) by passing Levelcount(n) (where n represents how far down the tree you are) into the function and the function then updates the value of LevelCount(n) (ByRef) so that next time I use it it is correct. That is fine but what I want to do is to is to call the same function from within itself when it adds each node which would make it work automatically - it would keep calling instances of the function until it reached bottom and then go back one step at a time to the top but I cant work out how to reference the right value in the array to pass into the second (and subsequent) instances of the function.
I don't think I can simply pass (n) into the function and in the body of the function set LevelCount(n) = LevelCount(n)+1
I also dont know ahead of time how many layers the model will have, nor can I tell which layer a node sits in as it depends on the links that are dynamically configured.
Beyond this a node can also be subsidiary to nodes in more than one level so it needs to sit at the lowest level - but I suspect if I can work out how to do the first bit i can do this too.
I'm trying to pass a multidimensional array to a function that I have defined, but I receive an error about an object mismatch. Here is what I have in regards to the array and function. What should I change?
Code:
Dim diffArray() As Integer
Dim countArray() As Integer
Redim countArray(count,2)
diffArray = getRunningSum(countArray)
End Sub
Public Function getRunningSum(ByRef countArray() As Integer) As Integer()
'Code here......
End Function
I have been struggling with a problem passing a small array to be used in a form and I hope that somebody can help me. I cannot get the array into the form.
In the main module code I have an array containing information on a book reference:
Private Function PresentForm1(ByRef iID() As String, sBookRef() As String) As Boolean
Dim iCounter As Integer
For iCounter = LBound(iID) To UBound(iID)
Form1.ListBox1.AddItem iID(iCounter)
Next
Form1.ListBox1.Selected(0) = True
For iCounter = 0 To 2
MsgBox sBookRef(iCounter) ' This bit works fine
Next iCounter
Form1.Show
End Function ...
Passing an entire dimension of an array to a function....
View 13 Replies View Relatedcode to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion
View 4 Replies View RelatedPassing an array from a sub to another sometimes works, sometimes not. But when I call the same sub twice, it definately gets stuck at least at the second time. Seems as if I tend to lose some of my items of the array. And I cannot figure out why it happens, at what pace it happens or how I could stop this from happening.
1.Could it be that a control perishes as soon as it is set to 'ctrl.enabled=false'? 2.Or could it be that a declared array 'ary=array(tb1,cb1,frm1)' can not be handled without having had its items initialized with some lines of code, although the items visibly exist within the user form? 3. Can it be that a Sub 'remembers' the former values passed to it, if set to 'optional'?
However, I have added a cleared/minimized version of my userform as a file to this post, hoping that some of you out there might find the time to have a look at it.... I would really appreciate it. I have added the decisive parts of the macro below here, as well,
Private Sub UserForm_Initialize()
Dim elem As Variant
Dim wSht As Worksheet
Dim ctrl As Control
'set control arrays
'[...]
ctrlAllStep2 = Array(cbTimeOnly, cbIntensityOnly, cbAreaOnly) '[...]
ctrlAllStep3 = Array(tbIntensityTotal, tbIntensityBleached) '[...]
MsgBox "Two Controls exist now: 1." & ctrlAllStep2(0). name & ", 2. " & ctrlAllStep2(1).name, vbInformation, "Note: controls exist..."
'disable Controls in Step2 and in Step3....................
I've noticed something strange when trying to solve this problem (XL07):
ABC1Data1Data2Sum21
32
43x654
65x976
87
98x21109x9
I was trying to create the sums in column C from "x" to "x". So I built the following array-formula in C2 and downwards:
{=SUM(OFFSET(A2,,,MAX((B$1:B1"")*ROW($1:1))-ROW()))}
It works fine, but unfortunately it also shows the "subtotals" in the rows without an "x". "No problem", I thought: this can be easily done with an IF-function surrounding the formula, like this:
{=IF(B2="","",SUM(OFFSET(A2,,,MAX((B$1:B1"")*ROW($1:1))-ROW())))}
But what happened? I got "#N/A!" in each "x"-row!!!
How can it be, that the IF-function affects the inner function???
Meanwhile I've found another formula to solve the original problem
How can I pass a column as a parameter when I execute the sub? ex:
View 2 Replies View RelatedI want to pass the name of the routine as a parameter.
View 6 Replies View Related=INDEX('Data Dump'!$C:$C,MATCH('YTD Detailed'!B$2&A40,'Data Dump'!$G:$G&'Data Dump'!$D:$D,0))
I have to above array formula, i am looking to nest an offset within it. That looks up two columns to the right, but im having trouble.
Can an offset be added to an index?
I got a couple of userform and want to make sure all form are closed before opening the next one.
I'm trying to pass the active user form as a parameter to a subroutine that will ensure the form is close before opening the main menu, but I'm doing something wrong.
Code:
Private Sub CmdMainMenu_Click()
Call gotoMainMenu(FrmInventoryMain)
End Sub
Code:
Sub gotoMainMenu(acsheet As String)
Unload acsheet
Unload FrmMainMenu
FrmMainMenu.Show
End Sub
Within a module I have about 10 Label_Click events. Since they essentially do the same thing I want to create a another procedure that these 10 label_click events will call.
This is what the click event on Label2 looks like:
Code:
Private Sub Label2_Click()
LabelClick (frm.Label2)
End Sub
As you can tell I am trying to call LabelClick routine, which looks like this:
Code:
Private Sub LabelClick(ByRef lbl As Object)
...{my code here}
End Sub
But when I run it, it throws an error at event level saying "Type mismatch".
I also tried "Label" instead of "Object" in the signature, but I get the same error.
I have three columns of Data. A is vehicle number, B is miles and C is a Date. I want to be able to pull the miles for the newest date when the user types in a bus number next to the formula. I think It can be done with an array formula but I am not 100% sure on how to do it. Also is it possible if they enter a vehicle number and a date that a different formula finds the miles for the most recent date to the date entered.
Excel Help.JPG
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
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.
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies View RelatedSay I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method
IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")
I then copy and paste 'values' and filter out the 'false' to get my final result.
This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.
This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.
wondering is there a VBA equivalent of --() in excel that turns trues and falses to 1's and 0's?
View 14 Replies View RelatedIs there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
=CORREL(C1:C10,C12:C21) at H1
=CORREL(C1:C10,C23:C32) at H2
=CORREL(C1:C10,C34:C43) at H3
etc
can i have a macro that first array remain the same, and second array always 11 cells added. drag it down also can
I've tried to multiply each element in a 6x6 array by a similar 6x6 array, both on the same sheet, and it worked.(see Macro2 and attached xls file "Test").Then I got more ambitious and tried to do the multiplication from a standard array in sheet "TestA", with the result on the same sheet, by each array in sheet "TestB" and failed.How do I solve this problem? Pgualb PS:I'm using the R1C1 style.
Sub Macro2()
For y = 29 To 34
For x = 2 To 7
Cells(x, y) = Cells(x, y - 27) * Cells(x, y - 18)
Next x
Next y
End Sub
Sub Teste12()
'Multiplica matriz em TestB por matriz padrão em TestA com _
'resultado na matriz em TestA correspondente à matriz em TestB
'
Dim x, y As Integer
For y = 2 To 7.............