# Storing Variables As Arrays - Code

Jun 29, 2007

I have the following code for a user-defined function:

Function SeriesSum2(A, B, x, C, D, y, z, Num)

Summation = 0

For i = 1 To Num

Summation = Summation + (((A - B) * (((0.01 * B / (A - B)) _
^ (1 / (y - 1))) ^ i) + B - x) / ((1 + x) ^ i))

Next i

Product = 1

For i = 1 To Num

Product = Product * (1 + ((C - D) * (((0.01 * D / (C - D)) _
^ (1 / (z - 1))) ^ i) + D))

Next i

SeriesSum2 = WorksheetFunction.SumProduct(Summation, Product)

End Function

To simplify, suppose Num = 3, and the three terms of Summation are {3, 5, 9}. Call these S1, S2, S3. Further, suppose the first three terms of Product are {1, 4, 12}. Call these P1, P2, P3.

My desired result of the function is 3*1 + 5*4 + 9*12 = 131. Instead, I'm getting 3*12+5*12+9*12 = 204. That is, the function is returning S1*P3+S2*P3+S3*P3, while I want S1*P1+S2*P2+S3*P3.

Can anyone instruct me as to what I need to do to get my desired result? I'm guessing it has something to do with storing Product as an array, but I'm a very novice VB'er, so I'm not sure.

Of course, if there are any other glaring errors in my code,

ADVERTISEMENT

## Storing 3d Arrays In Named Ranges

Jun 1, 2007

I have created a 3d array in visual basic that i want to access from a worksheet. it is basically a linked set of 2 dimensional arrays (i.e. there a x items with a x b data arrays). I want to stored in a named range, as a and b may both be more than 255 (i.e the width of a worksheet). is there an easy way to store and reference a 3d array in a name, or will i have to store each a x b array as a seperate name. Also, if I have to store them separately, is there an easy way to strip the component parts of a 3d array into 2d arrays?

## Compare Variables Storing Times

Dec 27, 2006

I have tried lots of alternative for making Excel Dates & Times calculation work in vba. I can't find the place its creating problem. I have attached spreadsheet to have better look at it. When I am using time in one column (Calender!C) in VBA code, it works fine and update values in R1,R2... columns. But, when I am using time in another column (Calender!D), its not working properly and not updating appropriate columns. I will appreciate if someone can look at vba code in attached file. It looks like I am missing some kind of setting in excel/vba.

## Excel 2007 :: VBA Storing Variables In Array?

Sep 7, 2012

Excel 2007.

I'm basically copying and pasting a bunch of columns. Currently, my code is very long because I'm not using a loop to plug in the column header. how I'd store all the column headers in an array (I think) and loop through 1-by-1.

Here's a look at what I'm doing now:

Code:
' ''Project Number
x = "Project number"
i = Sheets("RawData").Rows(FirstRow).Find(x).Column

[Code]....
how to store these column headers in an array and then pull them?

## Query Tables DataTypes And ColumnWidths Arrays As Variables

Jul 12, 2011

How do I set the datatypes and columnwidths as variables? I get invalid procedure call or argument. Will import different text files and set dt and cw based on names.

Dim dt As Variant
Dim cw As Variant
dt = "2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2"
cw = "7, 7, 5, 2, 7, 16, 3, 15, 11, 9, 9, 9, 7, 3, 3"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;B:BOMOEBTRfro1.txt", Destination:=Range("A1"))

[Code] ........

## Can Use Variable Names To Identify / Assign To Other Variables Or Arrays?

Apr 10, 2014

I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.

Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.

For example, under certain conditions

DataIdentifier would be set to "DataSetA" and under other conditions
DataIdentifier would be set to "DataSetB".

Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.

This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).

## Storing VB Code In A Variable Withing A Macro

Dec 31, 2008

I have piece of code that I'm trying to make dynamic. I want to store parts of an IF statement in a variable and use them when I need them. Here's a little sample of what I'm trying to do

## Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

## Passing Variables And Values Between Sheet Code And Module Code

May 13, 2009

I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure: ...

## Slicing And Dicing CSV Files - Involves Arrays And Jagged Arrays

May 8, 2013

I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:

|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|

.
. extending until the end of the data set
.

I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:

Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub

## Code: Defining Variables

Sep 15, 2008

I'm trying to develop a work spreadsheet template that is to be summarised every fortnight. Our fortnights here go Thurs-Thurs. So I figured I would start with a base date (thurs - Sept 4th) and then have the spreadsheet display the true fortnight ending upon loading. Ultimately this fortnight ending will be used to copy and paste the template information into a summary spreadsheet but for now I am trying to get this intial part working. It's linked to a command button and my attempt at code is below:

Private Sub CommandButton1_Click()
Dim Base As Date
Dim Now As Date
Dim Delta As Integer
Dim No_of_Fortnights!
Dim Rounded As Integer
Dim Fract!
Dim Fort_week_end As Date

(Note the following 8 lines are not the problem, they just open the main sheet. The problem is the eights lines starting with the word 'set')

Sheets("Technical Inquiry Form").Activate
Sheets("Technical Inquiry Form").Range("b3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Sheets("Technical Inquiry Form").Range("b3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Technical Inquiry Form").Activate

## Execute Code Many Times With Different Variables

May 10, 2007

I want to execute the following code once for each day (Mon to Sun)

Dim Day As String
Dim DeletedDept As String
Set StartRange = Cells(Range("Cashiers").Row + 1, Range(Day & "_Date").Column + 1)
Set EndRange = Cells(Range("Cashier_Totals").Row - 1, Range(Day & "_Date").Column + 3)
Set EntryRange = Range(StartRange, EndRange)
For Each cl In EntryRange
If cl.Value = DeletedDept Then cl.Value = ""
Next

## Add R1C1 Formula With Code & Variables

Sep 4, 2007

I am using follwoing vb code to enter formula to sum a range. However it is giving sum of different range.

Cells(Row, Column).FormulaR1C1 = "=SUM(R[" & a & "]C[" & b & "]:R[" & x & "]C[" & y & "])"

Where a, b , x and y are variables containg starting row, starting column, last row and last column value like a=19; b=3; x=24 and y=3. When i check the formula in that cell, it appears as Sum(F25:F30)

## Which Are The Variables In The Code, Delete All The Rows Which Have Data In?

Oct 28, 2008

This vba code will delete all the rows which have data in. At present it starts on A2 (so doesnt delete anything above A2. Which part of the code in red would I need to change for it to be A3 and which would I need to change for it to be B3.

## Dynamic Assignment Of Value To Variables Using Macros And VBA Code

Jul 6, 2012

I have two columns one with the variables names and the other with the values now I have to assign vales to teh variables dynamically using the macros and vba code.

## Excel 2010 :: Condensing VBA Code - Using Dynamic Variables?

Jul 17, 2012

I have some VBA that dumps various sheets data into an SQL Database.

Part of that requires me to sanitize all of the fields before they make it to the DB, (at least to prevent the code from breaking itself w/ errant ' characters.

right now my code is as follows

Code:
If InStr(aa, "'") > 0 Then
aa = Replace(aa, "'", "''")
End If
If InStr(bb, "'") > 0 Then
bb = Replace(bb, "'", "''")

[Code] ......

I was hoping to condense it to something like the following, however it is not working how I hoped / want it to. .. I had found somewhere out there that this Eval() function possibly could be used to 'reference' dynamic variables, however it does not appear to work at all anymore, and even then it may only have worked to 'read' and not 'write' to the variable. (Excel 2010, on Windows 7 64-bit).

Code:
itemsToSanitize = "aa,bb,cc,dd,ee,ff,gg,hh,ii,jj,kk,ll,mm,nn,oo,pp,qq,rr,ss,tt,uu,vv,ww,xx,yy,zz,aaa,bbb,ccc,ddd,eee,fff,ggg,hhh"
ITSArray = Split(itemsToSanitize, ",")

For Each thing In ITSArray
If InStr(Eval(thing), "'") > 0 Then
Eval(thing) = Replace(Eval(thing), "'", "''")
End If
Next thing

## Code To Generate Data Table Based On Variables

Aug 26, 2008

I was trying to use the VBA code to generate a data table:

Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet

'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub

and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?

## Add AVERAGE Formulas To Cells Using Variables In Macro Code

Apr 15, 2008

I am trying to have the formula =( SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Put into cells through vba. What I did to get the formula is typed it into an excel cell to find the average of a group of cells that do contain blank cells. The formula brought out the proper results. So all I did is put the formula into vba and changed the appropriate parts. The range will not be the same of course, but there is what I have.

ActiveCell.Formula = "=(SUMIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "," & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & "))/(COUNTIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "))"

When I show a msgbox for ActiveCell.Formula (Msgbox activecell.formula), it shows me the formula as above - =(SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Except instead of the s:ranges, it shows \$L2:\$L125 (which is correct). The quotes do show up around the criteria in both the sumif and countif. I keep receiving an error. I put a msgbox err.description & ", " err.number dialog in. The error comes up as ", 0" (no quotes).

## Add Vlookup, With Variables, To Cell To Reference Another Workbook Via Macro Code

May 21, 2008

I am trying to use VB to vlookup between to workbooks
1. Make active workbook WBK1
2. Make workbook being open WBK2
3. Copy and Paste between WBK1 and WBK2
4. Have a vlookup in WBK1 and bring in the values from WBK2
5. Close WKB2
6. Copy, Paste, and transpose values in wkb1 within wkb1

The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.

## Add Nest Formula/Function To Cell With Variables Via Macro Code

May 22, 2008

I would like to use a varible Cell for the following forumula:

ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F\$2,F\$3,F\$4,F\$5,0,)/3600,1)"

I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).

## Storing A Table

Jun 17, 2009

I want to create & store ad-hoc tables in excel.... can anyone suggest the best way of doing this? Maybe can i use some sort of marker to distinguish when one table ends, and another begins? For eg, maybe i want to store 3 tables - 1 of different fruits and fruit volumes, the 2nd of employees and their shift times, and the third of various different leaflets, paper codes, volumes and dates.... eg, no particular theme

## Storing And Using A Logo

May 18, 2006

I have solution that uses userforms, the intro/menu has a company logo on it. What I want to be able to do is use this logo on actually worksheets that I have set up as templates for sending out invoices etc.

The person I am doing this for sometimes uses pre-printed headed paper and sometime plain papper. So I have a check box that asks whether headed paper is used. If so not a problem, if not, I need to add the logo at the top of the page, ideally from within the workbook.

## Storing In Array

Feb 20, 2007

Here's the code linked with excel spreadsheet:

Function Function1(DailyClose, EFBillsYield)

TradingDays = Application.WorksheetFunction.Count(DailyClose)

For i_cnt = 1 To TradingDays - 1
DailyReturn = Application.WorksheetFunction.Ln(DailyClose(i_cnt) / DailyClose(i_cnt + 1))
Next i_cnt

AnnualReturn = Application.WorksheetFunction.Average(DailyReturn) * TradingDays
AnnualVolatility = Application.WorksheetFunction.StDev(DailyReturn) * Sqr(TradingDays)
RiskFreeRate = Application.WorksheetFunction.Ln(1 + EFBillsYield)

Function1 = (AnnualReturn - RiskFreeRate) / AnnualVolatility

End Function

When I enter the parameters DailyClose (which is an array of numeric), and EFBillsYield, which is a numeric also, the function will calculate the DailyReturn (in array) for each array element in DailyClose.

The problem is, I don't know how to catch the DailyReturn for each DailyClose element. The above code only loops and calculates DailyReturn one by one, but not storing it as an array.

How should it be modified so that the DailyReturn can be numeric array also, so that it can perform average and SD functions below the loop?

## Storing Information By Date

Apr 1, 2009

I've created a workbook that I use daily. The workbook has two sheets. One for entering information. The other for storing information.

On the first sheet, I enter health information about myself. I also enter the date.

On the second sheet, I have rows dated from January 1, 2009 to December 31, 2009. If the date entered on the first sheet matches January 1, 2009, then the information is copied to that respective row. If the date entered on the first sheet matches January 2, 2009, then the information is copied to the corresponding January 2, 2009 row, etc, etc.

So far so good. I enter the information, the date, and instantly the information is copied over. I save the workbook and go to sleep happy................

## Storing A Value In A Variable For Use At A Later Date

May 19, 2008

I have an Add-in that creates and formats reports for various users.

Within the add-in I have rules set to disable some buttons on a userform untill a process is run to enable them. I am doing this through the use of a variable - set as "0" untill such a time when the process is run that it will set the variable to "1". A function is then run to enable all of the buttons.

However, once excel is shutdown and re-openned - it resets the variable to "0" for no apparent reason. Is this normal? Is there a way around this - so that the Add-in remembers what the variable was set to before closure?

## Storing A Variable As A Range To Be Used Later

Nov 23, 2009

I am trying to store a variable (I will highlight it in red below) to be used later in the same formula. The formula I have now works, but it puts the variable in cell G20, and I do not need this variable anywhere on the sheet. However, I do need it to perform some calculations later in the code.

## Storing Number As Variable?

Jul 10, 2013

My code needs to find the last date listed in column A, which it already can do. And calculate how many days have passed between today and that date it finds in the column. Which I think my code can already do.

How do I store the number for later use?

Meaning, the next part of this code is going to send a request to a website to retrieve however many days of data that number is to be above ^. So it might be 2 days, 12 days 7 days, who knows...but the http request part will come after.

Code:

Sub date()
Dim lngLastRow As Long, lngRow As Long
Dim strColumn As String
strColumn = "A"
With ActiveSheet
lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
For lngRow = 2 To lngLastRow

[Code]...

## Storing And Organizing Data

Jul 11, 2014

I'm setting up a type of form in excel where I have some validated drop down lists and I'm trying to use a macro that will, upon a button click, take the data from the drop down lists that are entered into the boxes, and organize them into a chart on a different sheet of the document. This would be the form with some drop down lists.

Juice Type
Apple

Juice Size
8 oz.

Juice Buyer
Ronald

I want to add it in to a table with every button click filling in a new row in a table like this,

Juice Type
Juice Size
Juice Buyer

Apple
8 oz.
Ronald

## Store A Value Without Storing It In A Field

May 17, 2007

Can I store a value with out storing it in a field? -with the macro ending?

Ex macro1 runs and gets A1.value
Stores the value (Not in a cell)

I later run macro2
Gets the stored value

## Storing Autofilter Criteria In A Range

Sep 17, 2013

I would like to know that is there any way of recording criteria when I filtered data.

For example; A1,A2,A3,A4,A5,A6,A7,A8,A9,A10 and A11

I have this data such as Data,1,2,3,4,5,6,7,8,9,10 (starting from Range A2). Now when I created auto filter as

VB:
ActiveSheet.Range("\$A\$2:\$A\$11" _
).AutoFilter Field:=1, Criteria1:=Array("2", "3", "4", "5"), Operator:=xlFilterValues

And this criteria can be changed by the user anytime. Now for some reason sometimes I trigger a code which works under Function key(F8) and it refresh some data from the database. Just before it gets the data, it will remove the filter such as :

VB:
ActiveSheet.Range("\$A\$1:\$A\$11" _
).AutoFilter Field:=1

My problem is after I run my code (under F8), I want to filter back with the same criteria. Is there anyway that i record my criteria in any cell whenever I filter?

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