Geeting Background picture from the worksheet

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Vlookup Help I have an expense report I am trying to make stupid simple for my employeeâ??s. I have a worksheet called â??Detailed Expenseâ?? they fill out. They input their expenses and cost- code them. I have figured out a way to calculate the total for each code selected (probably not the most efficient way) but that works. I have another worksheet called â??Final Expense Reportâ??. This sheet has the totals for all codes in their appropriate categories. (ie: Travel, Mileage, Hotels, Meals, Airfare, Per Diem) The issue : people multiple have / work on multiple projects, they could potentially have four different cost codes for Airfare. Each cost code used and its total must be listed on the â??Final Expense Report.â?? Totaling all the airfare codes is easy. However, having each airfare code auto-populate in the final separate code totals on the â??Final Expense Reportâ?? has thrown me for a loop. I could just put all the Cost Codes on the sheet and then have the total, but I donâ??t want to put 300 Cost Codes on the â??Final Expense Reportâ??â?¦ Any help is appreciated.. Tag: Geeting Background picture from the worksheet Tag: 1006373
  - 2
    - Please help with Range.Resize I need to resize a Range from a Name object, but always receive errors. ... ThisWorkbook.Names("data").RefersToRange.Rows.Resize(10) What is the right way to do this? Tag: Geeting Background picture from the worksheet Tag: 1006371
  - 3
    - ME, BACKUP Hi, What is the command to copy everything in the folder C:\2009 to a drive G: (all subfolders should be included too) While copying, it should flash the message â??Copying â?¦â?? on the screen without showing the actual files being copied. It should be done in the background. And when it is finished, it should say â??Backup completeâ??. The messages should be larger than normal and Bold. Berj Tag: Geeting Background picture from the worksheet Tag: 1006368
  - 4
    - Variable not defined for "number of trials" ? Says variable not defined for number of trials for some reason - any way to correct this? Private Sub MyFunction() ' this variable holds the number of trials in the simulation NumberOfTrials = 500 ' start the simulation with the desired number of trials RiskAMP_BeginSteppedSimulation (NumberOfTrials) ' use a loop to run the simulation for the same number of trials For i = 1 To NumberOfTrials ' any VBA code can be run here; in this example, we do ' a simple calculation on some spreadsheet values. Sheets("Interest").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Principal").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Leasing Analysis").Select Range("A2").Select ' this call advances the simulation by one trial RiskAMP_IterateSimulationStep Next i ' when the loop is complete, finish the simulation RiskAMP_FinishSteppedSimulation End Sub Tag: Geeting Background picture from the worksheet Tag: 1006360
  - 5
    - Error when running Solver ("No return or halt function found...") Hi, When running the following Solver code, the error directly below comes up when I click on another application while Solver is running (ie - If I go to Internet Explorer while this code is running, after a few seconds, the error below comes up): "No return or halt function found on macro sheet" I then click on "OK", with is the only button within the error popup menu, and the code continues, seemingly without any damage being done. I did some research on this error, but only found an article saying that this error comes up in Excel2000 -- I, however, am using Excel2003. The article instructs that in order "To resolve this problem, obtain the latest service pack for Microsoft Office 2000". Since I'm using Excel2003, I'm assuming that the specific issue causing this error that this article refers to was resolved in Excel2003 and therefore an Excel2003 servicepack wouldn't address the issue I'm encountering -- is this a bad assumption? Any help is appreciated! Robert -- Robert Tag: Geeting Background picture from the worksheet Tag: 1006353
  - 6
    - WorkSheet_Change event This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C90C0C.0002BC90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am trying to create a simple code to place/remove a 'X' in a various = cells across a sheet IF the cell is clicked. There are about 20 individual cells: A12,A14,A16,A18.A20,A22,A24,E12,E14,E16,E18,E20,E22,E24, K12,K14,K16,K18,K20,K22,K24,Q,12,Q14,Q16, Q20,Q22,Q24. If the user selects one of the cells, then a 'X' appears. If the cell already had a 'X' in it, then the cell clears. How can i accomplish this ? --=20 Corey .... The Silliest Question is generally the one i forgot to ask. ------=_NextPart_000_0006_01C90C0C.0002BC90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.3395" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DGeorgia>I am trying to create a simple code to = place/remove a=20 'X' in a various cells across a sheet IF the cell is = clicked.</FONT></DIV> <DIV><FONT face=3DGeorgia>There are about 20 individual = cells:</FONT></DIV> <DIV><FONT=20 face=3DGeorgia>A12,A14,A16,A18.A20,A22,A24,E12,E14,E16,E18,E20,E22,E24,</= FONT></DIV> <DIV><FONT = face=3DGeorgia>K12,K14,K16,K18,K20,K22,K24,Q,12,Q14,Q16, =20 Q20,Q22,Q24.</FONT></DIV> <DIV><FONT face=3DGeorgia></FONT> </DIV> <DIV><FONT face=3DGeorgia>If the user selects one of the cells, then a = 'X'=20 appears.</FONT></DIV> <DIV><FONT face=3DGeorgia>If the cell already had a 'X' in it, then the = cell=20 clears.</FONT></DIV> <DIV><FONT face=3DGeorgia></FONT> </DIV> <DIV><FONT face=3DGeorgia>How can i accomplish this ?</FONT></DIV> <DIV><FONT face=3DGeorgia> </DIV> <DIV><BR>-- <BR>Corey ....<BR>The Silliest Question is = generally<BR> the=20 one i forgot to ask.</FONT></DIV></BODY></HTML> ------=_NextPart_000_0006_01C90C0C.0002BC90-- Tag: Geeting Background picture from the worksheet Tag: 1006342
  - 7
    - Add years, months, days to a date Excel 2003 With a worksheet function, you can add x years, y months, and z days to a date using a formula like: =DATE(YEAR(A1)+2, MONTH(A1)+3, DAY(A1)+4) [from Chip Pearson's site] What is the analogous way of doing this in VBA? DATE is apparently not a worksheet function available in VBA (Application.WorksheetFunction). The DateAdd function in VBA lets you add only one interval (years or months or days) at a time, and the Help file examples don't go beyond this. Thanks. Tag: Geeting Background picture from the worksheet Tag: 1006340
  - 8
    - Unwanted conversion from Null to Zero I am missing something... A zero value eventually shows in the spreadsheet as zero. Let me explain: my Function has a Range (a vertical vector) as a parameter. The range may contain empty cells. The range is then converted to a Variant that contains an array. The function puts all the Null (empty) values at the bottom of the array. Till here everything is fine. But in the spreadsheet, instead of empty cells I see zeroes, which is wrong. What am I missing? Function Igual2(Rng As Range) As Variant Dim Vector() As Variant Dim i As Integer, j As Integer Vector = Rng.Value i = UBound(Vector, 1) ReDim Preserve Vector(1 To i, 1 To 2) For j = 1 To i Vector(j, 2) = j Next j Vector = NullsAtTheBottom(Vector) ... Igual2 = Vector End Function Tag: Geeting Background picture from the worksheet Tag: 1006337
  - 9
    - cannot access macro in another file Hi All, In my addin, I open an excel file, then call a macro in that file. I close the file, and then open another and do the same. I repeat this 8 times with different files. Everything worked fine. I modified all of the marcos exactly the same. (added the same code) Now, one of the files fails. I get an error saying: Run-time error '1004': The macro SLOPE_MONITORING_1XX_BellPit.xls'!THisWorkbook.main()' cannot be found. My other 8 files are called _2XX_ to _9XX_ They all work fine, it is just this one. I went though everything line by line, there is no differences between any of the files. Please help, Chad Tag: Geeting Background picture from the worksheet Tag: 1006329
  - 10
    - Selecting values in sheet list box using vba Hi, A worksheet has several list boxes (values in drop down lists). Is there a way to select values in these list boxes through vba code? Regards, Raj Tag: Geeting Background picture from the worksheet Tag: 1006315
  - 11
    - why the code make the error "Overflow"? Sub MyByte() Dim b1 As Byte, b2 As Byte, i As Long For b1 = 1 To 200 For b2 = 1 To 200 i = b1 + b2 Next Next End Sub Thanks and regards. voda Tag: Geeting Background picture from the worksheet Tag: 1006299
  - 12
    - Putting named range into sub written by Tom Olgivy DataVal Hi everyone, I've found this routine written by Tom Olgivy that allows you to reset all the data validation in a column. I would like to change it to reset data val cells to blank ="" which is the 2nd option in the data val. The data val cells are in non-contiguous rows ( rows 3,5,7,9,11) on columns B to F. I've made these into a named range RngDV. I got stuck very easily because I didn't know how to replace entirecolumn in the code below Sub ResetBox() Dim r As Range Dim r1 As Range Set r = Range("B1").EntireColumn On Error Resume Next Set r2 = r.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If Not r2 Is Nothing Then For Each cell In r2 If cell.Validation.Type = xlValidateList Then cell.Value = "" End If Next Else MsgBox "No Data Validation Cells)" End If End Sub I'd appreciate any help :-) Thank you -- Mifty Tag: Geeting Background picture from the worksheet Tag: 1006295
  - 13
    - Hyperlink from the web Hi, I have copied a number of hyperlinks from the web into a table and I need to extract the web address from the copied cell. How do you do this please? Regards, Bob Tag: Geeting Background picture from the worksheet Tag: 1006293
  - 14
    - Problems with the Close statement not releasing the database fast I am trying to read and then update an Access Database using Excel VBA code. When I run the code below on a PC that has XP/Excel & Access 2003 - it works fine. When I run the same code on my home computer - which has VISTA/Excel & Access 2007 - it fails with a "Microsoft Database Engine Stopped because you and another user are attempting to change the same data at the same time" error. BUT - when I step through the code - it works fine, so the problem has something to do with the Code executing too fast for the ODBC connection??? Not sure if it is VISTA, or if it has something to do with the 2007 version of EXCEL or ACCESS. Has anyone else run into this?? And/or does anyone have a solution as to how I could slow it all down, so it works. HELP....... Dim rs As Recordset Set rs = CreateObject("ADODB.Recordset") Dim rs2 As Recordset Set rs2 = CreateObject("ADODB.Recordset") Dim rsTeam As Recordset Set rsTeam = CreateObject("ADODB.Recordset") Dim rsIce As Recordset Set rsIce = CreateObject("ADODB.Recordset") Dim sLevel As String Dim sSql As String Dim sSql2 As String Dim sSqlUpdt1 As String Dim sSqlUpdt2 As String Dim sNov As Integer Dim sDec As Integer Dim sJan As Integer Dim sFeb As Integer Dim sMar As Integer Dim sIceMax As Integer Dim sMoAvg As Integer Dim sLastDt As Date Dim sLvlCnt As Integer Dim sAlldone As Boolean Dim sTeam As String Dim sIceCnt As Integer Dim sEventStart As Date Dim sHrsDone As Boolean Dim sOrder As Integer sAlldone = False sTime = "#12/30/1899 8:0:0#" sHrsDone = False Do Until sAlldone sSql2 = "SELECT SchedDate, EventStart, Home, home_level, Event_Type " & _ "FROM Initiation_ice " & _ "WHERE home = '' " & _ "AND EventStart < " & sTime & _ " order by schedDate" & ";" With rsIce .Source = sSql2 .ActiveConnection = "ice_scheduling" .CursorType = adOpenStatic .LockType = adLockOptimistic .Open If rsIce.RecordCount = 0 Then sAlldone = True rsIce.Close Else res = rsIce.GetRows lrows = UBound(res, 2) r = 0 sIceDate = res(0, r) sMonth = Month(sIceDate) sMonthName = MonthName(sMonth) sSql = "SELECT Team, Hours_rcvd, max_hrs, hours_complete, assign_order, team_information.level, " & _ "nov_hrs, dec_hrs, jan_hrs, feb_hrs, mar_hrs " & _ "FROM team_information " & _ "WHERE hours_complete = " & sHrsDone & _ " order by hours_rcvd, assign_order" & ";" With rsTeam .Source = sSql .ActiveConnection = "ice_scheduling" .CursorType = adOpenStatic .LockType = adLockOptimistic .Open If rsTeam.RecordCount = 0 Then sAlldone = True rsTeam.Close Else res = rsTeam.GetRows lrows = UBound(res, 2) r = 0 sTeam = res(0, r) sIceCnt = res(1, r) sIceMax = res(2, r) sOrder = res(4, r) sLevel = res(5, r) sIceCnt = sIceCnt + 1 sNov = res(6, r) sDec = res(7, r) sJan = res(8, r) sFeb = res(9, r) sMar = res(10, r) If sMonthName = "November" Then sNov = sNov + 1 Else If sMonthName = "December" Then sDec = sDec + 1 Else If sMonthName = "January" Then sJan = sJan + 1 Else If sMonthName = "February" Then sFeb = sFeb + 1 Else sMar = sMar + 1 End If End If End If End If With rsTeam If .EOF Then .MoveFirst If sIceCnt = sIceMax Then .Fields(3).Value = True .Fields(1).Value = sIceCnt .Fields(6).Value = sNov .Fields(7).Value = sDec .Fields(8).Value = sJan .Fields(9).Value = sFeb .Fields(10).Value = sMar .Update Else .Fields(1).Value = sIceCnt .Fields(6).Value = sNov .Fields(7).Value = sDec .Fields(8).Value = sJan .Fields(9).Value = sFeb .Fields(10).Value = sMar .Update End If End If End With rsTeam.Close End If End With If sAlldone = False Then With rsIce If .EOF Then .MoveFirst .Fields(2).Value = sTeam .Fields(3).Value = sLevel .Fields(4).Value = "Practice" .Update End If End With End If rsIce.Close End If End With Loop End Sub Tag: Geeting Background picture from the worksheet Tag: 1006292
  - 15
    - VB in Excel Hi, Can anyone tell me about free vb code book available on internet, which can be downloaded and contain a to z about vb macro? Thanks in advance for any help. -- Dhimant Tag: Geeting Background picture from the worksheet Tag: 1006288
  - 16
    - Listbox + Multiselect + Pivots Hi, I have created a list box with Multiselect option. The list box contains the list of 12 months All - To select all months July August September October etc...... I have multiple pivots at the back end. The user has to select the option (One or more months) on the main sheet. Based on his selection for eg... July and August, the macro should trigger the change of that field in the pivot tables. I have six different pivot tables with different views. All of them should be filtered to July and August. The user has the flexibility to select any number of months or a single month. Can some please advise how to write code for this. Regards Sandip. Tag: Geeting Background picture from the worksheet Tag: 1006285
  - 17
    - Evaluating (Splitting out) a formula Hi, If there was a formula in cell A1 of the following: =offset(C1,1,1) + G1 * sheet2!A1 I would like to (using VBA) determine the number of elements in the formula. So for the above formula the evaluating would be: Parts: 3 (1=offset(C1,1,1), 2 = G1, 3=sheet2!A1) Elements: 5 (C1, 1, 1, G1, sheet2!A1) Ref Elements: 3 (C1, G1, sheet2!A1) Number Elements: 2 (1,1) Any help would much be appreciate. (Apologies for posting half complete beforehand) Best Meldrum Tag: Geeting Background picture from the worksheet Tag: 1006279
  - 18
    - Customized function Hello, I'm trying to write a simple function in VBA and to use it in the worksheet. I do the following steps: 1. Alt+F11 2. Defining the function: Function Koah(duration, percentage As Double) As Double Koah = duration * percentage / 12 End Function 3. Closing VBA But the name of the function doesn't appear in the list of "All" functions, and excel don't recognize my customized function. I tried to write a macro with this function - and it works, the problem that I need to use the function without the macro. Is it something Excel definitions? Please help, Tag: Geeting Background picture from the worksheet Tag: 1006278
  - 19
    - Evaluating (splitting out) a formula Hi, If there was a formula in cell A1 of the following: =offset(C1,1,1) + G1 * sheet2!A1 I would like to (using VBA) determine the number of elements in the formula. So for the above formula the evaluating would be: Parts: 3 (1=offset(C1,1,1), 2 = G1, 3=sheet2!A1) Elements: 5 (C1, 1, 1, G1, sheet2!A1) Ref Elements: Tag: Geeting Background picture from the worksheet Tag: 1006277
  - 20
    - Using vba coding to change text Hello, Currently I have a workbook call "Coding" that has vba coding to change certain things in a particular workbook called "Credit Adjustment" What I want to do is to change the text based on a particular text from a cell. For example in cell A7 I will have in that cell text "Mark" in cell G7 I want the text of that cell change to "Report" otherwise change the cell text to "Error". Now the rows may vary in the "Credit Adjustment" workbook in a month I may have 200 rows in another month 500 rows. Can anyone help me. Any tips or website to visit I will appreicate. Thank you in advance. Cheers Tag: Geeting Background picture from the worksheet Tag: 1006274
  - 21
    - Create a vba coding from one workbook to insert data to another workbook Hello, I have a workbox call "Coding" that has some macros via a command button that will change fonts, font color to a partictular workbook call "Montly Samples", I want to create a new vba coding via a command button that will insert in workbook "Monthly Samples" column H a particular text "Test Sample" starting from row 2 to the last row that is populated in column A. Now this workbook may have 200 rows or 500 rows in a given month so rows may vary. How do I create that coding. I just want to add the text "Test Sample". Any tips or website to visit will be appreciated. Thank you in advance. Tag: Geeting Background picture from the worksheet Tag: 1006273
  - 22
    - SOS- getting 'program error' and excel closes. DEAR ALL Excel 97: 1. file size 2.5mb bcos of lots of tanks quantities. 2. Has links to another worksheet on network file. 3. Deletes some sheets in another worksheet and replaces with todays sheets. Need to know: a) While so, in item-3, when the network file is opened, it says the file opened has links and asks if the links need to be updated. On clicking 'yes', thats it--"programme error' and on clicking on it the excel shuts down. b) When one sheet which has links is pasted in network using VB, the unfilled rows show #ref. How to get over with this #ref in pasted cell into network sheet. Pls help me and I will be watching whole day for it. brgds/captgnvr Tag: Geeting Background picture from the worksheet Tag: 1006272
  - 23
    - Statistics and Excel 2007 The June issue of the journal Computational Statistics and Data Anaylsis has published a collection of five articles on Microsoft Excel 2007. In the past, the journal has published articles critical of the accuracy of prior versions of Excel. Immediately below is a quote From the introduction to the special section, followed by the titles, authors and abstracts of the five articles. Over the years, this journal has published many articles describing the errors in Microsoft Excel, errors that raise concerns about Microsoft's quality assurance procedures, at least insofar as Excel is concerned. Hence it is perhaps worth comparing quality assurance in Microsoft's Excel to quality assurance in its game division. A recent issue of Wired magazine (Thompson, 2007) describes the way that "Bungie", a Microsoft gaming subsidiary, tests one of its products, a game called Halo3: "Because it [Bungie] is owned by Microsoft, which launches dozens XBox and PC games every year, Bungie has access to one of the most advanced game-testing facilities ever built. [Bungie has] now analyzed more than 3000 hours of Halo3 played by some 600 everyday gamers, tracking everything from favored weapons to how and where - down to the square foot - players most frequently get killed. "Bungie doesn't just test its own games this way. It also buys copies of rival titles and studies those, too, to see how Halo3 matches up." "It is difficult not to think that if Microsoft tested business software the way it tested game software, then the statistical functions in Excel would be as accurate as those found in any other major software package. If that were the case, then none of the articles in this special section would have been written." (1) "On the accuracy of statistical procedures in Microsoft Excel 2007" B.D. McCullough, David A. Heiser Computational Statistics and Data Analysis 52 (10), 4570-4578 abstract Excel 2007, like its predecessors, fails a standard set of intermediate-level accuracy tests in three areas: statistical distributions, random number generation, and estimation. Additional errors in specific Excel procedures are discussed. Microsoft's continuing inability to correctly fix errors is discussed. No statistical procedure in Excel should be used until Microsoft documents that the procedure is correct; it is not safe to assume that Microsoft Excel's statistical procedures give the correct answer. Persons who wish to conduct statistical analyses should use some other package. (2) "The accuracy of statistical distributions in microsoft excel 2007" A. Talha Yalta Computational Statistics and Data Analysis 52 (10), 4579-4586 abstract We provide an assessment of the statistical distributions in Microsoft=AE Excel versions 97 through 2007 along with two competing spreadsheet programs, namely Gnumeric 1.7.11 and OpenOffice.org Calc 2.3.0. We find that the accuracy of various statistical functions in Excel 2007 range from unacceptably bad to acceptable but significantly inferior in comparison to alternative implementations. In particular, for the binomial, Poisson, inverse standard normal, inverse beta, inverse student=92s t, and inverse F distributions, it is possible to obtain results with zero accurate digits as shown with numerical examples. (3) "Microsoft Excel's `Not The Wichmann-Hill' random number generators" B.D. McCullough Computational Statistics and Data Analysis 52 (10), 4587-4593 abstract Microsoft attempted to implement the Wichmann-Hill RNG in Excel 2003 and failed; it did not just produce numbers between zero and unity, it would also produce negative numbers. Microsoft issued a patch that allegedly fixed the problem so that the patched Excel 2003 and Excel 2007 now implement the Wichmann-Hill RNG, as least according to Microsoft. We show that whatever RNG it is that Microsoft has implemented in these versions of Excel, it is not the Wichmann- Hill RNG. Microsoft has now failed twice to implement the dozen lines of code that define the Wichmann-Hill RNG. (4) "It's easy to produce chartjunk using microsoft excel 2007 but hard to make good graphs" Yu-Sung Su Computational Statistics and Data Analysis 52 (10), 4594-4601 abstract The purpose of default settings in a graphic tool is to make it easy to produce good graphics that accord with the principles of statistical graphics. If the defaults do not embody these principles, then the only way to produce good graphics is to be sufficiently familiar with the principles of statistical graphics. This paper shows that Excel graphics defaults do not embody the appropriate principles. Users who want to use Excel are advised to know the principles of good graphics well enough so that they can choose the appropriate options to override the defaults. Microsoft=AE should overhaul the Excel graphics engine so that its defaults embody the principles of statistical graphics and make it easy for non-experts to produce good graphs. (5) "Teaching statistics with excel 2007 and other spreadsheets." John Nash Computational Statistics and Data Analysis 52 (10), 4602-4606 abstract This article considers which activities in teaching statistics may be suitable candidates for the application of spreadsheets, and whether spreadsheets in general and Excel 2007 in particular are suitable for these tasks. Tag: Geeting Background picture from the worksheet Tag: 1006270
  - 24
    - Statistics and Excel 2007 The June issue of the journal Computational Statistics and Data Anaylsis has published a collection of five articles on Microsoft Excel 2007. In the past, the journal has published articles critical of the accuracy of prior versions of Excel. Immediately below is a quote From the introduction to the special section, followed by the titles, authors and abstracts of the five articles. Over the years, this journal has published many articles describing the errors in Microsoft Excel, errors that raise concerns about Microsoft's quality assurance procedures, at least insofar as Excel is concerned. Hence it is perhaps worth comparing quality assurance in Microsoft's Excel to quality assurance in its game division. A recent issue of Wired magazine (Thompson, 2007) describes the way that "Bungie", a Microsoft gaming subsidiary, tests one of its products, a game called Halo3: "Because it [Bungie] is owned by Microsoft, which launches dozens XBox and PC games every year, Bungie has access to one of the most advanced game-testing facilities ever built. [Bungie has] now analyzed more than 3000 hours of Halo3 played by some 600 everyday gamers, tracking everything from favored weapons to how and where - down to the square foot - players most frequently get killed. "Bungie doesn't just test its own games this way. It also buys copies of rival titles and studies those, too, to see how Halo3 matches up." It is difficult not to think that if Microsoft tested business software the way it tested game software, then the statistical functions in Excel would be as accurate as those found in any other major software package. If that were the case, then none of the articles in this special section would have been written. (1) "On the accuracy of statistical procedures in Microsoft Excel 2007" B.D. McCullough, David A. Heiser Computational Statistics and Data Analysis 52 (10), 4570-4578 abstract Excel 2007, like its predecessors, fails a standard set of intermediate-level accuracy tests in three areas: statistical distributions, random number generation, and estimation. Additional errors in specific Excel procedures are discussed. Microsoft's continuing inability to correctly fix errors is discussed. No statistical procedure in Excel should be used until Microsoft documents that the procedure is correct; it is not safe to assume that Microsoft Excel's statistical procedures give the correct answer. Persons who wish to conduct statistical analyses should use some other package. (2) "The accuracy of statistical distributions in microsoft excel 2007" A. Talha Yalta Computational Statistics and Data Analysis 52 (10), 4579-4586 abstract We provide an assessment of the statistical distributions in Microsoft=AE Excel versions 97 through 2007 along with two competing spreadsheet programs, namely Gnumeric 1.7.11 and OpenOffice.org Calc 2.3.0. We find that the accuracy of various statistical functions in Excel 2007 range from unacceptably bad to acceptable but significantly inferior in comparison to alternative implementations. In particular, for the binomial, Poisson, inverse standard normal, inverse beta, inverse student=92s t, and inverse F distributions, it is possible to obtain results with zero accurate digits as shown with numerical examples. (3) "Microsoft Excel's `Not The Wichmann-Hill' random number generators" B.D. McCullough Computational Statistics and Data Analysis 52 (10), 4587-4593 abstract Microsoft attempted to implement the Wichmann-Hill RNG in Excel 2003 and failed; it did not just produce numbers between zero and unity, it would also produce negative numbers. Microsoft issued a patch that allegedly fixed the problem so that the patched Excel 2003 and Excel 2007 now implement the Wichmann-Hill RNG, as least according to Microsoft. We show that whatever RNG it is that Microsoft has implemented in these versions of Excel, it is not the Wichmann-Hill RNG. Microsoft has now failed twice to implement the dozen lines of code that define the Wichmann-Hill RNG. (4) "It's easy to produce chartjunk using microsoft excel 2007 but hard to make good graphs" Yu-Sung Su Computational Statistics and Data Analysis 52 (10), 4594-4601 abstract The purpose of default settings in a graphic tool is to make it easy to produce good graphics that accord with the principles of statistical graphics. If the defaults do not embody these principles, then the only way to produce good graphics is to be sufficiently familiar with the principles of statistical graphics. This paper shows that Excel graphics defaults do not embody the appropriate principles. Users who want to use Excel are advised to know the principles of good graphics well enough so that they can choose the appropriate options to override the defaults. Microsoft=AE should overhaul the Excel graphics engine so that its defaults embody the principles of statistical graphics and make it easy for non-experts to produce good graphs. (5) "Teaching statistics with excel 2007 and other spreadsheets." John Nash Computational Statistics and Data Analysis 52 (10), 4602-4606 abstract This article considers which activities in teaching statistics may be suitable candidates for the application of spreadsheets, and whether spreadsheets in general and Excel 2007 in particular are suitable for these tasks. Tag: Geeting Background picture from the worksheet Tag: 1006269
  - 25
    - determining the intersect of 2 lines using vba Y values - I have y-values in 2 rows of my spreadsheet, say A5:W5 and A10:W10. X values - The x-values are common to both rows, and listed in A2:W2. I would like to use vba to determine the intersection of these y- values, and determine where on the y-axis and x-axis do the lines intersect. I would like for the intercept to show up in a message box. Could someone please assist with some vba code on this "intercept analysis" Thanks in advance. V Tag: Geeting Background picture from the worksheet Tag: 1006266
