Excel VBA -- Decompile?

[lameid]

I have a situation with VBA in Excel that if it were in Access I would use the command line decompile switch to clear out any compiled vba code and start over. Is there any equivalent?

I'm writing that code from memory in this post, forgive any syntax issues it is for illustration purposes only.

The issue I am experiencing is that I have a range selected on a worksheet...

Then I try to delete the top two rows of that range with something like...

Range(MyRange.cells(1,1), MyRange.cells(3,1)).entirerow.delete

This seems to be failing...

when I select Myrange in break mode, it seems to be right but when I do something like

Range(MyRange.cells(1,1), MyRange.cells(3,1)).select

It looks like I ran something more like...

Range(MyRange.cells(14,1), MyRange.cells(15,1)).select

So any thoughts on decompile or root cause welcome... This is a Macro that has been running successfully for quite some time.

 

[combo]

Whai is the address of MyRange?. Try instead:

With MyRange
    Range(.cells(1,1), .cells(3,1)).entirerow.delete 
End With

combo

 

[strongm]

> if it were in Access I would use the command line decompile switch to clear out any compiled vba code

There's a free utility that will do this (and the page includes a pretty good explanation of the VBA compilation process) and why one might want to use decompilation - note that in my opinion, complete decompilation to fix an apparent bug is using a sledgehammer to crack a nut. We cancause individual functions and procedures to recompile all the time. I also don't think that cached P-code is causing your problem.

>I'm writing that code from memory in this post,
It would help to see actual code. And somewhat more of it ...

 

[lameid]

I haven't tracked it down yet but using different constructed input file works. I've yet to see what looks like it could be a material difference between the files.

I suspect it is some hidden trash in the files.

Aside from that I can't just download free stuff like a decompiler... I went with the old method of dumping code to files and importing them before the file input turned up.

 

[lameid]

I ended up saving each sheet in the problem data file to csv, and then using those files to reconstruct a new workbook. That new workbook file processed fine. Since the macro only looks at data and not formatting, I am assuming something about the original file was corrupt or causing a memory issue. Even so, I can't help but wonder if it could be something else. There is too much code to post but the range is a Usedrange.

 

[SkipVought]

UsedRange is a property of a WorkSheet. Could your sheet name have changed, for instance?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

No... the worksheet is not changing... the actual code that I see going awry in debug mode... The if evaluates true but does not delete the first three lines... It does delete rows in RngChartData, at least sometimes but not the first three rows.

    Set RngChartData = ws.UsedRange
    If g_BreakoutVariablesSelected > 0 Then 'Only need to delete breakout information if there are breakouts
        RngChartData.Range(RngChartData.Cells(1, 1), RngChartData.Cells(3, 1)).EntireRow.Delete
    End If

RngChartData has data placed on "ws" that is the result of a transpose above it.

I have to point out two things. 1) It works flawlessly most of the time. 2). Saving source data cleanly through csv fixes it. So there is something odd about the data file. But nothing I can think of would cause the wrong rows to be targeted other than a malfunction of Excel itself. If there is something you can think of, I am happy to look but I think it is one of those office things.... it was corrupt... fiddle with it until it is not... regret that your luck was spent on twiddling bits in an office file instead of winning the lottery. Now it has been made to work two different ways. A data file built from a different file used as a template and a data file whose all it sheets were ran through CSV format. Given the save through CSV case, I submit it is not the structure of the file or the data. I tried clearing out cells below and right of the data before the last resort save through csv. My conclusion is something is corrupt or there is some fundamental rare excel behavior that I don't know about to look for and it exists in the original file somehow. It still seems like corruption to me.

 

[SkipVought]

When you say, "wrong rows" exactly what do you mean?

UsedRange can reference MORE ROWS/COLUMNS than intended, if there's "data" (not necessarily TEXT). When ANY data is cleared from the sheet, it must be more than a simple DELETE. I often select ALL COLUMNS to the Left/Right of the range to keep, or ALL ROWS to the Top/Bottom of the range to keep, and Right-Click DELETE or programmaticly Delete and Shift.

