• 0

Anyone know how to "program" for Excel?


Question

18 answers to this question

Recommended Posts

  • 0

You can write scripts within Excel using Visual Basic for Applications (VBA). You have to go into Options -> Customize Ribbon and ensure the Developer tab is enabled to get access to those tools.

What are you looking to accomplish?

  • 0
  On 02/07/2024 at 22:52, Dick Montage said:

I have worked with VSTOs within the office suite. But I think you’ll be best served by telling people what you want to achieve and dropping the vagueness.

Expand  

I wasn't being vague. I was doing the initial post as fast as I could because the bus was coming, then I got a little busy. Now I have time to give a description of what I need.

  • 0

A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion.

What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows.

We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: 

(Fixed Dollar Amount)

Column A...........Column B

Row 1

2

3

4

5

6

7

8

9

10 and so on.

(Earned Amount)

I don't know if I explained it well.

 

  • 0
  On 03/07/2024 at 01:34, branfont said:

A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion.

What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows.

We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: 

(Fixed Dollar Amount)

Column A...........Column B

Row 1

2

3

4

5

6

7

8

9

10 and so on.

(Earned Amount)

I don't know if I explained it well.

 

Expand  

Sure, I understand your requirement. You want a VBA script that will help you track sales in two columns, with a fixed monetary amount at the top, and the total earned amount at the bottom of each column. Here's a VBA script that accomplishes this:

1. It initializes a fixed amount at the top of columns A and B.
2. As you enter sales amounts in columns A and B, it updates the total earned amount at the bottom of each column.

```vba
Sub TrackSales()
    Dim ws As Worksheet
    Dim fixedAmount As Double
    Dim lastRowA As Long, lastRowB As Long
    Dim totalA As Double, totalB As Double

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name

    ' Set the fixed amount
    fixedAmount = 1000 ' Change this to your desired fixed amount

    ' Place the fixed amount at the top of columns A and B
    ws.Range("A1").Valueount
    ws.Range("B1").Valueount

    ' Find the last row in columns A and B
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Calculate the total earned amount in columns A and B
    totalA = Application.WorksheetFunction.Sum(ws.Range("A2:AastRowA))
    totalB = Application.WorksheetFunction.Sum(ws.Range("B2:BastRowB))

    ' Place the total earned amount at the bottom of columns A and B
    ws.Range("AastRowA + 1).Value = totalA
    ws.Range("BastRowB + 1).Value = totalB
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A2:BMMe.Rows.Count)) Is Nothing Then
        Call TrackSales
    End If
End Sub
```

### Explanation:

1. **TrackSales Subroutine**:
   - This subroutine sets a fixed amount at the top of columns A and B (you can change the fixed amount as needed).
   - It finds the last used row in each column.
   - It calculates the total earned amount in each column (excluding the fixed amount at the top).
   - It places the total earned amount at the bottom of each column.

2. **Worksheet_Change Event**:
   - This event triggers whenever a change is made in columns A or B.
   - It calls the `TrackSales` subroutine to update the total earned amount.

### Usage:

1. Open your Excel workbook and press `Alt + F11` to open the VBA editor.
2. Insert a new module and copy the `TrackSales` subroutine into it.
3. Go to the sheet where you want to track sales, right-click the sheet tab, select `View Code`, and paste the `Worksheet_Change` event into the sheet module.
4. Adjust the sheet name and fixed amount as needed in the `TrackSales` subroutine.
5. Save and close the VBA editor.

Now, whenever you add or change values in columns A or B, the total earned amount will be updated automatically at the bottom of each column.

  • Like 2
  • 0
  On 03/07/2024 at 01:34, branfont said:

A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion.

What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows.

We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: 

(Fixed Dollar Amount)

Column A...........Column B

Row 1

2

3

4

5

6

7

8

9

10 and so on.

(Earned Amount)

I don't know if I explained it well.

 

Expand  

You don't need to program anything - this is exactly what Excel is built for.  Just start entering the data... It's a simple math formula - should take all of 5 minutes to get this going at most. If you want to get fancy, put in pivot tables/charts. Also, if you need to actually write a script or code, just ask an AI assistant like ChatGPT / CoPilot / Gemini.

  • Like 3
  • 0
  On 03/07/2024 at 10:30, trogenda said:

Sure, I understand your requirement. You want a VBA script that will help you track sales in two columns, with a fixed monetary amount at the top, and the total earned amount at the bottom of each column. Here's a VBA script that accomplishes this:

1. It initializes a fixed amount at the top of columns A and B.
2. As you enter sales amounts in columns A and B, it updates the total earned amount at the bottom of each column.

```vba
Sub TrackSales()
    Dim ws As Worksheet
    Dim fixedAmount As Double
    Dim lastRowA As Long, lastRowB As Long
    Dim totalA As Double, totalB As Double

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name

    ' Set the fixed amount
    fixedAmount = 1000 ' Change this to your desired fixed amount

    ' Place the fixed amount at the top of columns A and B
    ws.Range("A1").Valueount
    ws.Range("B1").Valueount

    ' Find the last row in columns A and B
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Calculate the total earned amount in columns A and B
    totalA = Application.WorksheetFunction.Sum(ws.Range("A2:AastRowA))
    totalB = Application.WorksheetFunction.Sum(ws.Range("B2:BastRowB))

    ' Place the total earned amount at the bottom of columns A and B
    ws.Range("AastRowA + 1).Value = totalA
    ws.Range("BastRowB + 1).Value = totalB
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A2:BMMe.Rows.Count)) Is Nothing Then
        Call TrackSales
    End If
End Sub
```

