I write programs… using software that was developed over half a century ago. I’ve been given a macro that I can use in Excel, and I have no idea how to use it. I expected to be sent a text file with a bunch of code in it, and then I could find out how to use it. Instead, I received a blank spreadsheet. I found the View Macros button, and there’s a name in it. I don’t see any way to view the text of the macro. So I need a primer.
First highlight the name of the macro. Then you’re stuck because you don’t have a View button. (I don’t have one either.) But that’s okay, if you see the Edit button. (I have one, so I hope you do too.)
One of the first tricks I learned as a programmer is that Edit is a perfectly acceptable substitute for View. Just go into edit mode, and look around without changing anything.
Alt+F11 should open up the Visual Basic for Applications window. In that window, you can open up various objects that might contain the code in question.
And, be warned, since I came to MS macros with probably the same old-school background you did, Excel (and Word) macros are an unholy mix of traditional ‘capture keystrokes’ macros and full-blown programs in Visual Basic (a complete programming language that’s no longer supported as a stand-alone programming language, but still forms the core of Excel macros) with I think a few bits of NewShiny.NET or whatever Microsoft calls the framework/language that replaces Visual Basic. I’m sure I’ve got some of that wrong, but that’s because I can’t be bothered to learn the entire confusiing mess.
The point is there are multiple ways to accomplish the same thing in an Excel macro: triggering built-in Excel functions, calling roughly BASIC-style functions or object-oriented style thing.dosomethingtothething.nowdothistothatresul,nowdosomethingelse calls. I
Hopefully your macro will be simple enough to understand without learning full-blown Visual Basic, but be prepared for anything in there.
After some instruction, I saved the .xlsm file that contains the macro. Then I opened the file I want to fix. I clicked View => Macro, and the macro window opened. I see two macros in the list, both with the same name. I clicked Run, and got results. Not the results I was looking for, but the macro did run.
I’ve emailed the person who wrote the macro and included an image of the results, plus an image of the record I hand-edited yesterday. (Find the account number and save it, use the address from the first of the subsequent records, and use the amounts from the record that has ‘Total - [name]’. I don’t know if I’m running it wrong, or if the macro is wrong. (My Easytrieve almost works. For some reason, it’s not picking up the total amounts and instead is picking up the amounts from the last record before the totals.)
Here’s the macro:
Sub CopyAR()
Dim ARnum As String
Dim x As Integer
Range(“A2”).Select
x = 1
Do Until x > 240
Do Until IsEmpty(ActiveCell.Value) = True
ARnum = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
x = x + 1
Loop
ActiveCell.Value = ARnum
Loop
End Sub
'filter A by starts with Total and delete these lines
'filter B by = blank and delete these lines
'enter header so file will load correctly
'AR#,NAME,ADDRESS,CITY,ST,CURRENT,PD30,PD60,PD90,PD91+,ZIP,BALANCE
(I miss the CODE tags!)
It’s a whole lot shorter than my Easytrieve! (Actually, the Easytrieve also reformats into a standard format and adds several other fields.) FWIW, this is a ‘temporary solution’. The company changed its accounting system and can’t write the previous format. I send the data to two databases. One recipient wrote the macro; but as I said, the results are incorrect. The other recipient requires 60 days to send the file to another country for programming. There are only about a dozen accounts that need to be combined into single records, so I can just manually edit for a couple of months. But I thought it would be useful to learn how to use macros.
I heard back that the first recipient’s (our company, in Florida) system doesn’t need the records consolidated, so that’s why the results were not what I was expecting. I can just email the raw file to them each month. I’d still like to figure out why my old-school program isn’t picking up the correct amounts, even though I won’t need it in a couple of months.
< evil cackle > Also Excel 4 macros (which you can’t write, but can use), and a new, just released, macro language for the cloud version of Excel, which returns to the Excel 4 kind of macro (because you knew that’s what you really wanted) < more evil cackling >
This code finds the last value in column A then copies it down the rest of the way to row 242 (no idea why you would want to do that, but there it is). And this code is a terrible way to do that.
This code does the same thing (@Johnny_L.A use the “preformatted text” control for code)
Sub copy2()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(LastRow + 1, "A"), Cells(242, "A")).Value = Cells(LastRow, "A").Value
End SubI haven’t tried it, but it looks like any span of blank cells in column A will get a value from the non-blank above it. It assumes that A2 has a value, and will stop at A242.
More generally, if you are going to be dealing with the macro-enabled xlsm excel files, you should enable the Developer tab in the top ribbon. Instructions for your version of Excel are easily found with a search on “enable developer option in excel XXXX”.
This adds the Developer tab in the top commands ribbon, clicking it brings up a sub-menu of macro related options. If your excel file has macros, clicking on the Visual Basic tab in the sub-menu switches to the Visual Basic for Applications IDE and allows you to view, edit and enter code.
Without going back to school and taking a class in script writing, I can’t tell what these things are attempting to do. But the data is like this: Most has a single record per account, with the account number in column A and the name in column B. The header record begins in A1. When a business has divisions, there is an a/r# in column A and the rest of the record is unpopulated. The following record(s) has (have) nothing in column A, the name in column B, and subsequent cells have the address and accounting amounts for that division. When there are no more divisions, the next record is the Total record. It has ‘Total - [a/r#]’ in column A. The Name and Address fields are unpopulated, and the balance and aging columns have the sum of the balances and aging amounts of the divisions.
Why 242? The only thing I can think of is that that’s about how many records there are.
Where is the ‘preformatted text’; control?
There’s a button in the reply window.
it looks like this … </>
That’s really the hard way for more than a line or two.
If you want to paste a block of code into a post, put down three backticks on a line by themselves. Then duplicate that on the line just below. Then paste the code in between
So it looks like this in the edit box
```
Sub CopyAR()
Dim ARnum As String
Dim x As Integer
Range(“A2”).Select
x = 1
Do Until x > 240
Do Until IsEmpty(ActiveCell.Value) = True
ARnum = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
x = x + 1
Loop
ActiveCell.Value = ARnum
Loop
End Sub
```
And like this in the preview box and when posted:
Sub CopyAR()
Dim ARnum As String
Dim x As Integer
Range(“A2”).Select
x = 1
Do Until x > 240
Do Until IsEmpty(ActiveCell.Value) = True
ARnum = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
x = x + 1
Loop
ActiveCell.Value = ARnum
Loop
End Sub
If there was indentation in your original, it got lost in your post which I copied. But if whatever you’re working with has indentation, it’ll be preserved when pasted into a fresh post.
Guessing about what your indentation looked like inside the xlsm, the result would be about like this:
Sub CopyAR()
Dim ARnum As String
Dim x As Integer
Range(“A2”).Select
x = 1
Do Until x > 240
Do Until IsEmpty(ActiveCell.Value) = True
ARnum = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
x = x + 1
Loop
ActiveCell.Value = ARnum
Loop
End Sub
BTW the quotes around “A2” as pasted are “smart quotes” and are illegal in VBA. You have to use dumb quotes.
If there are fewer than 241 records, it take the data in column A from the last available record and fills it down to row 242 (assuming row 1 is a header). I can’t begin to guess what value this has, or even if the code does what was intended. If you don’t know why 241 is the magic number then I guess nobody know except the person who wrote the code.
Agree with @CookingWithGas’s understanding of what it does and mystification about why.
I’ll just add that it’s hideously inefficient both in speed and in code complexity. Could’ve been one cell of copy & 1 range of paste with no looping. Which doesn’t matter from the computer’s or user’s POV; 240 iterations of anything is still substantially instantaneous.
My point being that this code was written by a plodding thinker who barely knew how to operate the core features of Excel or of VB. So @Johnny_L.A can expect a lot of WTFs as he digs into the rest of the project.
Underlining mine.
It sounds like this is meant to propagate the account or a/r# value from the first record into all those subsequent records where you say col A is blank and the rightward columns contain data. That interpretation also make the subroutine name and variable names (except the junk variable x) make sense.
Bigger picture it smells like this spreadsheet data is imported from some human-readable report but is then being regularized into a set of fixed format records with each row consisting of a bunch of fields. once regularized, this data will then be copied out somehow to some other system that’s expecting each record to be stand-alone.
In case you missed it, that is what I showed in post 9 (I think it was post 9; you can’t really tell anymore). In this case it would matter to the user–the original code uses Select for each cell update so the user watches all that activity happen on the screen, one cell at a time, which does make it hideously slow. It’s works like a Lotus 1-2-3 macro from 1990.
I totally skimmed over your earlier code, not reading what it did. Sorry! I somehow thought you we just demonstrating the Discourse code formatting feature and had just dumped some random code-like text in there to show font, indenting, etc.
Funny but a couple minutes before you posted just above I started really reading all the posts before and after Johnny’s mis-formatted code and suddenly realized everything I’d written in several posts was me slowly redoing stepwise all your earlier work, and sleuthing out stuff Johnny had written explicitly. :smack:
Just call me Mr. Oblivious J. Redundant today. :smack:
No offense to your workmanship implied.