Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Constant Array In Vba?

Is it possible to use a contstant array in Excel VBA? I have tried several differnt ways to declare it, but the VBA editor keeps yelling at me. The following did not work:

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Extend A Constant Array
I've never had to venture into Arrays as there was no need.

I'm now faced with Arrays.

Generally I'm able to figure things out, in this case I'm stumped.

What I have is a Constant Array, that output the amount of Rows the Array displayed in.

I know the information is there, what I don't know is how to extend the rows.

ie it Outputs 5 Rows right now, with a top row header.

What I want is 10 rows with a header.

View Replies!   View Related
Array Constant Named Ranges
Is it possible to make an named array constant from named constants to use in a lookup?


I have defined the following constants in Names:

Num1 = "1234567890"
Num2 = "0987654321"

And would like to make the following array constant in Names:

Arr = {Text1,Num1;Text2,Num2}

However, it does not work.

I am using Excel 2007 by the way and the above values are made up.

View Replies!   View Related
Array Constant From Cell Values
I need to convert a cell that has a list of values A1 = 1,2,3. To a range expression {1;2;3}. I want to do this with a formula and not VBA. Example: Cell D1 has the formula = SUMPRODUCT(COUNTIF(C30:C31,A30:A32),B30:B32). I want to put the list range from column C into one cell.


View Replies!   View Related
Replace A Constant With A Variable In An Array Formula.
I have the following array function that I am trying to get to work properly:

ActiveCell.FormulaArray = "=SUM(IF(NCR!O2:O100=39326,NCR!Q2:Q100,0))"
39326 is the value of 9/1/2007, and this formula works properly.

I am looking for a way to use this formula but replace 39326 with whatever date is in the first row of the same column as the active cell when it is run.

That is, if the macro was run with cell B8 as the active cell, "39326" would be replaced with whatever value was in cell B1.

View Replies!   View Related
Array Constant Composed Of Named Constants
I try to understand what the syntax is to create and use an array named constant which would be composed of other constant.

Example :
Constant called AvgZone1 which is =AVERAGE(zone1)
Constant called AvgZone2 which is =AVERAGE(zone2)

and then create a named array constant based on the names : =AvgZone1,AvgZone2
is not working. You cannot for example do a SUM().

View Replies!   View Related
Refer To Named Constant In VBA
I created a name in Excel 2002, Name = "NameToUse", Refers to = "Bill".
How do I get the value in VBA code?

Range("NameToUse").value ' doesnt work
NameToUse ' doesn't work

View Replies!   View Related
Access Public Constant From Another Workbook's Vba
how I can refer to a public constant in a different workbook's (which is already open) VBA from the workbook which I am currently working on's VBA?

View Replies!   View Related
VBA Macro: Clear All Constant Cells In Range That Don't Have Formulas
is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents

from another thread (which is excellent!)

- SHG's recommendation of using a named range, for example:


Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents

View Replies!   View Related
Sum Array In VBA And Clearing An Array
1) after I have made an filled an array with data.
Dim orders(100000 To 200000) As Double
I want to then sum the orders above and below a number, lets say

View Replies!   View Related
If And Constant Logical
i have problem with a long code.

So I divided that code in 4 parts.

So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()

I have declared

Option Explicit

Dim mdNextTime1 As Double
Dim myEnter As Integer
'Dim myK1 As Integer
'Dim myK2 As Integer
'Dim myK3 As Integer
Dim myK4 As Integer
Const myC As Integer = 82
Dim Lastrow As Integer
Dim myActualRow As Integer

all my sub have the following structure:

View Replies!   View Related
Declare Constant
what does the symbol # means in VBA? (but I couldn't put the # in the subject of my message )

I'm trying to understand someone's code... at some point he wrote:
sum_LU_Area = 0#

I thought that the # was used to declare a constant but I'm not too sure because in his code earlier he declared
Public sum_LU_Area As Double

Beside, sum_LU_Area is calculated somewhere further in the program.

View Replies!   View Related
Array Formula In VBA
I have a lengthy array formula that I try to enter using VBA. I get the common run-time error. I tried breaking it down, but that did not work. Maybe the syntax was wrong in the break down. Can some help out please?

