• 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

    • Rubbish talentless people hired on basis of filling a certain quota will always lead to rubbish company decisions. I am stumped at the thought that people are still OK with DEI after the hell that the gaming industry is going through.
    • I mean, to say that Xbox lost is soul, is the exact same argument you could make about Sony. Clearly, the console space is not the as it was a decade ago, as it was a decade before that and another before that. Generational shifts and market trends clearly defined the space we're currently at. One thing is clear, gaming is not going anywhere, it only continues to grow, for better or for worse. Microsoft pivoted to a publisher focused, and now they want to dominate console streaming, which they seem to have a considerable leg up over Sony on this regard as Internet reliability and speeds keep improving, console hardware might not be a feasible model unless you follow the Nintendo route of making the hardware at a profit (considering its limitations). Also, it seems now that Microsoft pivoted the Xbox branding, that could open the door for 3rd party hardware companies make Consoles that run Windows-Xbox OS, which is something interesting to consider, which would convert Xbox into an Android like approach.
    • UK planning measures to stop illegal content from going viral by Usama Jawad Image via Pixabay A few days ago, we learned that the UK's digital regulator Ofcom has mandated adult websites like Pornhub to put robust age check mechanisms in place to ensure that age-inappropriate content like pornography is not readily available to minors. Now, the government-approved authority has set its sights on a new target. According to an announcement on its website, Ofcom is now holding a consultation period for a set of measures it is proposing to curb the spread of illegal content online. These methods are a part of Ofcom's "Year of Action" initiative, where the regulator is taking decisive practical actions to ensure the online safety of the UK's citizens. Ofcom is proposing the implementation of better recommender systems and crisis response protocols to restrict illegal content from going viral online. In addition, it has also proposed the utilization of proactive mechanisms like hash checks for illegal images to nip the problem in the bud and not even allow any such visual content to be published online. Furthermore, the regulator will be asking tech firms and social media platform owners to leverage artificial intelligence systems in order to detect content like fraud and suicide. People who regularly engage in the practice of publication of illegal content online will also be punished with new sanctions. Lastly, Ofcom wants to build upon its existing child protection codes and capabilities online. It is planning to do this by placing restrictions on interactions between minors and streamers during livestreams. It will urge website owners to block users who share child sexual abuse material (CSAM) and is also proposing the development of AI tools to detect grooming activities and CSAM. The consultation period is open until 5pm on October 20, 2025. Ofcom is soliciting feedback from "service providers", law enforcement authorities, and the public until that deadline to finalize its proposal.
    • What are you talking about? I have had 4 Samsung fold devices and they all have the crease. It is not as noticable when facing straight on but its still noticable.
  • Recent Achievements

    • Week One Done
      TIGOSS earned a badge
      Week One Done
    • First Post
      henryj earned a badge
      First Post
    • First Post
      CarolynHelen earned a badge
      First Post
    • Reacting Well
      henryj earned a badge
      Reacting Well
    • Community Regular
      Primey_ went up a rank
      Community Regular
  • Popular Contributors

    1. 1
      +primortal
      480
    2. 2
      +FloatingFatMan
      194
    3. 3
      ATLien_0
      164
    4. 4
      Xenon
      81
    5. 5
      Som
      76
  • Tell a friend

    Love Neowin? Tell a friend!