• 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

    • https://deadline.com/2025/06/spaceballs-2-casts-rick-moranis-bill-pullman-keke-palmer-1236431204/
    • Microsoft updates default app choices for Windows 10 and the Curl tool with build 19045.6029 by Sayan Sen Microsoft has rolled out a new Windows 10 release preview build today for Insiders flighting the channel. The new build, 19045.6029, has some new changes and improvements. The biggest highlight is related to default app choices for the EEA (European Economic Area) region. Microsoft writes: "We are rolling out some small changes in the EEA region for default browsers via the Set default button in Settings > Apps > Default apps: Additional file and link types will be set for the new default browser, if it registers them. The new default browser will be pinned to the Taskbar and Start menu unless you choose not to pin it by clearing the checkboxes. There is now a separate one-click button for browsers to change your .pdf default, if the browser registers for the .pdf file type." Microsoft has also updated the curl command line tool with "most recent stable version is 8.14.1". Aside from those, remote Component Object Model (COM) activation that were failing with 0x8001011 error code has been fixed. And there are several other changes too. The full changelog is given below: [Mobile Operator Profiles] Updated: Country and Operator Settings Asset (COSA) profiles. [App Platforms and Frameworks] Fixed: An issue affecting Component Object Model (COM) functionality on Windows platforms, where remote COM activations were failing with error 0x8001011. Upgraded the version of the curl tool included in Windows to v8.13.0. [Authentication Platform] Fixed: An issue affecting the device registration in Entra ID Windows Account Manager (WAM) plugin. [Input and Composition] Fixed: An issue affecting the complete removal of unused language packs and Feature on Demand (FOD) packages. This led to unnecessary storage use and increase in Windows Update installation time. [Print and Peripherals] Fixed: An issue affecting USB-connected Multi-Function printers with dual protocol interfaces, where scanning may fail and prevent use of the OS’s built-in scanning functionality. [Start Menu] Fixed: An issue causing jump lists to disappear from the Start Menu. Fixed: An issue where the Start Menu was not starting after installing an update. [Settings] Fixed: Settings > System > About unexpectedly shows version 2009 instead of version 22H2. [Servicing] Fixed: An issue where Kiosk devices using the ForceAutoLogon configuration and Shift Override might stop responding with a blue screen after being locked and unlocked by support administrators. [File Server] Fixed: An issue where the system may hang when acknowledging an Oplock break on resources located on SMB shares. You can find the official blog post here on Microsoft's website.
    • This is a liability problem. They aren't simply going to trust that you aren't the driver. I think if they really wanted to they could do something similar to key fobs where they only work if they are in the proximity of the driver's seat. As already pointed out by a Random Stranger, simply having your passenger hit the play button doesn't make it any less distracting for the driver.
    • Windows 11 gets improved app defaults settings and Windows Share in build 22631.5545 by Taras Buria Windows 11 build 22631.5545 is now available for download in the Release Preview Channel of the Windows Insider Program. The update is a pretty minor one, but it still packs some important changes, such as improvements for app defaults in the Settings app, Windows Share enhancements, and a few fixes here and there. With build 22631.5545, Microsoft is giving users in the EEA region more control over default apps in Windows 11, particularly for browser defaults. Now, browser defaults support additional file and link types. Your default browser now pins itself to the taskbar (you can turn this option off), plus you can change your typical PDF viewer with one click (if the browser of choice supports PDF handling). As for Windows Share improvements, the sharing window now includes a preview of the link that you are about to send to someone. The rest of the changelog includes various fixes: [Audit] Fixed: An issue with auditing privilege use created too many security event logs. These logs filled up the system drive and prevented users from signing in. [Authentication] Fixed: This update fixes an issue where domain-joined machines running Windows 11 22H2 or 23H2 couldn’t update their account passwords on Windows Server 2025 domain controllers, which led to trust relationship issues. [Country and Operator Settings Asset (COSA)] Fixed: This update brings profiles up to date for certain mobile operators. [Display Kernel] Fixed: An issue that prevented Remote Desktop Protocol (RDP) connections until you restarted your device. [Network file sharing] Fixed: This update fixes an issue where workstations and servers might stop responding when connecting to resources located on Server Message Block (SMB) shares. [Performance] Fixed: This update addresses an issue that prevented the complete removal of unused language packs and Feature on Demand packages, which previously led to unnecessary storage use and longer Windows Update installation times. [Shell] Fixed: This update resolves an issue where kiosk devices might stop responding after being locked and unlocked by an administrator. [Windows Hello] Fixed: This update fixes an issue that prevented the automatic renewal of expiring certificates in Windows Hello for Business. [Windows Search] Fixed: Windows Search responds very slowly—the Search Box can take over 10 seconds to load before you can use it. You can find the announcement post here.
    • Father's Day is coming, so give your dad some great gifts by Steven Parker Mashup from Depositphotos.com (1) (2) Father's Day is quickly approaching on Sunday, June 15. If you haven't gotten your dad a gift for the occasion, don't sweat it. There are lots of affordable gifts you can buy for Father's Day on Amazon, and if you order one or more of them right now, you can get them shipped to you in time to give them to your dad. Below we have put together some Apple deals, and we'll keep expanding the list as we come across more interesting deals, so be sure to check back. iPad Deals Apple iPad 11" 128GB A16 Tablet $299 -14% now $299 (was $349) Apple iPad Air 11" 128GB M3 Chip Tablet -17% now $499 (was $559) Apple iPad Air 13" 128GB M3 Tablet -12% now $699 (was $799) 2024 iPad Mini A17 Pro 128GB 8.3" Tablet -20% now $399 (was $499) 2024 iPad Pro 11" 256GB M4 OLED Tablet -10% now $899 (was $999) 2024 iPad Pro 13" 256GB M4 OLED Tablet -15% $1099 (was $1299) Apple Pencil (3rd Gen, For Select iPads) -13% now $69 (was $79) Apple Pencil Pro (For Select iPad Pro & Air) -23% now $99 (was $129) AirPods deals Apple AirPods Pro 2 Wireless Earbuds -32% now $169 (was $249) Apple AirPods 4 Spatial Audio Wireless Earbuds -23% now $99 (was $129) Apple AirPods 4 Active Noise Canceling Wireless Earbuds -17% now $149 (was $179) Apple Watch Deals Series 10 GPS 42mm (Sport Band) -25% now $299 (was $399) Series 10 GPS 42mm (Sport Loop) -25% now $299 (was $429) Series 10 GPS 42mm (Sport Loop) -23% now $329 (was $429) Series 10 GPS 46mm (Sport Band) -23% now $329 (was $429) Apple Watch Ultra 2 49mm GPS Smartwatch -8% from $739 (reg $799) Apple Watch SE (2nd Gen) Smartwatch -32% from $169 (was $249) MacBook Deals 2025 MacBook Air 13.6" M4 Chip Laptop (16GB/256GB) -15% now $849 (was $999) 2025 MacBook Air 15.3" M4 Chip Laptop (16GB/256GB) -13% now $1049 (was $1199) 2024 MacBook Pro M4 14.2" Laptops -11% from $1429 (was $1599) 2024 MacBook Pro M4 16" Laptops -10% from $2249 (was $2499) Mac Deals Mac Mini M4 10-Core CPU 10-Core GPU -8% now $546 (was $599) iMac M4 24" 8-Core CPU/GPU (16GB/256GB) -8% now $1193 (was $1299) iMac M4 24" 10-Core CPU/GPU (16GB/256GB) -7% now $1349 (was $1499) Kindle deals 16GB Kindle Scribe + Premium Pen -25% now $299.99 (was $399.99) 32GB Kindle Scribe + Premium Pen -24% now $320 (was $420) 64GB Kindle Scribe + Premium Pen -22% now $350 (was $450) Samsung 49" Odyssey OLED G9 (G95SC) 240Hz Curved Gaming Monitor -$800 now $999.99 (was $1799) Samsung Galaxy Buds FE True Wireless Bluetooth Earbuds -35% now $64.99 (was $99.99) Samsung Galaxy Tab S10+ -$120 now $879.99 (was $999.99) Samsung Galaxy Watch Ultra 47mm -31% now $449.99 (was $649.99) SAMSUNG Galaxy S25+ -12% now $879.99 (was $999.99) These are just a small selection of the discounts on offer; for more great deals, go to Amazon's Deals page. As an Amazon Associate, we earn from qualifying purchases.
  • Recent Achievements

    • One Month Later
      Orpheus13 earned a badge
      One Month Later
    • Week One Done
      Orpheus13 earned a badge
      Week One Done
    • One Year In
      Orpheus13 earned a badge
      One Year In
    • Week One Done
      serfegyed earned a badge
      Week One Done
    • Week One Done
      fashionuae earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      525
    2. 2
      ATLien_0
      265
    3. 3
      +FloatingFatMan
      205
    4. 4
      +Edouard
      168
    5. 5
      Xenon
      122
  • Tell a friend

    Love Neowin? Tell a friend!