I create and delete a conditional formatting rule programmatically. The issue I am facing is when I try to delete the conditional formatting rule that I programmatically created, I ran into the following errors:
1. Exception from HRESULT: 0x800A03EC
2. Attempted to read or write protected memory. this is often indication that other memory is corrupt.
I get these errors only when I have user created conditional formatting rules prior to deleting – I don’t want to delete all conditional formatting rules using FormatConditions.Delete(). Further, I can’t use FormatConditions[index].Delete() because I don’t have a index reference for the one that was created from the application.
Note: I have checked the Trust access to the VBA project object model.
I have had a long-held belief that Excel/VBA methodology is very suited to solving this problem. I have investigated several possible methods noted below and picked one that gives a "quick win". I offer my results so far as a step towards the goal, as well as giving an example of how to manipulate a non-MS Office application using VBA. In this case Windows Explorer - I have used similar code on corporate applications like Oracle and SAP. My 'Write' method uses Sendkeys. Luckily I have been able to do the job without having to use code to simulate mouse functions or using API calls to simulate key presses. Ideally I would like to use something more stable, but there is the bonus that it is simple, as well as changing WMA and both versions of MP3 tag (see below) if present. Perhaps on reading this someone else may have a better method.
Being a ballroom dancer I have a large collection of CDs as well as software to rip to hard drive and enhance the sound quality. Many are quite old. A big problem has been to get a consistent view of the file properties - especially Genre, (eg. Waltz, Foxtrot .. etc.) which, from my own CD burning or external sources, is missing, or incorrect from using the now obsolete ID3v1 tag standard list. Applications such as Windows Media Player and RealPlayer allow functionality to edit tags but become very tiresome when it comes to making bulk changes - such as after burning a new CD. When viewing properties of the same file in various other applications they often show things like Title & Artist switched, and Genre not at all. I have tried software to change MP3 file tags, but find them over-complicated, confusing, and difficult to make the bulk changes I need. With Windows Explorer we can only change 8 properties - but I find these sufficient.
My method is :-
1. READ : Run one macro to put data into a worksheet.....
2. MAKE CHANGES : Make manual changes to the Excel worksheet in the normal way....
3. WRITE : Run another macro to read the worksheet and update the file properties in Explorer.
Part 1 is very simple and robust. Part 3 is difficult because when we use Sendkeys to mimic keyboard entry the code runs too fast to allow time for things to happen on screen, so we have to put Wait statements *depending on how fast the computer runs*. So Slower is better - up to a point.
Properties are added to MP3 files by using a "Tag" - additional bytes of information which form part of the file. WMA files are a Microsoft invention using a similar, but different structure. Interestingly, using my code to make changes via Window Explorer updates BOTH MP3 Tag versions as well as .WMA files. I moan about Microsoft less and less.
MP3 *ID3v1* consisting of 128 bytes always at the end of the file is now 'obsolete' - despite being still in use. This is very easy to read/write using the same code as for Text Files eg.
Open "c:myfile.mp3" For Binary As #1
etc. It is, however, limited to 4 text fields of 30 characters max, 'Year' =4 characters, and 'Genre' is a single character, the Asc() code of which is a lookup to a standard list which contains 125 items - none of which is any good to me. Could have my own lookup I suppose.
MP3 *ID3v2.3* is in the process of being superseded by ID3v2.4. The big problem here is that there are several different versions and the code required is extremely complicated - mainly due to the use of variable length fields. So we not only have to find the property, but read the field length before getting the field contents. Writing would need to change the coded field length. This is further complicated by there being the option to use an "Extended Tag" - *or not* ! Version 2 tag can be at the beginning or end of the file (before ID3v1 if it exists) - or both. The MP3 files on my computer all seem to have both versions - v2 at the beginning and v1 at the end. It is further complicated by the ability to have User Defined fields. I see some of my files have a user defined 'Genre' field, despite having the standard one 'TCON' too. The tag also needs a form of "encryption" so that the mp3 player does not treat it as audio data. Visit here for detailed information http://www.id3.org .
To view an audio file in its raw state open it in a Text Editor. I use 'TexPad' which is very fast and gives a choice of Binary (with Text 'translation' in a column) or Text view. Notepad gives just a Text view with empty space for non-text/binary characters. There is a large number of Null characters Asc(0) in proportion to the overall file length - mainly for "future development" I believe.
I would be interested to hear of any comments, suggestions and code improvements.
The problem with the Write macro was that, although it did work, it used SendKeys which has to be slowed down considerably.
Here is a new version of the Write macro that works as normal. I originally tried to use CDDBControl.dll version 1.2.0.51 which is widely available on the internet but found that I could only get it to change 1 file before crashing Excel.
I have put the READ macro in the next message.
Dim ws As Worksheet Dim FromRow As Long Dim LastRow As Long Dim FilesToChange As Integer ' number of files to change Dim FilesChanged As Integer ' number of files changed Dim MyFilePathName As String ' full path & file name Dim MyFileType As String ' mp3 wma etc. '- Dim id3 As Object Dim MyArtist As String Dim MyAlbum As String Dim MyGenre As String Dim MyTrack As String Dim MyTitle As String
Sub WRITE_TO_EXPLORER() Application.Calculation = xlCalculationManual Set ws = ActiveSheet Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag") LastRow = ws.Range("A65536").End(xlUp).Row ' count worksheet rows FilesToChange = ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count If FilesToChange = 0 Then MsgBox ("No files to change."): Exit Sub FilesChanged = 0........................
I have a tool that accesses a master sheet. Multiple people use the tool (sort of a user interface).
In some cases I need to write to the Master in which I check to make sure its not read only when the macro opens it (that works fine).
In other cases, like when just pulling information, read only is fine. But, I have not specified read only when I open the master. Sometimes the users are getting a "File Now Available" with a read-write and cancel option. I do not want them to get this message.
My current universal call to master looks like this: ...
I have recently used a before_close event on this workbook to save a backup of the open file to another location on my system. This works fine but I was wondering if there was some more code I could add to only execute this event on a write access basis.
The file I use can be viewed by anyone on the network as read-only and only certain users with a password can edit/update with a write access password.
The backup event is use executes every time the document is closed be it read-only or write-access.
Ideally I would like to add some code to only execute this backup if the file is opened on a write-access basis.
I've got a spreadsheet that has password protection before you can "modify" it. All of a sudden today, when I open the spreadsheet and enter the correct password, it still opens but only as Read Only.
I have a userform with about 20 multipage tabs, each of which has quite a few text and combo boxes. My problem is I think I have reached some physical limits for the number of controls on the userform. When I try and add code for the userform I get an "out of memory" error.
I thought I have seen some limits on the length of macro code before, but couldn't find them again. If anyone could let me know the limits on userform controls and the number of VBA lines allowed behind the userform, that would be great.
I have tried to put as much of the code as possible in public subs, but there is still a fair amount of code that is associated with things like the AfterUpdate function and I don't know of any other place to put this code. Please let me know if there is another way to put this code in so I don't run out of room.
I am using the below macro to copy the rows (Starting from Row 2 excluding header row) to another sheet (Starting from Row 2 excluding the header row). I have a huge number of rows which i need to copy to the new sheet (crossing 1.5 Lakh records) which is probably causing "Out of Memory Error: 7" to occur when i execute the below code.
I am trying to convert cells (all in column D) which are separated by "~" into columns. Unfortunately, running the text to columns command on several rows at a time can cause Excel to panic with an out of memory error (error #7 etc.).
The file is ~100mb and contains 500k-700k rows (I have 4GB of RAM so I know this is more a limit of Excel's 2GB RAM constraint).
Can you please help me write a macro to text to column convert each cell in column D?
I tried a macro which started with a for loop, and called the function for each cell individually, but even this led to an out of memory exception after 156,000 rows (although the same macro worked fine on a similar sheet with 700,000 rows).
Are there any other ways of clearing the Excel buffer/temporary space during the function calls to avoid causing Excel to crash?
i have a macro which is executing some cleaning up and then copying information from project sheets to a summary sheet. the individual project sheets are (currently still manually) copied/moved into the big(ger) overview workbook. to support the work with the individual project sheets they contain a macro that is assigned to some (three) shapes. one part of the cleanup is stripping the macros off the individual project sheets:
' remove the traffic-light macros of the project sheets from all sheets in case of unwanted or uncontrolled "infection" For CurrentWS = 1 To SheetCount Worksheets(CurrentWS).Select ActiveSheet.Shapes.SelectAll Selection.OnAction = "" Worksheets(CurrentWS). Range("A1").Select Next CurrentWS
on the line "ActiveSheet.Shapes.SelectAll" i get frequently (not always... ) a run time error '7': out of memory. it happens on at least two computers. i closed all other SW. according to the windows task manager information there should be plenty of memory free.
questions: - how to get rid of the error message? OR: - is there another (more smart) way to strip the ("imported") macros, while keeping the "main" marco alive that does not lead to an error message?
I have a macro that does a solver routine. I have another macro that runs this solver, then copies and pastes new inputs, then runs the solver, then copies the results and pastes them somewhere, then copies new inputs, etc. It is a long process and it worked fine a few days ago. Now when I run it, about 90% of the way through and then excel crashes. The error says:
Solver: An expected internal error occurred, or available memory was exhausted.
I am trying to write a formula to read the cells in column H individually if cell is blank write no to corresponding cell of column I, if the cell has any sort of data write yes to corresponding cell of column I
Currently H uses this formula ='name of column from sheet 1'!P:P
What if you have text that are hyperlinked to a txt file and you want to read from it and copy it into excel. What can I do then. Here is what I have been working on so far:
Sub GoToHyperLink() Dim cell As Range Dim link As Hyperlink Dim Textline As String
we have one excel sheet. I want to write or edited data in specific cell. For example anybody can write in coloum : C1, c2, c3, c4, k6 and m6 (highlighted in green colour) and remaining all cell range protcted no one can change data. Also they didn't know formula which i m writing in others cell for example fom l6 to l33 or n6 to n33. We are attach file for ur ready ref to know easily.
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 71 bytes) in /home/eforum/public_html/search.php on line 1155. I have following macro, what worked just fine in 2003. Now I'm working with 2007 and I can't set up the Microsoft Visual Basic for Applications Extensibility library. Here's the
Private Sub TextBox135_Change() On Error Resume Next TextBox138.Value = Format(CDbl(TextBox135.Value) / CDbl(TextBox86.Value), "0.0000")[code].....
It suppose to take one input and calculate for the other variables the problem is that once it writes the outputs the other text box it activates the second text box code so I keep getting the error "13" and the result is the same no matter what number I put.
I'm looking to write a nested IF formula which will be dependant on certain cells that have errors in them (because those error cells have a formula in them which can't formulate becuase the numbers needed for that formula haven't been entered yet).
For example.
Cell A1=#DIV/0!. Cell B1=5.
In cell C1 I want to calculate the average between A1 & B1, but there is an error in A1.
I'm trying to figure out how to make a formula which ignores an error and gives me the value in the cell which doesn't have an error. But I also need it to calculate the average if both cells have a value in it.
I have the following code which I just cannot get to work.
Sub Macrotest() ............. End Sub
The first time it saves it to the folder where business can view I don't have problems, but during the course of the day the info changes and I need to re-update the file.
This is where the problem comes in, as soon as it wants to overwrite the file in this folder :="J:BUSINESSStandardisationBSIS Reports.xls" it gives the Run time error '1004' with the statement the file is write reserved. Obviously the macros basically bombs out and doesn't save the new version over the old.
I have a userforms which transfers data to protected sheets.The userforms worked until i protected each sheet.Can there be any code which i add to the current userform code so it can still transfer the data to protected sheets
The other issue also is now that the sheets are protected the Outlining also does not work.Can this also be changed so the Outlining works on protected sheets
I am getting an error when I protect a worksheet and save it. Upon re-opening the worksheet, I try to use the cells that are being formatted by a macro, but I get the following error...
Run-time error '1004':
Unable to set the NumberFormat property of the Range Class.
If I open the worksheet and it is already protected from when I last closed it, then I get the error. If I unprotect the sheet, then close it and reopen it, then I don't get the error. Does anyone know what I might be missing or doing wrong.
I am running an auto_open macro in an Excel 2007 workbook that is kicked off remotely by a job on an AS400. This process is run multiple times during the day, one right after another. I am experiencing with some of the jobs the "Out of Memory" error condition.
When I protect my worksheet the option buttons, on occasion, give the error message "The cell or chart that you are trying to change is protected and therefore read only. To modify a protected cell or chart, first remove protection using the Unprotect sheet command. You may be prompted for a password." Sometimes I click on an option button and this error pops up and other times i can click the same button and no error occurs.
For some reason I thought this would be simple, but not as simple as I thought. I have a UserForm that enters data into a sheet, but before it is entered, the user is prompted for the sheet password (sheet is protected). What I tried to do is use an error handler to exit the sub if the password is incorrect, user hits Cancel, etc. Here is the code I am using:
Why am I getting a runtime error on the following code:
Code: If Target.Address = "$E$5" And Target.Value "" Then Range("E5:G5").Copy If Sheets("Sheet5").Range("F5").Value = "" Then Sheets("Sheet5").Range("F5").PasteSpecial xlPasteValues Else Sheets("Sheet5").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues End If End If
When I select only one cell it's ok but when I select multiple cell to delete it sends me a run-time error '13'.
I created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:
