Excel / VBA blogs from Wise Owl (page 1 of 7)

Showing blogs 1-20 (out of 138)

The Financial Modelling World Cup

Posted by Andy Brown on 29 September 2021

Want a chance to prove your financial modelling skills? Participate in the Financial Modelling World Cup, or maybe just watch some Excel Esports …

Tags:   Excel | General

How to build an Excel word search without even using VBA

Posted by Andy Brown on 23 February 2021

Details of an impressive new Excel word search system which allows you to recreate a randomised grid every time you recalculate your formulae!

Tags:   Excel | Formulae

Building a word search system in Excel VBA

Posted by Andy Brown on 02 February 2021

My aim in this blog is to show how you can go about building any computer system from scratch (and not just a word search in Excel VBA). Along the way I've included lots of hints and tips!

Tags:   VBA macros | General

Using the WOWSER Excel VBA system to build a word search

Posted by Andy Brown on 01 February 2021

Follow this blog to construct your own word search, complete with a missing phrase spelt out by the unused letters!

Tags:   VBA macros | General

The new Excel data types will make a big difference to your life

Posted by Andy Brown on 22 December 2020

Soon you will be able to display information from almost any common dataset in your Excel worksheets automatically. For now your access will probably be limited to stocks and geographical data, but many more data types (including your own custom ones) should be available soon.

Creating and using Lambda Functions in Excel

Posted by Andy Brown on 21 December 2020

You can now create and use anonymous functions in Excel (usually called "Lambda functions"). Learn how to add to the stock of Excel functions without having to learn any programming code!

Tags:   Excel | Formulae

You can use the new LET function in Excel to store interim formulae calculations

Posted by Andy Brown on 24 October 2020

The new Excel LET function lets you create variables to store the results of calculations, making formulae easier to read and quicker to calculate.

Tags:   Excel | Formulae

Why the XLOOKUP function should kill off VLOOKUP and INDEX/MATCH

Posted by Andy Brown on 24 September 2020

The XLOOKUP function has at least 7 advantages over an equivalent VLOOKUP function, as this blog shows. Convert to XLOOKUP now, and then convert your colleagues!

Tags:   Excel | Excel functions

How to have fun pixellating and reconstructing images in VBA

Posted by Andy Brown on 25 August 2020

You can have great fun taking your favourite image (a picture of your loved one?) and pixellating it in VBA. The results are stored as RGB numbers in a workbook - what you do with them then is up to you!

Tags:   VBA macros | General

Should you use JavaScript or VBA to program in Excel?

Posted by Andy Brown on 28 June 2020

Is JavaScript for Office (also known as OFFICE-JS) the next big thing? Unless you're a full-time web developer, emphatically no - this blog gives 3 reasons why you should continue to write your Office programs in humble VBA.

Use VBA to create Microsoft Team meetings

Posted by Sam Lowrie on 26 June 2020

Did you know that you can create Microsoft Team meetings using VBA in Outlook? This blog shows you how!

Tags:   VBA macros | General

Talking to Excel to fill in your input forms, using speech recognition and VBA

Posted by Andy Brown on 13 March 2020

What happens when you combine the magic of speech recognition with the power of VBA? A user form which you can fill in without a keyboard!

Tags:   VBA macros | General

Find out how dynamic arrays in Excel will change your (working) life!

Posted by Andy Brown on 14 February 2020

Dynamic arrays are a new feature being rolled out in Excel 365. This blog explains how they work, and why they will make so many things in Excel easier.

Tags:   Excel | General

Announcing the first Wise Owl online training course (Excel VBA)

Posted by Andy Brown on 10 January 2020

After a huge investment of time and expertise, we're proud to announce that our first full online training course is now live on our website! The course shows you how to program in VBA in Excel, and is completely free (you don't even have to log in to follow it).

Tags:   VBA macros | General

Excel MasterClass: creating a worksheet and chart to track your weight

Posted by Andy Brown on 14 October 2019

It's fairly easy to create a simple Excel worksheet to chart your weight, but this master class shows you lots of tips and tricks to make the final result quicker to create and easier to use.

Tags:   Excel | Masterclass

Introducting the new XLOOKUP and XMATCH functions

Posted by Andy Brown on 13 September 2019

Microsoft have finally recognised the limitations of the VLOOKUP function, and are rolling out the XLOOKUP function to supersede it, as well as the useful (if less significant) XMATCH function.

Tags:   Excel | General

How to confuse other Excel users by changing your cell styles

Posted by Andy Brown on 14 August 2019

Want to make a worksheet inexplicably change colour, and display strange text in cells? This blog explains how you can do this using the Normal style.

Tags:   Excel | Formatting

Another OOH moment in Excel - a quick way to create charts

Posted by Andy Brown on 14 August 2019

The previous blog listing great OOH moments in Excel omitted one of the biggest - the absolute quickest way to create a chart.

Tags:   Excel | Charts

A different owl's take on the five biggest ooh! moment tips in Excel

Posted by Andy Brown on 17 June 2019

This month it's Shaun's turn to nominate the five best short-cut tips in Excel.

Tags:   Excel | General

Problems with the Application.ScreenUpdating = False command in VBA