=IF(COUNTIF($B:$B,B2) 1 Then
Range("B" & x).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True

View Replies!   View Related
VBA How Can I Pop These Into An Array
i have 3 columns of data... i want to populate an array from anything in column 1 that has an 'N' in column 3...

View Replies!   View Related
VBA Initial Array Value
I have declared arrays for the variables in the code segment below. There is however a problem with the way I've initialized C and F as I the compiler stops C(1) and displays the message: "Compile Error: Expected Array." For the calculation to proceed correctly, the first values of the C(i) and F(i) arrays need to be one and zero respectively.

Is there a way to initialize these variables to these values?


C(1) = 1
F(1) = 0
G(1) = Y(1)
B(i) = C(i - 1) + Q
C(i) = (1 + B(i) ^ (-1)) ^ (-1)
F(i) = 1 / (1 + B(i))
G(i) = F(i) * G(i - 1) + (1 - F(i)) * Y(i)
arrKAL1(i)= G(i)

View Replies!   View Related
VBA Array Basics
Lets say I have this....

Dim B as Rangeset B = activesheet.range("A1:C3")Dim a(3) As Doublea(1)= 1.2a(2)= 3.6a(3)= .27 

B is a 3x3 Matrix of values in your sheet

How would I perform matrix multiplication of B * array "a"?

My main problem is with how to reference then entire array "a" and how the data is being stored (is the array a 3x1 or a 1x3). And if there is a VBA MMULT command or if building my own or using the worksheet one are my only options.

View Replies!   View Related
Median Of VBA Array
Hi, Im using a array in Excel VBA, i got a method which looks at a element no of the array as as a return for that array, i have got it to sort the array, i just need some help to find the middle number (median) of the array.

View Replies!   View Related
VBA Control Array
I have a form that launches when a user wants to confirm an order from a customer. It loads a drop-down combo box with our current customer list. When the user selects a customer, a checkbox array is loaded with the items that a customer has ordered. The checkboxes confirm (or un-confirm) that the order has been processed. Everything works fine up to this point. Let's say the user selects a customer, and this customer has 7 orders. 7 checkboxes appear on the form, either checked or unchecked.

The problem is:
when the user selects another customer from the drop down that only has 3 orders. The 3 checkboxes appear just fine, however, the balance of the previous customer (orders 4-7) also still show on the form. How do I remove the previous checkboxes from the form and prepare for a new recordset (or new checkbox array). I have tried to run backwards through the code that creates the array using Me.Controls.Remove instead of .Add, and this is just not working.

View Replies!   View Related
Passing Array To VBA
Passing Array to VBA. I have the following

View Replies!   View Related
VBA Headers And Array
I just joined this message board after having very little success in altering my VBA code. I have a data file and when I run the VBA code, it only works partly. Currently when I run the code, it will create new month headers, but only for a 12 month period and it is based off the current date on the computer. I need this to go to whatever specified date range I choose and I especially need it to go for more than 12 months. I am also having some issues because when my code runs, it is not running back far enough and there are gaps in the analysis that it does. For instance, after changing the time on my computer to 2009, project A and B's data stops in June 2009, even though the define phase is occuring before that. I have attached the code.

View Replies!   View Related
Create An Array In VBA
I am trying to create an Array, and have searched through many threads on this website and realise I don't have a basic understanding of Arrays. Specifically the ReDim Array function.

View Replies!   View Related
Array Variables In VBA
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 Replies!   View Related
Create A Constant Variable
I want to basically set a varible that can be called back into a multiple of documents I will try and explain this as best as I can but it is a fortnightly pay date... i.e. 22/04/2009 Wednesday... as the payroll is fortnightly I want to be able to use the current date or todays date in a future proof macro.

i.e. if todays date is the 14/04/09 then the next pay date has to be the 22/04/09, if this was run in two weeks it would be 06/05/2009.

I was thinking a long the lines of lock in one start date... 14/01/09, then adding increments of 14 days until todays date - variable + 14 * r = "-"

View Replies!   View Related
One Constant To Multiple Cells
I am just wondering if this can be possible:

this is an table with a lot of cells

12 36 89 56 97 78 98 36
36 89 56 97 78 98 36 12
97 78 98 36 56 97 78 98
12 36 89 56 97 etc ..

to become like this ...

View Replies!   View Related
Increment One Column But Keep The Other Constant
Here is what I try to do:
A1 = Power(C1,B1)
A2 = Power(C2, B1)
A3 = Power(C3, B1)
An = Power(Cn, B1)

How can I increment column C while keeping B constant at B1 by using right-bottom mouse pointer dragging on column A? Or is there another way?

View Replies!   View Related
Ambiguous Name Detected With Constant
Option Explicit
Public Const ARK As String = "Inddata-ARK"
Public Const VVS As String = "Inddata-VVS"
Public Const KON As String = "Inddata-KON"
Public Const EL As String = "Inddata-EL"
Public Const LD As String = "Inddata-LD"
Public Const KØL As String = "Inddata-KØL"
Public Const OpDat As String = "Opdatering"

And then macro's under it... So the aboce is in module "INIT_S". Now in another module "RUN_P" I have a sub-rutine that uses these constants with a select case.
Likes this:

Sub OpEksInitNu()
Select Case ActiveSheet. Name
Case ARK
With OpEks
'more stuff
Case VVS
'more stuff
Case KON
'more stuff
End Sub

But when I try to run OpEksInitNu i comes with ambigious name with ARK as the error, later down I use another public constant. But i keep getting the ambigious name error.. What could cause that... I have tried to locate all ARK, VVS etc. to try to determine if it is used later on, but it's not! I only declare the constants one time, and no module is called ARK, VVS etc.

View Replies!   View Related
Entering A Formula As An Array In VBA
My VBA sub contains
DIM sFormula As String
DIM rCell as Range

at some point in the code, the value of sFormula is set to something like
(not really this formula but of that flavour)
later in the code I have an instruction
rCell.FormulaArray = sFormula
(rCell is set to a single cell at the time)

When I run the code and then examine the cell, I find that the cell's formula has been correctly set to
however there are no {curly brackets} surrounding the formula to suggest that it has been entered as an array.

View Replies!   View Related
Quering An Array VBA: Get Blank
I read the my worksheet data into an array. I want to select only row that satisfy certain conditions. But I get blank rows for those that don't satisfy the conditions.

View Replies!   View Related
Maximize Performance Of Array In VBA
I'm trying to write some code that is performing operations on large arrays, using a pair of For loops. In languages like C++ and Fortran, it makes a big difference to the performance of the code which way round you put these 2 For loops, due to the way that array data is stored in the computer memory. However, for VBA, I've no idea which way round this is. For an array, A(i,j), do I want

For i = 1 To N
For j = 1 To M
A(i,j) = Cells(j, i).Value
Next j
Next i

View Replies!   View Related
Sort An Array In VBA Memory
two parts:

The following is the VBA version of what i've been using:

Range("A1:J1000").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _
Range("E2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlDescending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
now arrays are bigger than 1000 rows>> even bigger than 65536.

Such an array is stored in "arrBIG" and dimed as (1 to 10, 1 to 100000). The goal is to sort it by the 4'th, then 5th then 6th values in the first dimension - the one that is 1 to 10 - i call this the column.

arrBIG.Sort Key1:=4, Order1:=xlAscending, Key2:= _
5, Order2:=xlAscending, Key3:=6, Order3:=xlDescending _.................

View Replies!   View Related
Sorting Array's In VBA
I have an array that is 10 rows by 4 columns. I would like to sort the whole array based on sorting column 1 in descending order. Any good ideas to edit or write new code? Anything to send me in the right direction would be sweet as well!

View Replies!   View Related
How To Divide An Array With A Number In VBA
I have an array in my code and I need to divide each elements of the array with a constant.

View Replies!   View Related
VBA: Evaluating Values Against An Array
I am trying to loop through some rows within a worksheet in an effort to make sure the values match a list of values that are defined in an array. However, when I get to the IF statement, I always get a 'Type Mismatch' error.

Dim varRetailers
varRetailers = Array("Depot", "Lowes", "Sears", "TSC", "Walmart", "Z-Other")

For c = rowDataStart To rowDataEnd
If Cells(c, colRetailCat) varRetailers Then '

View Replies!   View Related
Use Vba Pass Array To C++ Dll
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 Replies!   View Related
VBA Selecting A Variable Array
I've successfully copied the array of equations using the VBA that Pjoaquin enlightened me with from my last thread. The outcome was Sheet2!A2:O2 being successfully populated with the equations from my first sheet... but here comes the problem: I'm looking to autofill A2:O2 down to the last record in Column P. But the number of records in this table is varable.

View Replies!   View Related
Enter Array Formula Via VBA?
I have following Array formula in Cells N1:N3.

HTML =SUM(IF(O$4:O$1000="Total I/O Points",N$4:N$1000,0))

Each formula has different text in "".

There are a few macros in this workbook that delete and/or insert rows.
When that happens, the formulas listed above get modified with different references. How do I restore the formulas back to the same range reference [O$4:O$1000]?

View Replies!   View Related
Sumproduct In Vba (formula Array)
I have a function that has a formula sumproduct and here it is:

Set RSheet = Worksheets("Rawdata")
Set RSheet = Worksheets("Rawdata")

RSheet_lastRow = RSheet.Cells(Rows.Count, "A").End(xlUp).Row
RSheet.Range("AJ2:AJ" & RSheet_lastRow).Formulaarray = "=SUMPRODUCT

The problem is that when the formula is sent to each cell V2 (bolded) does not change to V3, V4, V5.

View Replies!   View Related
VBA - Selecting Sheets In An Array
I am trying to clean up a macro a little that used to look like the following:

Sub Refresh()

ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False
ActiveSheet.QueryTables(2).Refresh BackgroundQuery:=False
ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False

I want to set up an array and have the code run through a loop and have gotten stuck on the following:

Sub Refresh()

Dim i As Integer
Dim SheetName As Variant

It does not seem to want to go from the first sheet to the second. What am I doing wrong/what can I do better?

View Replies!   View Related
Use Trend Worksheetfunction On Vba Array
I am trying to use the TREND worksheet function on a vba array. I keep getting Runtime error 1004: Unable to get the TREND property of the WorksheetFunction Class. I can bypass the error (like it were a search with no result), but I get no result of any kind from the function. My search of other threads yields nothing concrete, but causes a guess that it may have to be used on a worksheet range not a vba array. But that's not actually stated in any of the threads, and I find no documentation to that effect. Is this the issue or is it something else altogether?

Sub test()
Dim knowny As Variant
Dim knownx As Variant
Dim newx As Integer
Dim myval As Integer
knowny = Array(2, 4, 6, 8, 10)
knownx = Array(1, 2, 3, 4, 5)
newx = 6
'On Error Resume Next
myval = Application.WorksheetFunction.Trend(knowny, knownx, newx)
' 'next error, err as normal.
' On Error GoTo 0
' Err.Clear
Debug.Print myval
End Sub

View Replies!   View Related
Constant Line In Chart By Quarter
I have a bar chart that has data by the quarter over a span of 3 years, so there are a total of 12 bars on the chart. Everything looks good there, but now I need to add the minimum amount that the customer needs to acheive (which varies every quarter) in the form of a line. So I added a line chart with the minimum data, but the problem is it goes up and down from point to point and doesn't create a visual horizontal line for the customer to see if they are hitting their minimum every quarter. Is there any way around this?

View Replies!   View Related
Multiple Variables To Equal Constant
I'm trying to create a spreadsheet that will tell the user how much of two products to use in order to get a desired percentage of total fat. For example: if the total pounds is to be 3000 at a fat percentage of 30%. There would be two products (a lean meat and high fat meat). The percentage of fat in those two meats will change each time. The user will enter those percentages and then the formula needs to tell how many lbs of each to use in order to get the desired percentage of fat.

Each time there is a "target" amount of each product to use. For example: 1000 lbs of Lean Meant and 2000 lbs of Fat meat. Those are based on the ideal levels of fat in each.

However, sometimes the fat is not at the ideal so the formula has to be adjusted in order to compensate.

View Replies!   View Related
Detect If Cell Contains A Formula Or Constant
I need an equation that lets me determine whether a cell contains a static constant value (either a string or a number), or an equation that generates a value.

Problem: I have a cell that contains a default equation that generates a default value, depending on a few conditions. The user can overwrite this cell with a constant. In another cell I need to know whether that cell is still the default equation or a new constant value.

I cannot simply test the value of the contents to see if they match my default, because the user may choose to enter that value.

View Replies!   View Related
Making A Constant And A Variable In 2007
assume we have constant P = 100, K=50 and F=10.

I would like to "lock" these cells, and have a variable which could adjust and this variable would multiplicate each one and put out the number.

For example:

P = 100
K = 50
F = 10

Variable: 10 this would generate a sum of:

P = 1000
K = 50
F = 10

View Replies!   View Related
Define Constant Last Row For Module
I'm looking to run several subs in a module which all refer to the same constant but I'm having difficulty defining that constant in a way that the various subs will accept. Basically I'm trying to define a constant LastRow. I am fairly new to VBA so am not sure whether to use a Get LastRow public property (which I tried to use and failed) or a Function and then how to make this readable by all the subs.

View Replies!   View Related
Named Constant To Return Sheet Name
Most of the methods used for returning the sheet name are quite cumbersome. "=MID(CELL("filename",A1), FIND("]",CELL("filename",A1))+1,256)" takes up a lot of space in a larger formula.

Is it possible, please, to create a Name which returns the sheet name? For example I could write "=ThisSheet" and it would return the name of the sheet.

Of course I could resort to a UDF, but I wondered if it could be done directly in Excel.

When I try to insert the formula "=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)" into a Name, Excel helpfully converts A1 to "'Sheet1'!A1". So "=ThisSheet" in Sheet2 returns "Sheet1".

If I make the sheet name part of the Name, as in "'Sheet1'!ThisSheet, it works OK, but has to be added for each new sheet (though it works if an old sheet is copied).

View Replies!   View Related
Dialog Constant For The Security Form
I have some code that requires access to the Visual Basic Project. If the user hasn't checked this option in Excel, I'd ideally like to open up the security form for them, with code like the below:


However, I can't find the dialog constant for the security form. Does anyone know it, or have any alternatives? I don't want to use SendKeys.

View Replies!   View Related
Remove Empty Array Items In VBA
I have an array MyArr() in excel.

Its length is from 1 to i where i takes dynamic value from varibable.

Now the problem is MyArr(1 to i) has some empty values.

Like say: if i = 5 then
MyArr(1) = "a"
MyArr(2) = ""
MyArr(3) = "b"
MyArr(4) = ""
MyArr(5) = "c"

How can I get rid of those empty elements so that MyArr() becomes only three elements long and then display them in a range of three cells.

View Replies!   View Related
VBA Sort String Array Function
I have a string that I need to sort.
Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well?

Function Alphabetize(ByVal sText As String) As String
Dim sWords() As String, sTemp As String
Dim i As Long, j As Long, n As Long

'-- clean up text
For i = 1 To Len(sText)
Select Case Mid$(sText, i, 1)
Case " ", "a" To "z", "A" To "Z"
Case Else: Mid$(sText, i, 1) = " "
End Select
'-- remove leading and trailing spaces....................

View Replies!   View Related
Copy Array Formula To Other Cells Via VBA
{=AVERAGE(IF(('Sales Reports'!$A$1:$A$5000=$A3)*('Sales Reports'!$C$1:$C$5000=$C$1),'Break Reports'!$D$1:$F$5000))}

and am copying it to other cells using this
Sub copyformula()
With Sheets("Sheet2")
.Range("B3:B5000").Formula = .Range("B3").Formula
End With
End Sub
It does the copy but removes the { and } thus rendering the formula useless to me. How can I accomplish this task and keep the formula as an array formula?

I tried to drag the formula down and recorded it as a macro but when it runs it takes far to long.

View Replies!   View Related
VBA Array Function For Polynomial Coefficients
I have a VBA function that calculates polynomial coefficients for a series of data pairs. One selects the range of cells that the coefficients are to be stored in, and enters the polynomial formula:

{POLFIT(Xa, Ya, N)}

Where Xa is the array of ordinate values, Ya is the array of data values, and N is the polynomial order to be fit.

It is obvious that one needs to select at least N+1 cells when the array funtion is typed in. But, it is easy to select too few cells.

I am looking for a way to test whether enough cells were selected for the range formula: The function declaration is

Function POLFIT(Xa, Ya, N As Integer) As Variant

Various means I have tried to count POLFIT do not return the correct value.

View Replies!   View Related
Pass Cell Values To VBA Array
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

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

View Replies!   View Related
Copyright © 2005-08, All rights reserved