Re: Importing Exported Modules into Sheet Code Modules and ThisWorkbook Module
Outstanding info! Thanks.
Just a warning for anyone following this, you have to set a check box in Excel 2002 or later for "Trust access to Visual Basic Project"
This can be found under Tools:Macros:Security on the Truster Publishers tab.
I have a list of data sorted like the following:
DP 1.1
MP -0.9
MP -0.4
MP -1.4
DP 2.6
MP -1.8
MP 1.6
DP 1.2
DP -0.1
MP -2.3
I'd like to do some plotting that shows all the DP items in one color and all the MP items in another color.
I cannot sort because this is time series data and other charts rely on the time nature of date in adjacent cells.
Pivot tables are non-ideal (unless someone can explain dynamic pivot tables) since new data will be added daily and I don't want to rebuild the pivot every day.
Ideas?
I was able to correctly rank the list by creating columns with:
1) Rank by score (RS)
2) Rank by days (RD)
3) Aggregate = 10 * RS + RD
4) Rank by Aggregate (AG)
Remember to use the reverse rank bit when you create RD and AG.
You could, of course, make 1 column that is steps 1, 2 & 3, hide this and then rank based on the hidden column.
2nd idea would be to sort the data using the normal sort routine. This will reorder the data in rank form.
chris
I couldn't figure out how to do it with a cell function. There doesn't seem to be a search from the right function.
Solution 1 would be to create a search or replace from the right function and use that.
Solution 2 is a custom function, such as below.
Hope this helps.
******************************
Function InjectSpaces(strOLDTEXT As String)
Dim strPIECES(3) As String
intLength = Len(strOLDTEXT)
intLAST5POS = InStrRev(strOLDTEXT, "5")
intDASHPOS = InStr(strOLDTEXT, "-")
strPIECES(1) = Left(strOLDTEXT, intDASHPOS - 1)
strPIECES(2) = Mid(strOLDTEXT, intDASHPOS + 1, intLAST5POS - intDASHPOS)
strPIECES(3) = Right(strOLDTEXT, intLength - intLAST5POS)
strNEWSTRING = Join(strPIECES, " ")
InjectSpaces = strNEWSTRING
End Function
******************************
Thank you very much for the time and effort you devoted to this problem!
You managed to recover days 30 to 39 ... gives me at least a little history. I can fill in some gaps beyond this.
What is the format of data in an Excel file? Is there a book or white paper that would help me understand the cell contents format in binary code?
I tried to open the file in a hex editor and discern any data, but I couldn't manage to get a finger hold in the reverse-engineering wall climb.
chris
I've tried to use the Excel Rebuilder software. Didn't seem to work. I also tried to use text extraction from the Excel Rebuilder, but I can't figure out what this does (didn't give me data on a good file, either).
The VBA macros that are in the file are non-functional (experimenting only) so I don't care if they are retained. The only thing I would like to have back is the 5+ weeks of historical data that I've got in the sheet (with no backup of course).
If you manage to get it working, you will have intimate understanding of just how fat I am :coolwink:.
Thanks,
Chris
If your calling functions or loading cells with worksheet functions from specific add-ins, you need to make sure that these same add-ins are included in your buddies Excel. Most common for me is the "Analysis Toolpack" since I use lots of Hex2Dec, etc.
My file was working as of Saturday. Opened it and added a couple of cells of data. This is a very simple spreadsheet with 5 or 6 columns of data and 4 charts. Somehow, though, when I try to open the file now, Excel looks like it's processing the file and then comes back blank. Even if I switch over to the VB Editor, there is nothing indicating a file is loaded.
Any ideas how I can fix the file or get my data back?