Posted by Andy Brown on 17 June 2019

Some people are experiencing problems when trying to suppress screen updates while macros are running in Excel VBA, under Windows 10. This blog gives one possible solution.

Tags:   VBA macros | General
This page has 2 threads Add post
28 Mar 20 at 12:05

Hi Andrew

Firstly want to say your scraping series of videos are an excellent resource. I have never coded previously but they have helped me a great deal.

I am in need of some help though with the below code if possible.

Sub Portfolio_Webscrape_IE()

Dim IE As New SHDocVw.InternetExplorer
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim HTMLInput As MSHTML.IHTMLElement
Dim HTMLAs As MSHTML.IHTMLElementCollection
Dim HTMLA As MSHTML.IHTMLElement
Dim URL As String

URL = "https://uk.investing.com/portfolio/"

IE.Visible = True
IE.navigate URL

Do While IE.readyState <> READYSTATE_COMPLETE
Loop

Set HTMLDoc = IE.document
Set HTMLInput = HTMLDoc.getElementById("loginFormUser_email")
HTMLInput.Value = "xxxx@xxxx.co.uk"
Set HTMLInput = HTMLDoc.getElementById("loginForm_password")
HTMLInput.Value = "xxxxx"
Set HTMLAs = HTMLDoc.getElementsByTagName("a")

For Each HTMLA In HTMLAs
If HTMLA.getAttribute("href") = "javascript:void(0)" Then
HTMLA.Click
Exit For
End If

Next HTMLA

End Sub

When I press inspect element on the “sign-in button” on the website it says:

<a class="newButton orange" onclick="loginPageFunctions.submitLogin();" href="javascript:void(0);">Sign In</a>

I've searched through many resouces but they only seem to explain how to do this if you have an actual "button" name or id or the href / rel to grab.

I would but very grateful if you can help me with my problem.  Thanks in advance.

29 Mar 20 at 12:09

We're a bt preoccupied with COVID-19 events here, but I've posted your question in the hope that perhaps one of other kind readers can help?

04 May 17 at 18:18

Hello,

I'm having troubles in achieving something on excel and I think you are the right guy's to give me a hand with it.

I have an excel sheet that I use for flight simulation and between other things, I want to create a small compus with shapes(arrows) that rotates whenever I add a value to a specific cell. So far I managed to do it but only with a single arrow. When I try to add a second shape it says "Ambiguous name detected worksheet_Change".

I need 2 arrows because one indicates the heading of the aircraft and another indicates the wind.

On VBA I managed to do the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    'event raised when value in cell changed BY USER
    
    Dim KeyCells As Range
    Set KeyCells = Range("Z2")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
End Sub
Sub MoveArrowToA1()
    'Select shape to move
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z2")
    ActiveSheet.Shapes.Range(Array("seta")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    
End Sub

This gives me only the rotation for one of the arrows (shapes), do you have any idea how do I add a second one?

https://drive.google.com/file/d/0B6JMHUkV2NKJRTZVUmRBZER6bVE/view?usp=sharing  On this link there's a picture to give you the idea of what I'm trying to do.

If you need any other information from me, please let me know.

 

 

Thank you very much for all the help you can give me.

 

 

Kindly,

 

 

Edgar Freitas

05 May 17 at 07:05

Hi Edgar, that sounds like a cool idea!

The "ambiguous name detected" error occurs when you create multiple procedures in the same scope with the same name. What I imagine has happened is that you've copied and pasted the Worksheet_Change subroutine to create a separate procedure for the second arrow? Sadly, you can't do it this way - you can only have one Worksheet_Change subroutine per worksheet. The simple solution is to add the code for the second arrow to the same subroutine as the first.

I hope that helps and good luck with the project!

05 May 17 at 18:30

Andrew, I can't thank you enough!! It worked perfectly!!! :D

Thank you so much for your help.

 

Have a great weekend.

 

 

Kindly,

 

Edgar Freitas

05 May 17 at 16:40

Hello Andrew,

Thank you very much for your reply. 

That's exactly what happened. I thought I could simply copy and paste it to create new arrows.

Your solution is: "...to add the code for the second arrow to the same subroutine as the first."

As I'm not good with VBA, could you please indicate me how to do this? I already tried but I think it's not the right way of doing it as I got an error saying "Duplicate declaration on the same scope".

This is how I did it:

Private Sub Worksheet_Change(ByVal Target As Range)
    'event raised when value in cell changed BY USER
    
    Dim KeyCells As Range
    Set KeyCells = Range("Z2")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
    Dim KeyCells As Range
    Set KeyCells = Range("Z3")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
End Sub
Sub MoveArrowToA1()
    'Select shape to move
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z2")
    ActiveSheet.Shapes.Range(Array("seta")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z3")
    ActiveSheet.Shapes.Range(Array("seta2")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    
End Sub
 

Thank you once more Andrew!

 

Kindly,

 

Edgar Freitas

Andrew G  
05 May 17 at 17:29

Hi Edgar,

In the same way that you can't declare two subroutines with the same name in the same module, you can't declare more than one variable with the same name in the same subroutine.  Simply remove the duplicate Dim statements so that you don't have more than one variable with the same name in a single procedure.

I hope that helps!