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

Showing blogs 1-20 (out of 129)

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.

Tags:   VBA macros | General    |    Musings | Microsoft Office

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

A short-cut key to bring up the arguments of user-defined functions

Posted by Andy Brown on 17 June 2019

When you discover a new short-cut key, should you share it? This blog shows a way to bring up Intellisense for the arguments to user-defined functions in Excel VBA.

Why the Excel range name conflict dialog box appears, and how to avoid looping

Posted by Andy Brown on 14 June 2019

When you're copying worksheets containing range names, Excel can detect conflicts and take you into a seemingly endless loop of Yes/No dialog boxes. This blog explains why this happens, and how to get out of the loop!

Tags:   Excel | Formulae

Andy B's five biggest OOH! moments on an Excel training course.

Posted by Andy Brown on 09 May 2019

All trainers live for that moment when someone's face lights up, as they learn something new which will change their working life for the better. This blog lists my 5 biggest such OOH moments from our Excel training courses. How many of these tips do you know?

Tags:   Excel | General

Use the Excel camera tool, or create a linked picture in Word or PowerPoint

Posted by Andy Brown on 25 April 2018

This blog shows options for creating a webcam focused on one particular area of an Excel workbook! You can do this either by pasting the Excel range into PowerPoint or Word as a linked picture, or by using the Excel camera tool.

Tags:   Excel | General

Changes to Wise Owl Excel courses (including a new intermediate course)

Posted by Andy Brown on 15 January 2018

We've revamped our Excel course offerings, to include a new one-day Intermediate Excel course to sit between our introduction and advanced courses.

Tags:   Excel | General

How to create multiple worksheets based on pivot table report filter values

Posted by Andy Brown on 08 December 2017

Did you know that you can automatically generate multiple versions of a pivot table, with each one configured to filter by a different item? We didn't - this blog is an attempt to apologise for our oversight!

How to reuse a query created in Query Editor or Power Query

Posted by Andy Brown on 29 November 2017

This short blog explains how you can copy and paste the steps of a query that you've created in Power Query (within Power BI Desktop). The steps also work for queries created in Power Query within Excel.

Tags:   Excel | PowerPivot    |    Power BI | Query editor

Loading live web data into Excel without writing VBA code to scrape HTML

Posted by Andy Brown on 21 August 2017

Need to get tables of data into Excel from a website? There's no need to write lots of VBA code to scrape the site - just use Power Query to create a live link to the relevant page(s).

Tags:   Excel | PowerPivot    |    Power BI - Excel | Power Query

Find the many ways to interact directly between Excel and Power BI Service

Posted by Andy Brown on 29 June 2017

You can pull Excel workbooks (or individual data ranges) into Power BI Service, or push them from Excel using a special add-in. This blog shows how to use both approaches, as well as the underwhelming Analyze in Excel feature.

Tags:   Excel | PowerPivot    |    Power BI | Power BI Service
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!