Worksheet Calculate() And Static Variables
Apr 24, 2006
I have a spreadsheet with a real-time data link in cell A1 and I use Worksheet Calculate() to paste the new value, on any update,below cell A1. Once I have 10 values from A2 down to A11, I over-write the first one, so that I have the 10 most recent. From this I calculate the moving average.
Anyway, I would like to be able to do this without the pasting bit ie storing the most recent ten data points in a variable say x(i) and just averaging x(1) to x(10).
I'm guessing I need to use some kind of static variable or array, but I don't really know how to do this.
View 9 Replies
ADVERTISEMENT
Jan 20, 2007
problems with Global Static Variables.
In the Workook_open Sub I want to define 2 global static Variables.
For some reason I can't get it to work.
Whenever I try to access these vars they have the value "0"
I use Excel 2000.
Here is what i did:
Dim i As Integer
Dim ws As Worksheet
Public Static GlobalStartX As Integer
Public Static GlobalStartY As Integer
Private Sub Workbook_Open()
GlobalStartX = 6
GlobalStartY = 21
End Sub
View 4 Replies
View Related
Sep 30, 2013
Is there a way that formula links in spreadsheets can use environment variables to reference formulas in other spreadsheets/addins?
For example, I have a spreadsheet that imports templates which call functions in an addin. The spreadsheet, templates and the addin could be installed into either the 'Program Files' or the 'Program Files (x86)' folders.
I am experiencing issues with the formula references when moved between 32 and 64 bit computers since Excel seems to store the full path of the addin in the Excel formula (even though it hides it when the reference resolves its path) in my templates. I would like to update the references in my templates so that they use an environment variable (set by my software installer) to always know the correct path of the addin.
In Excel 2003, spreadsheet links are edited via: 'Edit'->'Links'
In Excel 2007 and higher,'Data'->'Edit Links'
View 1 Replies
View Related
Oct 7, 2013
I have a dataset that will be updated frequently and dumped into cells A1:D### that would look like:
ID Week Sales Rate
1 1 $200 3 0.5
1 2 $200 3 0.3
1 3 $200 3 0.2
1 4 $200 3 0.5
...
The formula i'd like to calculate in Column E Variable = "Forecast" Formula =IF(B2
View 2 Replies
View Related
Jun 26, 2014
I am looking for a syntax to ask a question with three variables
As an example
If
Blade size = 114 (only 3 blade size choices)
and
the Panel Size is between 500 and 700 ( This will also be dependant on the blade size)
and
the tilt bar is clearview (Three choices)
then
the result will be x = 182
The answers will depend on the above variables: 154, 159, 179, 177, 182 and 202
Screenshot 2014-06-26 14.21.41.png
Screenshot 2014-06-26 14.23.53.png
Screenshot 2014-06-26 14.26.11.png
View 3 Replies
View Related
Jun 3, 2009
I'm working on a time series dataset with a time step of 15 minutes. I need to calculate daily average of the several variables. So let column "A" be the "date-time" column, let column "B" be the "variable column" and column "C" be the "average column", I need a function that calculated in C1 cell the average of B1 to B95 cells, in C2 cell the average of B96 to B190 cells, in C3 cell the average of B191 to B285 etc.
View 3 Replies
View Related
Jul 27, 2009
In column H: =IF(ISERROR(L4-J4-K4),"",IF(OR(K4="0", K4=""), L4-J4, L4-J4-K4))
So, if formula is going to come up with an error, I don't need to know. If it's not an error: L - J - K. Except that sometimes there isn't information in J or K, and the formula won't calcluate L - 0 - 0, unless I physically enter in zeros.
=IF(ISERROR(L4-J4-K4),"",IF(K4=""), L4-J4, L4-J4-K4))
and
=IF(ISERROR(L4-J4-K4),"", L4-J4-K4))
I don't want the people using this to have to enter in a plethora of zeros in order to force the calculation. What am I doing wrong? I've attached workbook with the relevant information in it. You can see in the middle that if there is nothing entered, it won't work right.
View 5 Replies
View Related
Sep 30, 2009
In the file I have attached, I need to work out how to give the final total (cell G27) inclusive of VAT, dependent on the value assigned in cells K17-K26, which are explained in the list starting cell B29.
At the moment the value given is net of VAT, and I need the total figure in G27 to recognise that the first item needs to be at 15%, the second at 5% and the 3rd at 0%.
View 13 Replies
View Related
Aug 12, 2006
With Wb.strMyBookINT
Set S70Wscopy = Sheets("s70 pivot data") 'set s70 pivot data sheet for kpi 44 s70 pivot data
Set IMFWscopy = Sheets("imf pivot data") 'set imf pivot datasheet for kpi 44 imf pivot data
End With
With Wb.strMyBookEXT
Set IMFEXWscopy = Sheets("imf ex") 'set imf ex sheet for kpi 15 imf ex
End With
correct syntax to set these sheets?
View 9 Replies
View Related
Feb 3, 2014
I am trying to create an array in VBA with a loop, where new variables are assigned each time the loop starts. These variables are then used in calculations and the final answer found by using Solver. I have managed to get this working by setting specific cell references, but I want to do it without interacting with the sheet and populate and array.
I am not sure how to use the variable, eg. x in the place of the SetCell:=Range("A1") part.
So to put it clearer, I want to substitute the Range references with variables x and y from my VBA variables, without interacting with the worksheet to get values.
View 5 Replies
View Related
Jun 6, 2014
How to assign a "WS(i)" worksheet name and have the assigned variable (i) in the name equal the consecutive steps in a total page count.
Here's the code that does not work:
Code:
Dim WB as Workbook
Dim WS as Worksheet
Set WB = ThisWorkbook
For i = 1 To WB.Sheets.Count
Set WS(i) = WB.Sheets(i)
Next
I can see why it does not work. I can't figure out what to declare in my Dim or how to word the statement using the (i) variable in order to capture the value to attach to "WS."
Details: The issue is that the number of pages created will vary day to day depending on staff needs. There's an array of staff names to be assigned to page tabs and there will be skips and deletions along the way depending on daily attendance so "WS(i)" worksheet names can't be assigned at creation. What happens if they are is that the succession of worksheet names winds up being (e.g.) "WS1, WS2, WS5, WS12," etc. instead of consecutive.
There's a very complex data-sort-and-assign system that depends upon how many staff-assigned sheets there are, and as such each sheet has to have a "WS" worksheet name for the process to stay smooth. If the names are not consecutive, though, the sort-and-assign process becomes confused. This step takes place at the end of the initial workbook set up process, immediately before the data are addressed, so there really isn't an earlier opportunity. It can't be later since from this point on the system depends upon the "WS(i)" names being there.
It started fine when there were 5 people. Now there are 37.
View 2 Replies
View Related
Apr 5, 2007
passing variables within procedures on a worksheet. i have:
Private Sub lblGoToMaterials_Click()
'email to:
'----------------
'grab current position
myRow = ActiveCell.Row
mycol = ActiveCell.Column
and i would like to pass the values gotten in myRow and mycol to:
Private Sub cmdGoBackTo_Click()
Application.Goto Reference:="R" & myRow & "C" & mycol
View 3 Replies
View Related
Dec 22, 2011
Using Excel 2007, I'm trying to figure out (and not succeeding!) on how to declare worksheet and range - in order to:
input a formula to cell xfd1, then copy down to xfd2:xfd100 - and here's what I've been playing about with
Code:
Sub filldownxfd()
Dim src As Range, out As Range, wks As Worksheet
Dim sRangeName As String
Workbooks.Item(1).Sheets.Item ("Sheet1")
Dim example As Range
Set example = Range("xfd2:xfd100")
[code]....
View 2 Replies
View Related
Feb 5, 2009
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
View 2 Replies
View Related
Apr 14, 2014
The situation is a Workbook_Activate macro is conflicting with a worksheet module which is a worksheet_calculate that also runs a macro when there is a query refresh from another book. How do I get the workbook activate macro to run before the worksheet one activates? Right now everything crashes.
View 1 Replies
View Related
May 23, 2006
A long time i have not been practising VBA so i am looking for a little refreshment. I am having a workbook with a random number of worksheet with different name that the user can define as he wants. My calculation option is set-up on automatic so each time i make a change all my formulas are updated. However i want the user to be able to disable the calculation of a specific worksheet that he would chose throught a list. Basically the calculation will remain automatic but the selected worksheet will not be updated when a change occur.
View 2 Replies
View Related
Feb 26, 2009
i can place a tick box on a worksheet that will turn auto calc on/off? The same exact as going through the tools menu but for a lazy person?
View 2 Replies
View Related
May 28, 2006
Calculate the number of rows used in sheet. for exa: i have A1:A100 i want that B1=100 and if i delete some of rows the number of rows in B1 auto-change. and can let B2 display the number of rows deleted.
i have 2 sheets now, can the Cell C1 in sheet 2 display the number of rows in the sheets, B1 in sheet1 + B1 in sheet 2
View 9 Replies
View Related
Oct 3, 2013
I have a workbook with about 34 worksheets (one for every day plus a few extras). I have a summary sheet which collates all the data from each other worksheet. I have required a "MAX IF" type array formula on this summary sheet which slows everything down.
Is it possible to have the whole workbook in automatic calculation, but set the 'Summary' sheet to manual calculation when I press F9 or click on a button?
View 4 Replies
View Related
Feb 15, 2014
I have the following code that I would like to trigger when cells B26:U26 change to something other then 0 due to a formula? How was I make this happen?
[Code] .....
View 4 Replies
View Related
Jan 30, 2009
I am trying to use the worksheet calculate event to automatically change the color of a cell only when that particular cell changes. In E2 of the worksheet is a formula use to determine rating based on the result of 2 other cells. The rating is classified as follows
Low
Moderate
High
Maximum
I would like to generate a different set of color to the cell and fonts for each of the rating. For example,
"Cyan" to the cell E1 and E2 with Black font if the result is "Low"
"Plum" to the cell E1 and E2 with "Black font if the result is "Moderate"
"Blue" to the cell E1 and E2 with "White" font if the result is "High" and
"Red" to the cell E1 and E2 with "White" font if the result is "Maximum"
View 9 Replies
View Related
Jul 27, 2006
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
View 2 Replies
View Related
Mar 21, 2014
How do i Keep shape where it is no matter if i scroll up or down.
Sub KeepshapeStatic()
Dim ws As Worksheet
Dim shp As Shape
Set ws = Sheets("Sheet1")
Set shp = ws.Shapes("MyShape")
'How do i Keep shape where it is no matter if i scroll up or down
End Sub
View 6 Replies
View Related
Feb 17, 2014
I am trying to create a For Loop where the RND from array column sptemp(i,2) is static throughout the entire loop when I pull the data out separately by columns to create a chart. The data works perfectly if I print it out on the excel spreadsheet via ctrl+shift+enter as an entire array but I am trying to graph the columns and the RND is updating and not syncing with the same random number, as sptemp(i,3) and sptemp(i,5) are both linked to sptemp(i,2) which is the RND. I am trying to graph column 3-5 on the Y-axis and column 1 on the X-axis, and column 3 and 5 should look identical after subtracting column 4.
View 1 Replies
View Related
Dec 7, 2006
what im creating is a live odds program for NFL football... and in my workbook I have ALL the teams as individual sheets.
The problem im having is say im looking at the calculations for Pittsburgh on the pittsburgh sheet, and my macro decides to update.
What it does is pulls me away from the pittsburgh sheet and goes through all the updates for all the teams on all the sheets. By the time its updated all the odds for all the sheets and running and highlighting with the other macros I have to click back to Pittsburgh and view again for like 20 seconds.. and away she goes again updating the sheets.
Is there any substitute for "select" in the programming where it will not select the sheet.. but just update it "behind the scenes" ?? making the page static and remaining viewable while it runs all the processes in the background??
( yep im pretty new at all this, but i have a great method for calculating games and winners each week, but probably better without looking for 20 seconds waiting, clicking back to the sheet and looking for another 20 seconds .. etc etc .)
View 11 Replies
View Related
Jun 18, 2009
I have found threads that explain that if you want a to use the now() or today() function but make it static, you have to put a code in the worksheet relating to it.
I have read quiet a few threads, but i can't seem to make my code work.
I need to format column A to have the date format in it. I have a event procedure that puts Now() when run in Column A and I think i need to add the following code to the worksheet to make it static.
View 2 Replies
View Related
Aug 18, 2009
I have the following code that places a formula into the cell.
View 5 Replies
View Related
Oct 3, 2011
I have multiple VBA modules that all refer to directories on the local machine and across the network.
I am currently defining the directories in each module.
How can I define them once and then refer to them in any module?
Set Word_Template_Name = "Sanitary Survey Template.dotx"
Set Excel_Template_Name = "Sanitary Survey Blank.xlsm"
Set WQCDdir = "c:CO Sanitary Survey"
Set WQCD_Source_Dir_File = "...wqcProgramsSDWEngineeringTemplates"
This would ensure that any directory changes could be made once rather than 5 times.
View 1 Replies
View Related
Jan 30, 2012
I have a formula in column A, =AVERAGE(C2:Z2). I have a macro that moves columns C to Z over one column to the right every day. How do I keep the above formula the same as it is now. At present the range also moves one column. I have added $ but it still changes
View 5 Replies
View Related
Mar 13, 2012
I have developed an excel template that is used at our fire department for dispatching purposes. The template is loaded and then info is placed into the template. It is a read only file so I am looking to have it save the file name as "Incident Report ()" replacing the bracket will be the value of cell C1. Example would be "Incident Report 234". I would also like it to save in the same file location every time.
View 6 Replies
View Related
