INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel VBA -- Decompile?

Excel VBA -- Decompile?

(OP)
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...

CODE

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

CODE

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

It looks like I ran something more like...

CODE

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.

RE: Excel VBA -- Decompile?

Whai is the address of MyRange?. Try instead:

CODE -->

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

combo

RE: Excel VBA -- Decompile?

> 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 ...

RE: Excel VBA -- Decompile?

(OP)
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.

RE: Excel VBA -- Decompile?

(OP)
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.

RE: Excel VBA -- Decompile?

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel VBA -- Decompile?

(OP)
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.

CODE

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.

RE: Excel VBA -- Decompile?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel VBA -- Decompile?

(OP)
For grins I tried combo's syntax...

CODE

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

CODE

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

CODE

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).

RE: Excel VBA -- Decompile?

So are you good?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel VBA -- Decompile?

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

combo

RE: Excel VBA -- Decompile?

(OP)
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.

RE: Excel VBA -- Decompile?

(OP)
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?

RE: Excel VBA -- Decompile?

(OP)

Quote (remarks from https://msdn.microsoft.com/en-us/library/office/ff...)


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...

CODE

Set RngChartData = ws.UsedRange
    If g_BreakoutVariablesSelected > 0 Then 'Only need to delete breakout information if there are breakouts
        With RngChartData
            ws.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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close