• 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

    • The $80 video game - A new frontier of cost, or a bridge too far? by Paul Hill The Xbox Games Showcase took place over the weekend, followed by Obsidian Entertainment’s The Outer Worlds 2 Direct deep-dive event into the upcoming game. Shockingly, this will be Xbox Game Studios' first $79.99 game, and unfortunately, it’s part of a larger trend. The price creep is not new; this is just the latest iteration of increasing prices. Just over a decade ago, when the Xbox One launched, you could expect to pay $59.99 for a new standard edition of a game and in 2020, with the launch of the Xbox Series X, prices were nudged up even higher to $69.99. Now, Microsoft is trying to push Xbox Game Studios titles even higher to $79.99, which could set a precedent. Here's an overview of game prices over the years: Xbox Console Release Period Typical AAA Game Price (USD) Original Xbox 2001-2005 $49.99 Xbox 360 2005-2013 $59.99 Xbox One 2013-2020 $59.99 Xbox Series X|S 2020-2025, 2025-Present $69.99, $79.99 The decision to increase game and console prices was not announced this week, but at the start of May. The recent Xbox Games Showcase has just drawn attention to the issue again. The price rises are set to come with some of the games launched during this year’s holiday season. Given the inflation we’ve seen since 2020, rising salaries to keep up with the inflation, and the unilateral tariffs the US is trying to impose on countries the world over, it’s not too surprising to hear Microsoft say prices need to go up due to development costs. Nevertheless, it will still be an unwelcome shock, especially to younger gamers who may not even be out of school and struggle to, or get their parents to pay for an $80 game. Even if you do have a steady income, there is a good chance that your wage has failed to keep up with inflation, making it more difficult to afford brand-new $80 games. In this editorial, I will take a deeper dive into the economic realities of game development and the justifications used to raise prices periodically, the impact on developers, alternative actions consumers can take to avoid paying so much money for games, and the specific context of The Outer Worlds 2’s price rise. The economic realities of game development and the justification for price increases During the COVID-19 pandemic, people were told to stay at home in most countries. In developed countries such as the UK, there were furlough schemes to tide people and businesses over, but production fell significantly in many countries, including the US, where manufacturing output fell at a 43-percent annual rate. The pandemic reduced output from producers globally, which led to the same amount of money chasing fewer goods, causing prices to go up. As inflation rocketed, workers across the economy sought out higher-paying jobs and pay increases, leading to even more money chasing fewer goods and ensuring inflation kept on. As a result, Microsoft was affected by higher hardware production costs, higher wages for developers working on games, and higher fees paid to actors needed for the games. All of these costs are being passed onto customers through increased game, console and accessory prices, and increased subscription costs. The $80 price tag will mainly affect AAA games because they have larger development teams, take longer to make, have more complex graphics, and animators, programmers, and writers who work on these titles are able to demand higher salaries. These games are also trying to innovate more, so they have to use next-gen technologies and pay the associated research and development costs, which get passed onto customers. In general, there is greater demand from consumers looking for the best graphics, which also pushes costs up as complexity rises. The public's demand for better graphics and stories means that development costs go up. While gaming revenue has been rising in many places, the large costs companies face mean diminishing returns on sales. Raising the cost of games and subscriptions is just one way that studios can cover their increased costs. Outside of the company-consumer relationship, public companies like Microsoft are always under pressure from shareholders to continuously increase revenues, which is another reason the company could be raising AAA game prices to $80. Consumer perception and the value proposition of an $80 game Seeing a $10 increase for popular games can be pretty jarring for consumers when the price of games already seems high enough, but what is not seen is the cost that these games take to make, especially with the expectation that the graphics must continually improve with each new update. When you take an objective look at the situation, you can justify the price increase given everything that these companies need to pay for behind the scenes. That said, the move will undoubtedly impact budget-conscious gamers, students, and those with limited disposable income. While these groups will be more excluded from AAA games, there are other options available, which I will cover later. In the case of The Outer Worlds 2, many people have criticized the price tag and said it is ironic considering the fact that the game critiques unregulated capitalism. If this type of sentiment sticks, it could drive more people away from AAA games to cheaper alternatives. As Xbox Game Studios’ first game out of the gates with the increased price, The Outer Worlds 2 will also be the title to take the most flak, whereas subsequent games could get away more lightly because $80 will already be the new normal. Gamers will also likely be cheesed off about the price increase, considering many games, including The Outer Worlds 2, have paid downloadable content (DLC). When games used to be cheaper, you could justify the additional cost of DLC, but when a game costs $80 upfront, then you have to pay for DLC, it seems like less of a good deal. The Outer Worlds 2 and the $80 price point: A case study Helping to justify the $80 price tag is Obsidian Entertainment’s reputation for creating strong role-playing games, deep character development, and choice-driven narratives. Fans of the company will be some of the least likely to worry about the new price because they know they’ll get a game they like for their hard-earned cash. Just like The Outer Worlds 2, the first installment was a full-priced game when it launched six years ago. At the time, full-priced meant $60, so the second installment is going to cost you an extra $20. Online opinion suggests that the price they paid for the first game was worth it, and the second game could be similar. According to this Reddit thread, the poster claimed that they, and many other players, easily spent over 100 hours playing The Outer Worlds, meaning it cost less than $1 per hour, well worth it according to that person. According to How Long To Complete, the game takes about 13.5 hours to 40 hours, depending on how thorough you are. This is much longer than Call of Duty games (Modern Warfare III came in at 17 hours for completionists), and you’re also going to be getting a focused RPG that is praised for its quality over quantity, reflected in the various awards it has won. Given that The Outer Worlds 2 will be one of the first games with the revised price tag, many players will likely try to find fault with it, such as its length, depth of content, technical performance, and replayability. As a sequel, it will also face the bias that it’s not as good as the original. We mentioned that The Outer Worlds isn’t the longest game out there, it isn’t egregiously short like Call of Duty, and it’s also not huge, like many popular titles. We have no official word about how long the upcoming game will take to complete, but if it takes the same time to complete as the first one, players will certainly think twice before handing over $80. Speaking with Eurogamer recently, Brandon Adler, the director of The Outer Worlds 2, said that the developers did a pretty good job predicting what would draw criticism, notably the small worlds. In the second game, the developers have addressed this by making the worlds about 50% bigger and ensuring there is more stuff at the cool places players travel to. Adler also said that players didn’t think much about the feel of the guns in the first game, so this time, they’ve reworked things to improve how guns feel. The game has also been improved to feel more reactive to character decisions, potentially boosting the replay value. All of this was possible because Obsidian Entertainment had more resources to throw at the game thanks to its partnership with Microsoft, which it didn’t have with the first game. The Outer Worlds 2 will be launching day one on Xbox Game Pass, meaning that if you’re a subscriber, you can play the game without the big $80 upfront price tag, for as long as you have Game Pass. The increase in game prices will likely make Game Pass more appealing to players who simply don’t have the money to throw at games that are this expensive. With that said, there’s a very legitimate fear people have with Game Pass, and that is that it has started fine, but as more people turn to it and away from physical games, the stuff you get for the subscription will get worse and worse. Just look at Netflix. Around 2012, the Standard plan cost $7.99, but nowadays, it costs $17.99, and the Standard with Ads plan costs $7.99 instead. In a decade, if most people have moved to subscriptions, is Microsoft going to start “offering” ads to increase its revenues? In April 2022, Game Pass had 10 million subscribers, and by February 2024, that figure had grown to 34 million. Alternative models and potential solutions As alluded to in the previous section, players can dodge paying $80 for new games by picking up a Game Pass / PlayStation Plus subscription if the game is being offered as part of those subscriptions. Not only do you get to avoid the high price of games you want to play, but you may also use your subscription to try out other games you wouldn’t have otherwise tried. This can be good for developers looking to get their games onto people’s consoles. Many games, including The Outer Worlds 2, are available on Game Pass on the first day they come out, helping players avoid high initial prices. On the flipside, Game Pass subscribers are less likely to buy games, which can hurt developers who do not get as many full-game sales. While the value for money with these subscriptions could decline over time, like Netflix has, in the near term, it’s a viable option for people on a budget to get their hands on $80 games. Xbox Game Pass Standard costs $14.99 per month with access to hundreds of games. To spend $80 on that, you’d need to have been subscribed for over five months, which is plenty of time to complete a game and play several other titles included in your subscription, too. Another approach to dealing with rising development costs could have been to introduce tiered releases of games, such as deluxe editions, or increase the prices of existing higher tiers. By offering players extra features or accompanying merchandise or by raising the prices of existing higher tiers, real fans can self-select to pay a higher price, helping cover development costs. Another interesting option for those who don’t want to pay $80 upfront for a game is titles that fall under the Games as a Service model. These include subscription games or free-to-play games that rely on players buying in-game content. The latter model is already very popular on mobile platforms, and it makes games more accessible to younger players. Some of these games let you buy in-game items to speed up progress, but in many cases, you can get away with paying absolutely nothing. Finally, for those who do not want to subscribe to Game Pass or PlayStation Plus, and also do not want to pay $80 for a single game, there’s always the option to switch to indie games instead, where you can pay a much lower price for titles. Doing this has lots of benefits. First, it exposes you to more games that have potentially implemented new ideas and mechanisms. It helps the developers financially as they have fewer sales than big AAA games. Second, it tells Microsoft and AAA game makers that you’re not going to be spending $80 on one game or not nearly as much, making it more difficult for them to justify the price or continued hikes in the future. Conclusion Unfortunately for players, the constant demand for game improvements has led to increased costs for game studios, necessitating the passing on of costs to players in the form of $80 games. The decision to increase the price of The Outer Worlds 2 has already drawn criticism from gamers, with some finding it ironic that a game critiquing capitalism has been one of the first to put its price up. Outer Worlds 2 faces a unique challenge as one of the first games to be sold for $80. It’s likely to have to absorb the worst of the backlash compared to the titles that come later when people have accepted the price rises. Additionally, Outer Worlds 2 may be a AAA game, but it’s certainly not as popular as Call of Duty or Assassin’s Creed—this could pose a challenge for Obsidian Entertainment. Rising game prices, especially for AAA games, are probably not going away due to the costs associated with developing new tools for making better games and paying increasing wages. While this sucks for consumers, especially those on a budget, there are still options available, including a Game Pass subscription, playing free-to-play games, and buying much cheaper indie games. The increase in gaming prices could even be a good thing for the smaller game studios, as they may be able to attract more customers. This allows these smaller developers to increase revenues to reinvest in their business and grow. More people switching to cheaper indie games, as mentioned earlier, would make it harder for AAA game makers to justify further price rises. The $80 price tag for games is likely here to stay, even if it does discourage players from buying games outright. Not only does it help developers fund the cost of development better, but it also acts as a funnel for Microsoft to get more subscribers on Game Pass. Not only that, but Nintendo has also announced an $80 price tag for some of its games. To be fair to studios working on AAA games, many of the titles people buy today have longer play times compared to games in the past, the graphics are much better, and there tend to be additional play modes. Just look at PS1 and PS2 games, which were largely offline. Compared to consoles that came after, they introduced a multiplayer mode, in addition to the single-player campaign, which does justify a higher price. With the introduction of VR devices and the new gaming experiences they offer, it’s fair to say that game makers are not resting on their laurels and charging the Earth at the same time. Tangible improvements are being made that justify an increased price. I think that we will see game prices for AAA titles continue rising every five years or so, going forward, and a steady increase of people shifting over to subscription services like Game Pass. As we have seen from the backlash to the current price rise, studios will also need to balance how much they’re investing in new games in the first place with how much customers are willing to pay. I think that if games ever jump from $90 to $100 or more, that could create a significant purchasing roadblock in people’s minds, so game makers would need to tread carefully at that point.
    • I love the way liquid glass looks. They just need to tweak certain parts like control center. This is only beta 1.
    • Mixed news. Glad to hear LibreOffice is catching up to MS's Office. The latter has become way too bloated/complicated and non-intuitive to non-power users. Apparently, MS no longer cares for the non-gaming home-based consumer. Sad to hear support for WIndows-7 is ending. Many users remain with Windows-7 as it was easy to use, intuitive, and reliable. Non-power users and gamers don't need to needless complexity, dubious "features," and long-term instability of the last few versions. [After "how many years" Windows-10 is now reasonably stable--as long as MS stops mucking with it.]
    • Yeah but Microsoft actually did a good job with Aero Glass. I remember the only complaints was about how people could not use it without a top tier card back in the day, it would also disable until you activated Windows. Aero Glass was used in window borders, taskbar and yes the Start menu, but only as a border reference, the programs in the Start menu had a black background on Start (if I remember correctly) and All Programs and context menus were not glass.
  • Recent Achievements

    • One Year In
      Wulle earned a badge
      One Year In
    • One Month Later
      Wulle earned a badge
      One Month Later
    • One Month Later
      Simmo3D earned a badge
      One Month Later
    • Week One Done
      Simmo3D earned a badge
      Week One Done
    • One Month Later
      greege earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      549
    2. 2
      ATLien_0
      236
    3. 3
      +FloatingFatMan
      163
    4. 4
      Michael Scrip
      119
    5. 5
      +Edouard
      91
  • Tell a friend

    Love Neowin? Tell a friend!