VBA read a value and not a formula

Visual Basic for Applications (VBA)

‹ Microsoft ISV
- Archive
  - SharePoint
  - VC++
  - Visual Studio
  - Visual C#
  - VFP
  - ISV
  - VB
  - Windows Forms
  - VS 2008
  - SQL Server
  - Dotnet
  - Windows Vista
  - Game
  - VS Express
  - Smart Device
  - IE
  - Windows Live
  - Architecture
  - Office

- Previous
  - 1
    - VBA and automated word generation Hi there, I am currently working on a VBA application. There is an automated word generation available in the appliacation that serves the reporting process. If there is any Flash add-in being installed in the user's machine (can be viewed through Tools->Templates and Add-ins in MS Word) ,the applicaiton crashes down when the user tries to generate the report because the flash add-in runs initially before the word gets opened. Is there any work around available other than removing the add-in for the application to run successfully Your help is greatly appreciated Tag: VBA read a value and not a formula
  - 2
    - Using Excel files in MS Works Had Office and XP on my previous computer and created several Excel files I want/need to keep New computer has Vista and MS WORKS installed - will I have a problem with transferring the Excel files to new computer If so, how do I remedy Tag: VBA read a value and not a formula
  - 3
    - iexplore.exe - Application Error I discovered the problem. It was because of Yahoo Toolbar add on. I uninstalled it and now it works fine. Marty Tag: VBA read a value and not a formula
  - 4
    - error with wscript.shell error 80070483 hi, i am trying to run a vb script from a VBA module in excel. fn = "test.vbs Set wshShell = CreateObject("Wscript.Shell") wshShell.Run (fn) in the line wshShell.Run (fn) i obtain an error error 80070483 method Run of object IWshShell3 failed it is the first time i do this, so i am not shure if i need to have done something before, some references in windows extension .vbs is not related to any application (is it ok ) tks in advance Tag: VBA read a value and not a formula
  - 5
    - Obtain Unique Work Units via SQL Adamus, I am still getting 197 for total WorkUnits. Code Snippet SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units], 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals], FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults] FROM WorkUnitsFaultsMainTBL WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt)); What about in line four in the code above where I am dividing with /Count([WorkUnit]),2) AS [Pct NoFaults] Could this be causing the WorkUnits field to be total records and not distinct WorkUnits Tag: VBA read a value and not a formula
  - 6
    - Adding Menu to Microsoft project 2007 using VBA How can I add a custom menu using VBA code. There are some changes happened in from Microsoft Project 2003 to Microsoft Project 2007. How can I add custom menu to microsoft project 2007 and make it work to invoke appropriate code. Are there any whitepaper which talks about the changes in the coding from Microsoft project 2003 to 2007. I am new to this area of coding with little knowledge of VBA. ANy help will be highly appreciated. A sample code will work wonders for me. Tag: VBA read a value and not a formula
  - 7
    - compile error: invalid quaifier Hi! I'm a portuguese school teacher that uses vb 6.0. The error mentioned occurs here **: Public Sub introduz() i = 0 Do While i <= 20 VF.Nr(i) = Val(InputBox("Introduza o numero de cliente", "Numero de cliente")) ** VF.NomeCliente(i) = InputBox("Introduza o nome do cliente", "Nome do cliente") If VF.NomeCliente(i) = ("XYZ") Then Exit Do End If VF.Dt(i) = InputBox("Introduza a Data da Factura", "Data da Factura") i = i + i Loop End Sub That's an array defined with public statement that uses an type structure also defined with public statement. Can anyone help me My students say thanks!! Tag: VBA read a value and not a formula
  - 8
    - Collection Item: Parent / NextItem Hi, I am struggling with classes. This is what I want to do, in VBA (test it in Excel please): I want to add a property/function to my class, named NextItem and PreviousItem. These return the next and previous items in the collection. I have several separate collections where I keep my items in. So if I want to determine the next and previous item, I need to figure out in which collection the item resides before I can start to loop through that collection. Unfortunately there is no collection.item.parent. That is what I need to simulate I thought about adding a property ParentCollection to the class, but how can I use that property as a pointer to a collection and retrieve the collection from that pointer. Help! Tag: VBA read a value and not a formula
  - 9
    - VBA Language Definition Hi, just a quick question Does there exist anywhere a formal definition of the VBA language It would be really useful for my dissertation as i am writing a program that interprets macros so could do with something that would tell me, for example all the different ways that variables can be defined, or all the different types of statement that can be used etc. Any advice is much appreciated Ryan Tag: VBA read a value and not a formula
  - 10
    - Are these valid bucket ids ? DHON, You are running into trouble here because you are trying to mimic an event type. If I understand the problem you are really trying to solve, it’s getting your application’s crash reports on Winqual. All you need to do is map your products on Winqual using the Product Feedback Mapping Tool , and nothing else. As long as your application is not trying to handle its own exceptions, you will get a WER report for your application crashes by default. When your exceptions bubble up the UEF in Windows, Windows Error Reporting is invoked. Depending on the type of crash (Application Hang, 32bit crash, 64bit crash, Buffer Overrun, etc), Windows Error Reporting will create your event type for you as well as handle creating a ‘crash signature’ for the event used to report the event to Microsoft. Examples of these common crash eventtypes that are already built into WER are ‘Crash32’ for an application crash on a 32bit machine, ‘Crash64’ for an application crash on a 64bit machine, ‘BEX’ for security related events such as Buffer Overrun, and Data Execute Prevention issues, and Safe CRT exceptions to name a few. The event type you are mimicking here (PCA2) is used by a service that handles product compatibility checking and reports on failed installation attempts. Mimicking an event will not help you because …not all of these events (such as PCA2) are not exposed through Winqual and you need to successfully map on the valid signature type during a report. There is no reason to create a custom event for a crash or a hang because you get that event report for ‘free’. You don’t have to do anything. Custom events are really for non-fatal events, and require a registration with Microsoft (a manual process today by contacting WER@microsoft.com ). I hope this helps answer your question. Kind regards, -Jason Tag: VBA read a value and not a formula
  - 11
    - VBA 6.5 New Version? I have loaded Office 2007 and the VBA about screen states version 6.5. Is there any documentation on recent changes to the VBA environment An older product which includes the VBA environment is not able to launch any VBA macros with Office 2007 installed. The upgrade to VBA 6.5 may be the problem and I would like to find some detailed information on new releases. Thank you, Greg Tag: VBA read a value and not a formula
  - 12
    - Updating column totals in sub forms This has to be simpler than I'm making it! Trying to maintain column ototals ni SubForm, and update them to the MainForm. Typical Northwind-type Orders-&-Details relationship: I extend row totals in the OrderDetails subform; and show OrderTotal (column total) in the SubForm's FormFooter. So far, so good. But when I change a quantity or price in the subform, I'd expect the subtotal to change automatically, no It does not; and I don't know whether I need to code a "Change" event myself, to modify that total whenever a single cell changes; and whether I must loop through the rows, or if there's a nifty group-operator in Access to do the columnTotal for me. (I'm a VB programmer, reluctantly roped into writing an Access form.) Finally, when I leave the Main Record (i.e., parent of the rows in the subForm), I want to store the updated suptotals in the Main/Parent record. (I know, I know, it's not kosher, but they pay me to do this stuff. -- Besides, it records what has been billed as it was billed; not as it's been updated since.) Many thanks, anyone who can provide a cogent reply. S Tag: VBA read a value and not a formula
  - 13
    - AutoExec will not execute properly your findings are all correct. In the AutoExec I have now added a small delay as shown Sub AutoExec() ' ' macro is run when AddIn is read in ' the only job is to set the entry in the menubar ' ' we have to wait until Word has done its job Application.OnTime When:=Now + TimeValue("00:00:03"), _ Name:="start_macro" End Sub ' AutoExec Note: the start_macro is as follows: Sub start_macro() show_warning = True Call initialise_program Call check_if_vpn(False) ' check if connection to server exists End Sub with this delay the entries in the menubar show up correctly. The sub initialise_program is identical in Excel and PowerPoint and executes correctly also on those PC's where the Wordmacro does not. Tag: VBA read a value and not a formula
  - 14
    - MS Office document and image writer print driver Thank you! Tag: VBA read a value and not a formula
  - 15
    - Fill word document with fields from Access form programmatically Hi - i have a form called 'formpo' that allows users to enter information that we use for purchase orders such as date, order value, supplier name, purchase description, etc etc. when the user has filled in the information on the form i would like them to be able to click a button on the form screen that will take the values from the form and insert them into a specific word document, and have the values entered into a specific place on the word template. There is a lot of stuff out there but its not the best and its mainly about mailmerging it which i dont want to have to do because its a right pain in the proverbial behind. Has anyone done this before Many thanks, Rhys. Tag: VBA read a value and not a formula
  - 16
    - Projects, objects & modules Thanks for the examples. I got the 1st one to work but not the 2nd. I have a document named Test01.doc. The first code example was placed in ThisDocument. Upon saving, closing and opening the document, the Message Box appeared as it should. I then entered the followng code into Module 1 within the VB Editor; Sub DocInfo() MsgBox " Thisdocument is " & ThisDocument.Name MsgBox "Active document is " & ActiveDocument.Name End Sub I exited the VB Editor, saved the document, closed it and then opened it. I opened the VB Editor and pressed CTRL + G to show the immediate window. I entered DocInfo into this window and pressed enter. An error window appeared saying "Compile error: Syntax error". The code window for module 1 also appeared with the first line of code "Sub DocInfo()" highlighted in yellow. I closed the Immediate window, closed the module 1 code window and then tried to close the VB Editor window by clicking the x at top right. This caused a warning message box to appear saying "This command will stop the debugger". I select OK in this warning box and then click the x at top right in the VB Editor window again. The VB Editor window then closed. What went wrong Tag: VBA read a value and not a formula