Simply try ActiveSheet.UsedRange.Select and see exactly what's selected.

You can get around this seeming anomaly if this range is intended for ONE TABLE, by referencing any range in the table... SomeRangeInTable.CurrentRegion.Select

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

For grins I tried combo's syntax...

    Set RngChartData = ws.UsedRange
    If g_BreakoutVariablesSelected > 0 Then 'Only need to delete breakout information if there are breakouts
        With RngChartData
            .Range(.Cells(1, 1), .Cells(3, 1)).EntireRow.Delete
        End With
    End If

So with the breakpoint on and stepping into the if...
My immediate window and comments

RngChartData.Parent.visible = true
RngChartData.Parent.activate  'Sheet was hidden and didn't have focus

RngChartData.Select 'Yep used range in this case A:11 to row 51 not sure of ending column... at least AB  (turns out it must be BG)

RngChartData.Range(RngChartData.Cells(1, 1), RngChartData.Cells(3, 1)).select 'Selects first column and rows 11,12 & 13 of RngChartData (Rows 21-23 of sheet)- WHY? This is what is deleted if it gets that far
RngChartData.Cells(1, 1).select 'Selects first row and column of RngChartData - Expected (Row 11 of sheet)
RngChartData.Cells(3, 1).select 'Selects third row and first column in RngChartData - Expected (Row 13 of sheet)

ActiveSheet.UsedRange.Select 
? Selection.count
 2419 
RngChartData.Select 'Looks like same data of active sheet but can't scroll in debug mode
? Selection.count 
 2419

Stop code... Immediate window... repeat

ActiveSheet.UsedRange.Select 'all the way over to col BG
? Selection.count
 2419

I did try deleting the offending rows via the immediate window, and moving the next line to run appropriately... I ended up with another weird failure later. Weird because the code runs until the end with the cleansed version (same data cycled through csv).

 

[SkipVought]

So are you good?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Nested ranges in immediate window:
[tt]RngChartData.Range(RngChartData.Cells(1, 1), RngChartData.Cells(3, 1)).select[/tt]

combo

 

[lameid]

I'm good from the stand point that I found a file that apparently is corrupt and causes the issue... As to what that corruption is, I don't have the foggiest idea.

The larger concern of course is that whatever the root cause may occur again and effect things in a bad way that may not be readily detectable.

I finally see the nuance of combo's suggestion... I don't understand why that would have a different effect. But I'll go kill that . in front of the range.

 

[lameid]

Huh, combo's suggestion gets past that error... Seriously I would love an explanation of that. Interestingly it gets to another error that I think is the same error when I manually fixed the code.

Probably another oddity that crops up in some contexts.

I get that is a nested range but why does that matter?

 

[combo]

See remarks in

https://msdn.microsoft.com/en-us/library/office/ff834676.aspx

combo

 

[lameid]

https://msdn.microsoft.com/en-us/library/office/ff834676.aspx[/URL]]
When applied to a Range object, the property is relative to the Range object. For example, if the selection is cell C3, then Selection.Range("B1") returns cell D3 because it?s relative to the Range object returned by the Selection property. On the other hand, the code ActiveSheet.Range("B1") always returns cell B1.

So in theory it should work... And it more generally works on the worksheet object with an implicit activesheet if left out so I should specify the worksheet since I am not using activesheets...

Set RngChartData = ws.UsedRange
    If g_BreakoutVariablesSelected > 0 Then 'Only need to delete breakout information if there are breakouts
        With RngChartData
            [red]ws[/red].Range(.Cells(1, 1), .Cells(3, 1)).EntireRow.Delete
        End With
    End If

That said it seems to just be moving the weird to a different place in code. So it works despite itself a little longer using that method.