### Explanation:

1. **TrackSales Subroutine**:
   - This subroutine sets a fixed amount at the top of columns A and B (you can change the fixed amount as needed).
   - It finds the last used row in each column.
   - It calculates the total earned amount in each column (excluding the fixed amount at the top).
   - It places the total earned amount at the bottom of each column.

2. **Worksheet_Change Event**:
   - This event triggers whenever a change is made in columns A or B.
   - It calls the `TrackSales` subroutine to update the total earned amount.

### Usage:

1. Open your Excel workbook and press `Alt + F11` to open the VBA editor.
2. Insert a new module and copy the `TrackSales` subroutine into it.
3. Go to the sheet where you want to track sales, right-click the sheet tab, select `View Code`, and paste the `Worksheet_Change` event into the sheet module.
4. Adjust the sheet name and fixed amount as needed in the `TrackSales` subroutine.
5. Save and close the VBA editor.

Now, whenever you add or change values in columns A or B, the total earned amount will be updated automatically at the bottom of each column.

Expand  

...all that work only to later find out...

 

  On 03/07/2024 at 11:50, branfont said:

I forgot to mention that I'll be using Excel on my Samsung Galaxy S23 Ultra. 

Expand  

 

  • 0
  On 03/07/2024 at 12:44, branfont said:

I never break any laws, why would I start now?!

Expand  

really sus when you said it was not for anything illegal. Why would we think that in the first place? But now that you said it's not illegal, it's definitely illegal 😛

  • 0
  On 03/07/2024 at 12:57, tsupersonic said:

really sus when you said it was not for anything illegal. Why would we think that in the first place? But now that you said it's not illegal, it's definitely illegal 😛

Expand  

Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person.

  • Facepalm 3
  • 0
  On 03/07/2024 at 13:28, branfont said:

Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person.

Expand  

🤦‍♂️  who said anything about color of skin/race?! respectfully, we're just trying to help you out. 

  • Like 2
  • 0
  On 03/07/2024 at 13:28, branfont said:

Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person.

Expand  

🤦🏻

  • 0

1.  What you're trying to do is literally what spreadsheet applications are designed for and "programming" isn't necessary. 

2.  If you're running it on your phone, just use Google sheets instead of anything Microsoft. 

3. This thread literally explains so much that wasn't asked but never really gets to the point of what was asked.

a. We know you're black.

b. We know you don't know much about spreadsheets

c. We know you assume that everyone else automatically assumes lesser of you. (I mean, how the hell would we have known anything about you without you telling us?) 

d. You assume everyone out there is white and racist. 

e. You haven't asked many questions online.  Just get to the point of what you need and more than likely we'll post a solution and get on with our day feeling like we just did something good. 

f. Don't treat Neowin like Reddit and you won't get Reddit responses. 

g.  You haven't used any of the new AI systems like chatGPT, bings integration or Google integration.  Most of those would have spit out an answer to your question in under 3 seconds.  "how do I make a spreadsheet that..  blah blah blah"

 

 

  • 0
  On 05/07/2024 at 13:25, branfont said:

I tried that and it's not working.

A1: Name

=SUM(A2:A62)

B1: Name

=SUM(B2:B62)

C1: Amount

=C1-(A62+B62)

I put them in their required place, but then I get the following error:

 

Screenshot_20240705_072450_Microsoft 365 (Office).png

Expand  

Hi Branfont 🤗.

 

Where are you placing the SUM?

 

This error usually happens when a cell is referring to another cell that refers to the cell you were referring.

 

Imagine that you have cell A1 `B1`, and in B1, you have `A1`. That's a circular reference.

 

  • 0
  On 05/07/2024 at 13:59, Jose_49 said:

Hi Branfont 🤗.

 

Where are you placing the SUM?

 

This error usually happens when a cell is referring to another cell that refers to the cell you were referring.

 

Imagine that you have cell A1 `B1`, and in B1, you have `A1`. That's a circular reference.

 

Expand  

