• 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

    • LG G3 is still one of the best phones I’ve ever owned
    • LibreWolf 140.0.2-1 by Razvan Serea LibreWolf is an independent “fork” of Firefox, with the primary goals of privacy security and user freedom. It is the community run successor to LibreFox. LibreWolf is designed to increase protection against tracking and fingerprinting techniques, while also including a few security improvements. This is achieved through our privacy and security oriented settings and patches. LibreWolf also aims to remove all the telemetry, data collection and annoyances, as well as disabling anti-freedom features like DRM. LibreWolf features: Latest Firefox — LibreWolf is compiled directly from the latest build of Firefox Stable. You will have the the latest features, and security updates. Independent Build — LibreWolf uses a build independent of Firefox and has its own settings, profile folder and installation path. As a result, it can be installed alongside Firefox or any other browser. No phoning home — Embedded server links and other calling home functions are removed. In other words, minimal background connections by default. User settings updates Extensions firewall: limit internet access for extensions. Multi-platform (Windows/Linux/Mac/and soon Android) Community-Driven Dark theme (classic and advanced) LibreWolf privacy features: Delete cookies and website data on close. Include only privacy respecting search engines like DuckDuckGo and Searx. Include uBlockOrigin with custom default filter lists, and Tracking Protection in strict mode, to block trackers and ads. Strip tracking elements from URLs, both natively and through uBO. Enable dFPI, also known as Total Cookie Protection. Enable RFP which is part of the Tor Uplift project. RFP is considered the best in class anti-fingerprinting solution, and its goal is to make users look the same and cover as many metrics as possible, in an effort to block fingerprinting techniques. Always display user language as en-US to websites, in order to protect the language used in the browser and in the OS. Disable WebGL, as it is a strong fingerprinting vector. Prevent access to the location services of the OS, and use Mozilla's location API instead of Google's API. Limit ICE candidates generation to a single interface when sharing video or audio during a videoconference. Force DNS and WebRTC inside the proxy, when one is being used. Trim cross-origin referrers, so that they don't include the full URI. Disable link prefetching and speculative connections. Disable disk cache and clear temporary files on close. Disable form autofill. Disable search and form history...and more. Notes: To receive automatic updates, you may refer to the unofficial LibreWolf-WinUpdater that employs a scheduled task to accomplish the same. Download: LibreWolf 64-bit | Portable 64-bit |~100.0 MB (Open Source) Download: LibreWolf 32-bit | Portable 32-bit | Other Operating Systems Links: LibreWolf Home Page | Addons | Screenshot | Reddit Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • At least it doesn't reset your defaults lol. Back to Bing, MSN and Copilot lol. Wait, that's a feature, not a bug?
    • Exactly what I was going to say, lmao. Pro-grade where? I have the S24 Ultra and there are only marginal differences between that series and the S25 series. Hell, even the S23 and S22 series is similar. Samsung stopped trying years ago.
    • Are you asking us or telling us my friend?
  • Recent Achievements

    • Week One Done
      Hartej earned a badge
      Week One Done
    • One Year In
      TsunadeMama earned a badge
      One Year In
    • Week One Done
      shaheen earned a badge
      Week One Done
    • Dedicated
      Cole Multipass earned a badge
      Dedicated
    • Week One Done
      Alexander 001 earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      553
    2. 2
      +FloatingFatMan
      182
    3. 3
      ATLien_0
      170
    4. 4
      Skyfrog
      108
    5. 5
      Som
      106
  • Tell a friend

    Love Neowin? Tell a friend!