- Next
  - 1
    - ReportFault() vs. WerReportCreate/WerReportSubmit() on Vista John, thanks for your message. It's not about the case when a debugger is launched - that works. It's the default reporting path (i.e. without hitting "Debug") which terminates the application - on Vista, that is. Last night, I debugged into ReportFault() on Vista, and found that it launches a child process (wermgr.exe) which does the actual reporting. And while I don't have final proof for that claim yet, I think that it is that child process which terminates the calling process, probably using TerminateProcess() or similar APIs. In my test app, I have a top-level exception handler which catches everything, and I also tried a variation in which I explicitly called SetUnhandledExceptionFilter(). To sum it up, I'm pretty sure that ReportFault() changed its behavior in Vista. That in itself is not a showstopper since Vista has the new WER APIs which provide more explicit control for the error reporting process - but so far, I can't get fully satisfying results with those APIs, either. Hence my quest for sample code. Claus http://www.clausbrod.de/Blog Tag: VBA read a value and not a formula
  - 2
    - CurrentRegion Property to Specify SourceData in PivotCache Method I'm fairly new to writing VBA macros to create pivot tables in Excel, and I could use some help. I would like the macro I am writing to be able to use the Currentregion property in order to specify the SourceData for the PivotCache method, where the source data is located on a sheet named "macro". Any feedback warmly appreciated. Tag: VBA read a value and not a formula
  - 3
    - Passing Function As Argument I'm okay with passing strings, as long as it works This function is to find the root of the function, if there's only one root, and if the values of the function in left and right are of different signals. It's a rude way to find roots, but it's the only method that I know that always find a solution (tangent and newton-raphson sometimes don't find any). It explodes, yes, but the validation is made outside the function =] Thx Andy, once again Tag: VBA read a value and not a formula
  - 4
    - Right-click menu icon Hi there, Can you tell me how to add an icon next to a custom right-click menu item Like the little scissors next to the "Cut" item, I would like to place an image next to my custom menu item. Any help would be great. Thanks. Tag: VBA read a value and not a formula
  - 5
    - Runtime Error '2001' Access 2002 Windows Xp Pro Sp2 And Windows Xp Media Center Edition Sp2 (I tried both. Don't worry, no multi-booting here) Tag: VBA read a value and not a formula
  - 6
    - Search (e.g. Windows Desktop Search) for specific words in VBA code Tag: VBA read a value and not a formula
  - 7
    - Are these valid bucket ids ? DHON, You are running into trouble here because you are trying to mimic an event type. If I understand the problem you are really trying to solve, it’s getting your application’s crash reports on Winqual. All you need to do is map your products on Winqual using the Product Feedback Mapping Tool , and nothing else. As long as your application is not trying to handle its own exceptions, you will get a WER report for your application crashes by default. When your exceptions bubble up the UEF in Windows, Windows Error Reporting is invoked. Depending on the type of crash (Application Hang, 32bit crash, 64bit crash, Buffer Overrun, etc), Windows Error Reporting will create your event type for you as well as handle creating a ‘crash signature’ for the event used to report the event to Microsoft. Examples of these common crash eventtypes that are already built into WER are ‘Crash32’ for an application crash on a 32bit machine, ‘Crash64’ for an application crash on a 64bit machine, ‘BEX’ for security related events such as Buffer Overrun, and Data Execute Prevention issues, and Safe CRT exceptions to name a few. The event type you are mimicking here (PCA2) is used by a service that handles product compatibility checking and reports on failed installation attempts. Mimicking an event will not help you because …not all of these events (such as PCA2) are not exposed through Winqual and you need to successfully map on the valid signature type during a report. There is no reason to create a custom event for a crash or a hang because you get that event report for ‘free’. You don’t have to do anything. Custom events are really for non-fatal events, and require a registration with Microsoft (a manual process today by contacting WER@microsoft.com ). I hope this helps answer your question. Kind regards, -Jason Tag: VBA read a value and not a formula
  - 8
    - Cut A Variable Row from 1 Sheet and Paste to the End of Another Sheet with a condition to meet I wanted to Cut rows that has a status of "High" from sheet B and paste it to the end of sheet A. Sheet A and B will be ever growing list of items. Here's the code that I would like to share. I'm sure a lot more people who would like to create a VBA for Action Database will require this. Sub CopyHigh() Dim HighLastRow, iLastRow As Integer HighLastRow = Worksheets("A").Cells(Rows.Count, "F").End(xlUp).Row With Worksheets("B") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value = "High" Then .Rows(i).Cut Destination:=Worksheets("A").Rows(HighLastRow + 1) .Rows(i).Delete HighLastRow = HighLastRow + 1 End If Next i End With End Sub Tag: VBA read a value and not a formula
  - 9
    - Now Button Hi I'm new to coding macros and am wondering if anyone knows how I can create a button that jumps to today's date on a spreadsheet I have a long column of different dates and would like to make it easier for anyone who accesses the worksheet to go straight to today's date... I've been trying to use several combinations of the code "Range(Now).Select" but have yet to succeed.. Help with this would be much appreciated! Cheers Tag: VBA read a value and not a formula
  - 10
    - Access Mail Merged fields ( Values) in side the Macro as a variable. Hi, I want to utilize the partial printing functionality of the word document. e.g. There is 40 page document out of these user may want to print only 10-20 pages. This page range info will be passed to document through mail merge data. ( Data Source is from SAP-ABAP). Depending upon values in this data I want to execute a 'Print1' macro with the variables. Sub Print1() ActiveDocument.ActiveWindow.PrintOut _ Range:=wdPrintFromTo, From:="1", To:="3" End Sub In above code I want the values for "From" and "To" to be from the mail merged data. Please help me on this as i am very new to all this stuff!!! Thank you, Vinod Doshi. Tag: VBA read a value and not a formula
  - 11
    - VBA Merge question Hi (my English is not very good but i'll try it). When I link the datasource to the document, appears a dialog to select the text encoding. The Document.Open method has a parameter to specify the Encoding, but MailMerge.OpenDataSource does not include it. Is there any way to not appear that dialog Thanks, Janfry Tag: VBA read a value and not a formula
  - 12
    - Exporting the access resultset to excel programmatically Dear all, I would know how can I export the access resultset to excel. Can I get the query from Access which I have done and saved in access directly, so that I do not need to rewrite the SQL statement in the VB, if yet, then how I think I need to use something like Set rs = db.OpenRecordset("Query name") or such but I don't know how. Can anyone of you can help me in this issue Thanks in advance. Regards, Alex Tag: VBA read a value and not a formula
  - 13
    - New lines in MS WORD Hi! I need to insert a "new line" into Word. (i.e. an <enter> ) I have two strings, say sen1 = "This is the first line." sen2 = "This is the second line." I know how to get "This is the first line. This is the second line." into Word, but I need "This is the first line. This is the second line." Please help! how do you do this many thanks! Pieka Tag: VBA read a value and not a formula
  - 14
    - VBA String manipulation problem. (i orignially posted this message in the vb forum... but was told to post in here instead.) I'm trying to code for an Excel spreadsheet. Very new to VBA. I want to replace all comma characters with a newline character. And then get rid of all whitespaces. So far, I've got this: Worksheets("Summary").Cells(i - 4, 1).Value = Trim(Replace(StrConv(Worksheets("SalesHistory.sample").Cells(i, 3).Value, vbProperCase), ",", Chr(10))) Unfortunately, this isn't giving what I want. If I have for example: ABC, DEF Then I end up with: ABC DEF I don't want the extra spaces before the DEF. How do I fix this Also... if I have 2 or more commas without any data in between them (apart from say spaces) then I want all of those commas to be replaced with one comma - and then the above function applied. How would I do this Any help would be appreciated. Thanks. OM Tag: VBA read a value and not a formula
  - 15
    - Where can I find information for "SignedVbScript Class "? Signed VBscript Class and SignedJavascript class appear as drop-down choices for scripting languages supported in the ActiveX Script Task of the SSIS. Where can I find some reference to this class and class members Google search did not yeild any results nor did a search on MSDN. Tag: VBA read a value and not a formula
  - 16
    - Are these valid bucket ids ? DHON, You are running into trouble here because you are trying to mimic an event type. If I understand the problem you are really trying to solve, it’s getting your application’s crash reports on Winqual. All you need to do is map your products on Winqual using the Product Feedback Mapping Tool , and nothing else. As long as your application is not trying to handle its own exceptions, you will get a WER report for your application crashes by default. When your exceptions bubble up the UEF in Windows, Windows Error Reporting is invoked. Depending on the type of crash (Application Hang, 32bit crash, 64bit crash, Buffer Overrun, etc), Windows Error Reporting will create your event type for you as well as handle creating a ‘crash signature’ for the event used to report the event to Microsoft. Examples of these common crash eventtypes that are already built into WER are ‘Crash32’ for an application crash on a 32bit machine, ‘Crash64’ for an application crash on a 64bit machine, ‘BEX’ for security related events such as Buffer Overrun, and Data Execute Prevention issues, and Safe CRT exceptions to name a few. The event type you are mimicking here (PCA2) is used by a service that handles product compatibility checking and reports on failed installation attempts. Mimicking an event will not help you because …not all of these events (such as PCA2) are not exposed through Winqual and you need to successfully map on the valid signature type during a report. There is no reason to create a custom event for a crash or a hang because you get that event report for ‘free’. You don’t have to do anything. Custom events are really for non-fatal events, and require a registration with Microsoft (a manual process today by contacting WER@microsoft.com ). I hope this helps answer your question. Kind regards, -Jason Tag: VBA read a value and not a formula

Pompoire

Dear All,

I have a question, I want to sum some values if a reference is equal to a date that I implemented in a cell that I defined as name (defined as string)

name changes in a loop.

I used the following formula

ActiveCell.FormulaR1C1 = "=SUMIF(R[-2]C10:R[-2]C100,"" = name"",RC[7]:RC[84])"

unfortunatelly it does not take the VALUE of the name variable but it writes it as =name.

how can I force in my program that the value will be taken into account and not the formula which is behind

thank you in advance

Pompoire

Re: VBA read a value and not a formula

Shasur

As the 'name' is within doube quote it is considered as literal string in the formula

Try the way as used below:

name = "A1=10"
ActiveCell.FormulaR1C1 = "=SUMIF(R[-2]C10:R[-2]C100," & name & ",RC[7]:RC[84])"

Cheers

Shasur

Re: VBA read a value and not a formula

Pompoire

thanks and it almost works....

It is on the good way but it does not sumif the value is equal to name.

how to write = name in the formula

Cheers

Pompoire

Re: VBA read a value and not a formula

Peter Mo.

Hi

As you say almost there. Try ..

ActiveCell.FormulaR1C1 = "=SUMIF(R[-2]C10:R[-2]C100,""=" & name & """,RC[7]:RC[84])"

Regards

Peter Mo