The sums are put in their respective column.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Oh for crying out loud, more AI rubbish. It gets everywhere, well almost. i use Numbers on the Mac, which does what I need, at the moment I can turn any AI off, but maybe i should go back to LibreOffice.
    • Google Sheets gets exciting Gemini feature that is still missing in Excel by Usama Jawad We recently learned that Copilot in Microsoft Excel got a big upgrade in the form of context awareness, which is in addition to the formula generation capabilities that were made available earlier. However, no firm has really won the AI race yet, and Google has now edged ahead in at least one domain today, by adding Gemini text generation capabilities in Google Sheets. In a blog post, Google has announced that while you can already leverage Gemini in Sheets to generate charts and insights, you can now generate text using its new AI function too. This function can utilize a natural language prompt and an optional context window to summarize and generate text, categorize information, and perform sentiment analysis. The syntax for the AI function is fairly straightforward: AI("prompt",[optional range]) So you can invoke the Gemini function as follows: =AI("Perform sentiment analysis on the emails sent by the customers to the barbershop.", C2) Or: =AI("Create an email to the reviewer addressing specific items in their reviews.", A2:G2) It is very interesting to note that a similar capability is not offered by Copilot in Excel yet. In my testing, I did get Copilot to generate charts, formulas, and do some text formatting, it simply doesn't seem to have the ability to actually generate textual data directly in a workbook just yet. While an input range isn't mandatory, Google recommends using it for the best results. Just like any cell using a formula, you can refresh the AI cell, and get an indicator about its sync state. That said, a limitation that is present with regards to the AI function is that it only works for the first 200 selected cells in your range. Once that generation completes, you can select more cells and repeat the process, which is a slightly cumbersome experience. The rollout for this capability has begun across Business, Enterprise, Education, Education Premium, AI Pro, and Ultra plans, so it should be available to everyone sooner rather than later. In the meantime, you can view details about the AI function here.
    • Space Marine 2 gains a horde mode and DLSS 4 with new Siege update by Pulasthi Ariyasinghe Warhammer 40,000: Space Marine 2 just received its next major update. Dubbed the Siege update, it is finally bringing the long-promised horde mode for the third-person action game, as well as new tech for PC gamers. The Siege mode takes place on a fortified Imperial stronghold on Kadaku that is enduring endless attacks by Tyranids and Thousand Sons. There are three sections in total that players will defend in order before circling back to the original position, fighting away thousands of enemies in five waves before swapping locations. To give players a small break, there's a one-minute gap between each wave to resupply and purchase perks from the points they collect while in battle. Players can call in AI-controlled specialized soldiers, Space Marines, or even a Dreadnought to help out if the waves get a little too tough. To cap it all off, every fifth wave a boss encounter kicks off. There are 15 waves to go through in a single session, but parties can also kick off an endless mode at the end of these to see how far they can get. "Siege Mode expands on our PVE operations, emphasizing endless enemy waves that challenge even the strongest teams," says Focus Entertainment, describing the new mode. "When overwhelmed, players may call reinforcements for assistance. Engage in intense battles across three distinctive sectors, each requiring strategic adaptation. Side objectives emerge during combat, providing essential resources used to summon elite Space Marines or a formidable Dreadnought, drastically shifting battlefield dynamics. Over on PC, the studio has added two new options. One is RTAO (Ray-Traced Ambient Occlusion), though this is currently listed as an experimental feature. Next, Nvidia 5000 series graphics card owners can now enable DLSS 4 for better upscaling tech as well as multi-frame generation. The complete patch notes for Patch 8, which includes details on the balance changes, can be found here. The Warhammer 40,000: Space Marine 2 Siege update is now available on PC, Xbox Series X|S, and PlayStation 5.
    • Microsoft updates Media Creation Tool with a newer Windows 11 release by Taras Buria If you want to download a Windows 11 image or create an install media, the Media Creation Tool app is one of the best options—simple, fast, and, more importantly, official. While there are other useful alternatives, such as Rufus, Media Creation Tool remains the default choice for those who simply want a clean Windows 11 installation on a supported computer. Thanks to the latest MCT update, you will have to deal with fewer updates after installing Windows 11. Microsoft has quietly updated the Media Creation Tool app so that it downloads the latest Windows 11 release, version 24H2, with build number 26100.4349. This is the most recent public Windows 11 update (June 2025 Patch Tuesday), which eliminates the need to download large updates for your PC after installing the operating system. You can download Media Creation Tool from the official Windows 11 website. If you are familiar with it, this simple app can generate a Windows 11 image with the language you need or create a bootable USB media. Note that it only works on Windows, so Linux or macOS users have to download Windows images directly from the official website and then use apps that are available on their platforms. Another thing worth noting is Media Creation Tool is not the right choice if you want to install Windows 11 on an unsupported PC. In such a case, you can use MCT to download an ISO and then create a modified install media with apps like Rufus or Ventoy as described in our dedicated guide. If you want a more exotic Windows 11 Edition, try the Oofhours Media Tool, which allows you to select Windows 10 or 11, architecture (x64, x86, or ARM64), language, and edition. In addition to standard Home and Pro, the application can download non-mainstream SKUs, such as Enterprise, Education, or Professional for Workstations. Via: Deskmodder
  • Recent Achievements

    • Conversation Starter
      Kavin25 earned a badge
      Conversation Starter
    • One Month Later
      Leonard grant earned a badge
      One Month Later
    • Week One Done
      pcdoctorsnet earned a badge
      Week One Done
    • Rising Star
      Phillip0web went up a rank
      Rising Star
    • One Month Later
      Epaminombas earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      539
    2. 2
      ATLien_0
      207
    3. 3
      +FloatingFatMan
      175
    4. 4
      Michael Scrip
      148
    5. 5
      snowy owl
      118
  • Tell a friend

    Love Neowin? Tell a friend!