Member Avatar for crazy_girl292

Hi... I'm just a beginner in Excel and i have problem concerning building macros... would anyone be willing to give me few tips on how to create and a simple example of a syntax in a macro. Any help will be appreciated... :D

  • 3 Contributors
  • 9 Replies
  • 4K Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by Buff

Dani AI

Generated

If you are brand new to Excel macros, , think of a macro as a named procedure written in VBA. A procedure starts with Sub and ends with End Sub. You can record simple steps with the Macro Recorder, but learning a few lines of VBA gives you far more control.

Quick start:

  • Press Alt+F11 to open the VBA editor.
  • Insert -> Module.
  • Paste a tiny macro, run it with F5, and watch Excel change.

Example 1: the smallest possible macro.

Option Explicit

Sub HelloExample()
    Range("A1").Value = "Hello from a macro!"
End Sub
  • Option Explicit (recommended) forces you to declare variables, preventing hard-to-find bugs.
  • Sub HelloExample() is the procedure name; End Sub closes it.

, instead of hard-coding a column like F for the Amount field, you can let VBA find whatever the last column is after your CSV import and sum it automatically. This adapts whether users include 4, 5, or 6 fields.

Option Explicit

Sub AddTotalToLastColumn()
    Dim ws As Worksheet
    Dim headerRow As Long, lastCol As Long, lastRow As Long

    Set ws = ActiveSheet
    headerRow = 1

    ' Find last used column in the header row
    lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column

    ' Find last used row in that last column
    lastRow = ws.Cells(ws.Rows.Count, lastCol).End(xlUp).Row

    ' Write a label and the SUM below the data
    ws.Cells(lastRow + 1, lastCol - 1).Value = "Total"
    ws.Cells(lastRow + 1, lastCol).FormulaR1C1 = "=SUM(R2C" & lastCol & ":R" & lastRow & "C" & lastCol & ")"
End Sub

Tips:

  • Save as .xlsm so your macros are preserved.
  • If your first row is not headers, adjust R2C in the formula to your first data row.
  • To avoid accidental edits, you can convert the imported range to a Table and use structured references before adding the total.

Recommended Answers

All 9 Replies

Member Avatar for crazy_girl292

*actually im a beginner in building marcos

Member Avatar for big_k105

what do you want to know about creating macros. and are you talking VBA im guessing you are. if you need some help jst ask a specific question. im not good at giving tips but i can help for problems :)

Member Avatar for crazy_girl292

I don't exactly know how write macros... :S and really want to know what the syntax "sub" and all those types stand for... i'd rather u post an example if possible :D

Member Avatar for big_k105

well i will tell you how to to get to the vba stuff when i get to work today as i dont have windows or ms office on my home computers so when i get to work i will help guide you to get to the macro stuff. :)

Member Avatar for crazy_girl292

ummm.. ok...

Member Avatar for crazy_girl292

exactly when do u get to work? Just to let u know... I M WAITING..... :)

Member Avatar for big_k105

sorry about that i got busy at work. and now i dont work till thrusday. and i wont get anytime for awhile to explain everything to you so here is a good tutorial to get you started. if you have any other questions let me know and i will try to help :)

Member Avatar for Buff

I've got an accounting program I wrote (basically a General Ledger). I
have an option to send the postings to a spreadsheet.

I use a comma delimited file. This works fine.
The user can include up to 6 fields, the last being the amount field.
I want to be able to include a macro or something to total this last column
at the bottom of the spreadsheet, when it is imported.

Any Ideas.
Fields are:
Date, Journal, Reference, Description, Account, Amount

I don't send (currently) any formatting information, just a comma delimited
file with the headings as the first "record"

Thanks in advance for any help.

Member Avatar for Buff

Got the answer (to my question above) on the PowerBasic forum
add a line
,,,"Totals",,=SUM(F1.F4)

In my case whether I use F or another letter depends on how many
fields are selected by the user (amount if selected will always be last).
the 1 and 4 are replaced with beginning record number and ending record
number +2
(I actually use Val(EndRec$)-Val(BegRec$)+3
Then I simply change the width on the description and amount fields (in excel after importing) and viola.

BegRec$ is the beginning record selected by the user from the data file,
etc.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.