- Next
  - 1
    - Where can I find documentation for the axSpreadsheet control Hello. I'm familiar with Excel application programming through VBA, but I'm trying now to add an axSpreadsheet to a C# Windows Form, and the model seems to work differently. I'm not seeing a "range" object, and the properties on things look different. Can someone point me to some documentation or resource for using the axSpreadsheet control on a Windows form? Thanks for your help Tag: Geeting Background picture from the worksheet Tag: 1006264
  - 2
    - VC use Excel? I use TypeLib include microsoft Excel 11.0 object library .Is there a way to programatically freeze panes? I want my column headers to be locked rather than scroll when scrolling the window downï¼?I do not found Excel.Window.activeWindow.FreezePanes in Cwindows or Cwindow0 class! Tag: Geeting Background picture from the worksheet Tag: 1006263
  - 3
    - How to stop ## showing when cell contents too wide sI have narrow cells in which i have formulas which return a month name when certain criteria are matched When the formulas return a true result, the month name shows, but is too wide for the narrow cell and displays as ## Is there a way to make the contents display fully. I dont mind if the cell answer covers the result from the next cell as there will always be blank results next to the cell. Can anyone help? Tag: Geeting Background picture from the worksheet Tag: 1006255
  - 4
    - web page entries Hello everyone, I am not sure where to look. this groups has helped in the past so I thought I would start here. I have a website that I need to entry part numbers and qty for several months of sales. On the web page you tab for each entry box and you can only entry ten rows at a time before you submit for validation. Each box holds different information such as, part number "b2gt, 5647,a1a" qty of "1" would be,first box "b2gt", second box "5647" third box "a1a" and forth is the qty "1". My spreed sheet has it broken down correctly. I can not copy and paste to the page, this puts everything in same entry box. I need to have it tab after each cell on the web page. Can I do this? I have been searching for a program/ software. We use some emulation software to do this on our invoices from our lookup catalog but I am not sure where the program came from. Do I use macros? If this is not the correct group I apologize in advance. Tag: Geeting Background picture from the worksheet Tag: 1006247
  - 5
    - getImage and VBA Callback Hi, First post here. I consider myself a dabbler not a programmer. So if you can help, please help gently ;-) Off an on for the past year or so I have been puzzling (sometimes fretting) over how to use the ribbon attribute getImage with a VBA callback to dispaly a custom image on a Word ribbon control. I have seen code examples using C+, .Net, VB, etc., which I don't have and don't know anything about, that make me believe that it can be done. When I try a VBA callback like: Sub GetImage(Control As IRibbonControl, ByRef image) Select Case Control.ID Case "gallery1" image = "ContentControlBuildingBlockGallery" 'This works Case "gallery2" image = "CustomeImage" 'Where "CustomImage" is a png format image file 'stored in the Open Office File format zip container images folder. This doesn't work Case Else 'Do Nothing End Select End Sub Word throws an error stating "CustomImage" is not a valid office control id. The key it seems is a process that takes a *.png format image file and converts it into a IPicture object that Word at least thinks is a valid office control id and then displays that image on the ribbon. I have ordered Ken Puls book RibbonX hoping it will provide a cookbook explanation of how this is done. I am awaiting the arrival of the book, but I would certainly appreciate learning how it is done. Thanks. -- Greg Maxey - Word MVP My web site http://gregmaxey.mvps.org Word MVP web site http://word.mvps.org Tag: Geeting Background picture from the worksheet Tag: 1006243
  - 6
    - Can I use Excel to measure time? I am doing a science fair project which involves human reaction times. Can I set Excel up to measure and record the time between two inputs (keystrokes, mouse clicks, etc...). Does Excel have a "stopwatch" function? Tag: Geeting Background picture from the worksheet Tag: 1006233
  - 7
    - Date formatiing - using code to block months together day I have a row of dates which start from =TODAY() and increment by 1 along the row giving a sequential row of dates. My question is as follows: I would like to automatically format the row below the dates to show the blocks of months for the day date row above- January, February etc The day date row of course updates each day so i would like the month row to reflect this. Is there anyway to do this? Thanks, Roger Tag: Geeting Background picture from the worksheet Tag: 1006225
  - 8
    - Network printing The code below is suppose to print a worksheet, but my problem is that it is suppose to print to a specific network printer and that printer is not always assigned to Ne09. I have seen it be anything from Ne01 to Ne10. Any way this code can be forced to look for the proper Ne##?. Unfortunately, depending on the computer from which this is run, it is not normally the default printer. This is just a snippet of the code. The macro is much more involved Sub Macro7() ' ' Macro7 Macro ' Macro recorded 8/28/2008 by wta ' ' Application.ActivePrinter = "\\printserver1\ParaT_HPLJ_3800 on Ne09:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "\\printserver1\ParaT_HPLJ_3800 on Ne09:", Collate:=True End Sub Tag: Geeting Background picture from the worksheet Tag: 1006212
  - 9
    - Modification in Recorded Macro Hi, I m trying below code Sub Macro1() Application.Goto Reference:="Sheet1!RC" Selection.Copy Application.Goto Reference:="'Sheet2'!RC" Cells.Find(What:="NOV08987", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Application.CutCopyMode = False End Sub but at "NOV08987" I want to use sheet1!A1 Kindly Thanks in advance for any tip or hint. -- India Tag: Geeting Background picture from the worksheet Tag: 1006209
  - 10
    - Check whether text in cell contains "?" Hi, How can I check with a macro script if the text in a cell contains a question mark? Thanks in advance for any tip or hint. Hans Tag: Geeting Background picture from the worksheet Tag: 1006202
  - 11
    - Image Control - MouseMove Event Hi all, i am looking for macro code that when i take curser on image control it should show picture1 and as soon as i put my curser away from image control it should show picture2. i saw few examples like putting label control over image control etc but its not working for me. I am goint to create a spreadsheet which will have many image controls and i want them to change pictures when user move curser on them. Can any friend help me on this. thanks Tag: Geeting Background picture from the worksheet Tag: 1006200
  - 12
    - what does the operator ":=" mean? in Office VB code: what does the operator ":=" mean? example: Worksheets("Daily Targets").Range("A5:N5").Copy _ Destination:=Worksheets("Daily Targets").Range("A" & CStr(rowCounter)) i wrote this code but i copied the it from the HELP, & then i changed the names & variables. BUT, i'd like to understand what does the operator := is filling for, is it an object assigning or what ... i'm not sure ??!!! regards, Michael Tag: Geeting Background picture from the worksheet Tag: 1006194
  - 13
    - Making a cell a color using color numbers Is it possible to enter a color number (1-56 for example) and make that cell the color in question? I am guessing conditional formatting might be a way, but i think some code may be needed. Can anyone help? Thanks, Roger Tag: Geeting Background picture from the worksheet Tag: 1006187
  - 14
    - Matching values in different columns/workbooks Hi, The code below loops through each cell in one column of data and finds the same value in another column (on another spreadsheet). This works fine for a 100 cells, but the final version will check 600 rows in seven worksheets, and this takes an age. How can I do this more efficiently? Would it be better to use an array (not sure how to do this), and I assume that when the correct value is found, the remaining cells are still searched? Could I use a function which finds the match and so skips checking the rest of the column? For Each r In Workbooks("02 - Scheduler Plan.xls").Worksheets(SchedulerDayName).Range("B2:B100").Cells If IsNumeric(r.Value) Then For Each c In Workbooks("00 - MDS.xls").Worksheets(MDSDayName).Range("C4:C1000").Cells If c.Value = r.Value Then c.offset(0, 2).Resize(1, 145).Copy r.offset(0, 4).PasteSpecial xlPasteValues End If Next c End If Next r hope you can help. regards, Matt Tag: Geeting Background picture from the worksheet Tag: 1006177
  - 15
    - UBound function does not work I have defined two matrices InMatrix1 and InMatrix2 on a spredsheet by names. The programme does not execute. When I type UBound in the VBEditor, there is no prompt for the UBound word after I press shift+spacebar. What is the problem? Function MatrixMult(InMatrix1 As Variant, InMatrix2 As Variant) As Variant 'generic matrix multiplication engine, pure BASIC code Dim OutMatrix() Dim i As Integer, k As Integer Dim j As Integer Dim Sum As Integer ReDim OutMatrix(1 To UBound(InMatrix1, 1), 1 To UBound(InMatrix2, 2)) Application.Volatile 'force recalculation with spreadsheet For i = 1 To UBound(InMatrix1, 1) 'rows of inmatrix1 For j = 1 To UBound(InMatrix2, 2) 'cols of inmatrix2 Sum = 0 For k = 1 To UBound(InMatrix1, 2) Sum = Sum + InMatrix1(i, k) * InMatrix2(k, j) Next OutMatrix(i, j) = Sum Next Next MatrixMult = OutMatrix End Function Tag: Geeting Background picture from the worksheet Tag: 1006176
  - 16
    - How to check one cell value to diffrent cell How to check one cell value to diffrent cell and check the condition and if fulfils the condition than return a value from a different cell to the cell we Started comparing Tag: Geeting Background picture from the worksheet Tag: 1006173
  - 17
    - Could someone review my first Excel VBA program? Hi everyone.This is my first attempt at coding in VBA for Excel. If someone could give any tips about my code, I'd really appreciate it. In a nutshell, this code reads a source spreadsheet of about 35,000 rows and pulls each sales manager out into a template file which contains a pivot table. The template is then saved as a spreadsheet with that manager's name. The code works, but I'm sure there's something that can be improved. I thought it might be less memory intensive if I could copy over an entire row into the template sheet. But every time I tried writing code for that, I kept getting errors preventing it from running. Thanks. Dim varSourceRow As Integer 'Defines which row to read data from source file Dim varTemplateRow As Integer 'Defines which row to write data in template file Dim currentMgr As String 'Defines which manager is currently being read Dim pivotRange As String 'Defines data range for Pivot Table in template Dim varSourceLen As Integer 'Defines row count of the source file 'Turn off screen updates Application.ScreenUpdating = False 'Load the source data and template data into memory Set sourceWB = Workbooks.Open("C:\Documents and Settings\Test \Desktop\ReportTest\Source.xls", True, True) Set templateWB = Workbooks.Open("C:\Documents and Settings\Test \Desktop\ReportTest\Template.xls", True, True) 'Sets sales manager for first loop currentMgr = sourceWB.Worksheets("Source Data").Cells(2, 3) varTemplateRow = 2 varIteraion = 2 varSourceLen = sourceWB.Worksheets("Source Data").UsedRange.Rows.Count For r = 2 To varSourceLen 'If the row in the source document has the current sales manager If sourceWB.Worksheets("Source Data").Cells(varSourceRow, 3).Value = currentMgr Then For clNumber = 1 To 16 'For each column copy over the cell data to the template file templateWB.Worksheets("Sheet1").Cells(varTemplateRow, clNumber).Value = sourceWB.Worksheets("Source Data").Cells(varSourceRow, clNumber).Value Next clNumber varTemplateRow = varTemplateRow + 1 intCount = intCount + 1 'If the row in the source document does not have the current sales manger Else 'Define the pivot table range pivotRange = "Sheet1!R1C1:R" & varTemplateRow - 1 & "C16" 'Refresh the pivot table with the new data templateWB.Worksheets("Pivot").PivotTables("PivotTemp").ChangePivotCache templateWB.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pivotRange) templateWB.Worksheets("Pivot").PivotTables("PivotTemp").RefreshTable 'Save the file using the sales manager name templateWB.SaveAs "C:\Documents and Settings\Test\Desktop \ReportTest\" & currentMgr & ".xls", FileFormat:=56 currentMgr = rawWb.Worksheets("Source Data").Cells(varSourceRow, 3).Value 'Clear the contents of the template file for the next cycle and reset the template row count pivotRange = "A2:P" & varTemplateRow templateWB.Worksheets("Sheet1").Range(pivotRange).ClearContents varTemplateRow = 2 End If Next rwNumber 'Once complete close the original files and remove variable values sourceWB.Close False templateWB.Close False Set rawWb = Nothing Set templateWB = Nothing Application.ScreenUpdating = True Tag: Geeting Background picture from the worksheet Tag: 1006170
  - 18
    - excel 97, VB project unable to view in another comp Dear ALL I locked the vb project with the password protection in excel 97. I could load this saved worksheet in another comp and could see the vb project by entering the password. But on one computer running the same excel 97, when i click on the project I get "project is unviewable" instead of asking for "password". What could be this reason and how to solve as I need to open the project in that computer and do the final coding for local paths etc. Pls help br/captgnvr Tag: Geeting Background picture from the worksheet Tag: 1006169
  - 19
    - MVP Question Why have you ignored the question "Precedents for formulas" dated 8/29/08? We need you. Tag: Geeting Background picture from the worksheet Tag: 1006165
  - 20
    - Array Copy - Paste Special Add Help Here is the code I'm working with: Basically what this allows me to do is copy and paste multiple selections, but I want to make one tweak to it. I want it to copy, and them paste special add. I believe the line of code that is hanging me up is this: SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset) I'm not sure how to modify it to accumulate another section on top of it, and not just paste over it. So what I want this to do, is just keeping adding the same range together as many times as I run the macro. Anyone want to take a stab at it? Thanks, Tyson Option Explicit Sub CopyMultipleSelection() Dim SelAreas() As Range Dim PasteRange As Range Dim UpperLeft As Range Dim NumAreas As Integer, i As Integer Dim TopRow As Long, LeftCol As Integer Dim RowOffset As Long, ColOffset As Integer Dim NonEmptyCellCount As Integer ' Exit if a range is not selected If TypeName(Selection) <> "Range" Then MsgBox "Select the range to be copied. A multiple selection is allowed." Exit Sub End If ' Store the areas as separate Range objects NumAreas = Selection.Areas.Count ReDim SelAreas(1 To NumAreas) For i = 1 To NumAreas Set SelAreas(i) = Selection.Areas(i) Next ' Determine the upper left cell in the multiple selection TopRow = ActiveSheet.Rows.Count LeftCol = ActiveSheet.Columns.Count For i = 1 To NumAreas If SelAreas(i).Row < TopRow Then TopRow = SelAreas(i).Row If SelAreas(i).Column < LeftCol Then LeftCol = SelAreas(i).Column Next Set UpperLeft = Cells(TopRow, LeftCol) ' Get the paste address On Error Resume Next Set PasteRange = Range("H10") On Error GoTo 0 ' Exit if canceled If TypeName(PasteRange) <> "Range" Then Exit Sub ' Make sure only the upper left cell is used Set PasteRange = PasteRange.Range("A1") ' Copy and paste each area For i = 1 To NumAreas RowOffset = SelAreas(i).Row - TopRow ColOffset = SelAreas(i).Column - LeftCol SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset) Next i End Sub Tag: Geeting Background picture from the worksheet Tag: 1006149
  - 21
    - VBA Clear Screen I am trying on a VBA form to clear the screen leaving blank variables. The screen does clear fine. The problem I am running into is that one of the fields being cleared is the product list selection. After the clear screen, the product list add the list of products to the existing list so the products are now displayed twice. Clear the screen again and the product list is displayed three times. Any ideas on why this is happening. Private Sub ClearButton1_Click() Prqty.Text = "" PrComboBox.Value = "Select a Product" PrMSRP.Text = "" Prwholesale.Text = "" Pronline.Text = "" End Sub Doobage Tag: Geeting Background picture from the worksheet Tag: 1006143
  - 22
    - How to get all info on a specific name from diferent worksheets t I have a worksheet that has names of managers. I need to open a new worksheet that can merge all the info on one specific manager from any cells/ other worksheets. this is what I have on the columns name of manager and comments A B C I need to open separate tabs that can tell me all the commets that manager "A" said all coments manager "b" said and so on. Can any one help me? Thanks -- ll Tag: Geeting Background picture from the worksheet Tag: 1006134
  - 23
    - Working with a Loop I can't figure out why this double loop does not work... For X = 1 To 9 For T = 1 To 11 range("SP[DATE]").Select Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Offset(0, 1).Activate CURRPERCENTAGE.Offset(X, 0) = ActiveCell Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1) T = T + 1 MsgBox T Next Next If you have any idea, it would be greatly appreciated..I have tried everything... Tag: Geeting Background picture from the worksheet Tag: 1006128
  - 24
    - How to print the values of cells Hi All Below code compares and gives the result in numbers.What I want is instead it gives number like 2 or 3 it should gives the names.So how can I print the contents of the cells after it does sum. Thanks for your help Sub HCL() Dim self As Workbook Set self = ActiveWorkbook Dim Table1 As Worksheet Dim NetworkDrivers As Worksheet Set Table1 = self.Worksheets(1) Set NetworkDrivers = self.Worksheets(2) Table1_usedRow = Table1.UsedRange.Row + Table1.UsedRange.Rows.Count - 1 NetworkDrivers_usedRow = NetworkDrivers.UsedRange.Row + NetworkDrivers.UsedRange.Rows.Count - 1 NetworkDrivers_usedCol = NetworkDrivers.UsedRange.Column + NetworkDrivers.UsedRange.Columns.Count - 1 For i = 2 To NetworkDrivers_usedRow drivers = UCase(Trim(NetworkDrivers.Cells(i, 1))) For j = 2 To NetworkDrivers_usedCol project = UCase(Trim(NetworkDrivers.Cells(1, j))) Sum = 0 For c = 2 To Table1_usedRow If UCase(Trim(Table1.Cells(c, 8 )) = drivers Then If UCase(Trim(Table1.Cells(c, 9))) = project Then Sum = Sum + 1 End If End If Next c NetworkDrivers.Cells(i, j) = Sum Next j Next i Tag: Geeting Background picture from the worksheet Tag: 1006116
  - 25
    - Formatting cell poulated by User Defined Function I have a UDF that goes off and retrieves various pieces of data from a database based on parameters that the user enters into the UDF. Some of the data in the DB are strings, and others are Integers or Doubles. My UDF returns a string so that any of those values can be displayed (i.e. ints and doubles from the DB are converted to string in my method that calls the DB). Public Function MyUDF(param1, param2) As String If a value coming back from the database is the number 3.51, the UDF returns it to the calling cell as "3.51". If I try to format this in Excel and ask it to display one decimal point, for example, it does nothing because it treats 3.51 as a string. If I wrap the call to the UDF with =VALUE, that works, but I wanted to avoid having the user have to do this. I also wanted to avoid having seperate UDFs for each return type (i.e. one that returns String, one that returns Integer, etc.) Does anyone have any suggestions? Tag: Geeting Background picture from the worksheet Tag: 1006115

Hi,

I have applied background picture for the sheet. I would like to check
whether sheet has background or not.

How do I do it using excel object model in c#.

Sheela

Top