EXCEL VBA'DA KÖPRÜ LİNKLERİ

 

Excel VBA'da, köprüler elektronik tablonuzda başka bir konuma, örneğin bir web sayfasına, başka bir çalışma sayfasına veya aynı veya farklı çalışma kitabındaki belirli bir hücreye gidebilen tıklanabilir bağlantılar oluşturmak için kullanılır. VBA Köprülerini kullanarak Excel çalışma kitabınızda köprüler oluşturabilir, değiştirebilir veya kaldırabilir, VBA köprülerini içeren görevleri otomatikleştirmeyi kolaylaştırabilirsiniz.

Aşağıda gösterildiği gibi köprü metinleri kullanılabilir, VBA köprü metinlerini gösteren bir örnek aşağıdadır.

Bu örnek VBA Hyperlinks adresini " https://www.google.com " olarak ayarlar. VBA Hyperlinks.Add fonksiyonuyla "A1" hücresine hyperlink'i kuruyoruz ve hyperlink'e tıklamamız için görüntülenecek metni tanımlıyoruz. Çıktı aşağıda gösterilmiştir.

Ve eğer "A1" hücresine tıklarsanız otomatik olarak Google'a yönlendirileceksiniz.

ÖNEMLİ NOKTALAR

• VBA Köprü Metinleri, sizi belirli bir yere götüren ve program aracılığıyla eklenebilen, değiştirilebilen veya kaldırılabilen tıklanabilir bağlantılardır.
• Çalışma kitabında gezinmeyi otomatikleştirerek kullanıcı deneyimini geliştirirler.
• Dahili köprüler sayfalar arasında gezinmeyi kolaylaştırarak gezinmeyi kolaylaştırır.
• Outlook kullanılarak tıklanabilir bağlantılara sahip e-postalar göndermek için hiper bağlantılar kullanılabilir.
• Verilere veya kullanıcı girdilerine bağlı olarak hiperlink adresleri dinamik olarak üretilebilir.
• Veri doğrulama amacıyla hiperlinkler kullanılabilir ve ilgili bilgilere hızlı erişim sağlanabilir.

VBA Bağlantılarının Formülü

Hyperlinks.Add(Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay])

Nerede:

• Bağlantı: Bağlantının yer alacağı aralık veya şekil.
• Adres: Hedef konumun adresi, örneğin bir web URL'si veya dosya yolu.
• Alt Adres (isteğe bağlı): Hedef belge içindeki belirli konum. Örneğin, bir hücre başvurusu veya yer imi.
• Ekran İpucu (isteğe bağlı) : Kullanıcı köprü metninin üzerine geldiğinde metin bir araç ipucu olarak görünür.
• TextToDisplay (isteğe bağlı): Köprü metni için görüntülenen metin. Sağlanmazsa, Excel gerçek köprü metni adresini görüntüler.

Yukarıda VBA Hyperlinks nesnesini kısaca açıklamak gerekirse, önce VBA Hyperlink.Add fonksiyonunda bahsedilen parametrelerden bahsedelim.

• Bağlantı: Bağlantıyı eklemek istediğiniz hücre veya şekil.
• Adres: Bağlantı kurulacak hedef URL veya dosya yolu.
• Alt Adres: İsteğe bağlı. Hedef içindeki belirli konum (örneğin, bir hücre başvurusu).
• Ekran İpucu: İsteğe bağlı. Araç ipucu metni, kullanıcı köprü metninin üzerine geldiğinde görünür.
• TextToDisplay: İsteğe bağlı. Metin, hücredeki köprü metni için görüntülenecektir. Sağlanmazsa, gerçek köprü metni adresi görüntülenecektir.

Excel VBA'da Köprüler Nasıl Oluşturulur?

VBA'da Köprüleri uygulamak için aşağıdaki adımları izleyin:

Adım 1: Araç çubuğundaki “Geliştirici” bölümüne gidin ve “Visual Basic” seçeneğine tıklayın. Şimdi, VBA Editörü fonksiyonlar ve Alt prosedürler eklemek için açılır. Ardından, yeni bir modül veya boş sayfa oluşturmak için “Ekle” ve “Modül” düğmelerine tıklayın.

Hiperlinklerin ve site adlarının bulunduğu bir tablomuz olduğunu varsayalım; aşağıdaki tabloda gösterilen VBA Hiperlink Nesnesine hiperlinkleri eklememiz gerekiyor:

Adım 2: Alt rutinin adını tanımlayarak başlayın.

Adım 3: Bu alt programın üzerinde çalışacağı çalışma sayfasını tanımlayın.

Adım 4: VBA XlUp işlevini kullanarak tablo boyutunu bulun. Bu, tabloları değiştirmeyi daha dinamik hale getirmek için yapılır. Tablo değerleri güncellenirse aralığı manuel olarak değiştirmemize gerek kalmamasını sağlar.

VBA XlUp fonksiyonu her sütundaki son boş olmayan hücreyi bulur ve bir tamsayı değeri döndürür.

Adım 5: VBA Hyperlinks.Add ile eklenmesi gereken web sitesi adlarının listesini tanımlayın.

Adım 6: Yukarıdaki web sitesi adlarının URL aralığını tanımlayın.

Adım 7: Hiperlinkleri uygulamak için bir for döngüsü başlatın. Birden fazla web sitesine aynı anda ekleyin.

Adım 8: Tekrarlı olarak çalıştırılacak aralıktaki web sitesi adlarını tanımlayın.

Cells(i,1).value fonksiyonunu kullanarak, daha önce tanımlanan aralıkların yardımıyla web sitesi adları aralığını tek tek çalıştırabiliriz. Benzer bir işlem diğer sütunlar için de yapılır.

Adım 9: Bir sonraki sütunda verilen URL Bağlantılarını for döngüsünün içinde çalıştırın.

Adım 10: VBA Hyperlinks.Add fonksiyonunu gerçekleştirmek istediğiniz aralığı tanımlayın.

Adım 11: Önceki adımlarda belirtilen parametrelerle (web sitesi adları, URL'ler ve Köprü metni aralığı) köprü metinlerini ekleyin.

Bağlantının Çapasını, döngüyü çalıştırdığımızda elde ettiğimiz web sitesi adları olarak tanımlıyoruz.

Köprünün adresi, sayfada belirtilen web sitesi URL'sidir. TextToDisplay, Website Name parametre adıyla tanımlanacaktır.

Kod:

Sub AddHyperlinks()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim rngWebsiteNames As Range
Set rngWebsiteNames = ws.Range(“A2:A” & lastRow)
Dim rngURLs As Range
Set rngURLs = ws.Range(“B2:B” & lastRow)
Dim i As Long
For i = 1 To rngWebsiteNames.Rows.Count
Dim websiteName As String
websiteName = rngWebsiteNames.Cells(i, 1).Value
Dim websiteURL As String
websiteURL = rngURLs.Cells(i, 1).Value
Dim hyperlinkCell As Range
Set hyperlinkCell = rngWebsiteNames.Cells(i, 1)
ws.Hyperlinks.Add _
Anchor:=hyperlinkCell, _
Address:=websiteURL, _
TextToDisplay:=websiteName
Next i
End Sub

Adım 12: “A” sütununda gösterilen metin üzerinde köprü metinleri uygulamak için yukarıdaki kodu çalıştırın. Çıktıyı ilgili sayfada görüntüleyin.

Bağlantıya tıkladığımızda bahsi geçen web sitesine yönlendirileceğiz.

Artık Excel VBA'da Köprülerin nasıl uygulanacağını öğrendiğimize göre, daha ilginç örneklere bakalım.

Örnekler

Programımızda VBA'da Hyperlink kullanımına dair birkaç örneğe bakalım.

Örnek 1

Aynı Excel Çalışma Kitabında farklı çalışma sayfalarına bağlantı vermek için köprüler oluşturmalıyız. Bu, VBA Hyperlinks.Add fonksiyonu yardımıyla yapılabilir.

Adım 1: Excel Çalışma Kitabındaki farklı Çalışma Sayfalarını takip eden VBA Köprülerini gerçekleştiren bir alt prosedür tanımlayın.

Adım 2: Bu alt rutinin üzerinde çalışacağı çalışma sayfasını tanımlayın.

Adım 3: Tablonun boyutu bulunur ve tanımlanır. Excel tablolarıyla çalışmayı daha dinamik hale getirmek için XlUp işlevi kullanılır. Tablo değerleri güncellenirse, çalıştığımız aralığı her zaman manuel olarak değiştirmeye gerek kalmaz.

VBA XlUp fonksiyonu her sütundaki son boş olmayan hücreyi bulur ve bir tamsayı değeri döndürür.

Adım 4: Eklenmesi gereken sayfa adlarının aralığını tanımlayın.

Adım 5: Yandaki sütundaki sayfa adlarının dizin aralığını tanımlayın.

Adım 6: Tüm tabloyu kapsayacak bir for döngüsü oluşturun.

Adım 7: Yinelemeli olarak çalıştırılacak aralıktaki sayfaların adını tanımlayın. Farklı sayfalara köprüler oluşturmak için kullanılır.

VBA Cells fonksiyonu ile değerinin görüntülenmesini istediğimiz hücrenin satır ve sütun numarasını belirtebiliriz. Burada sayfaların adlarını almak için kullanılır. Aynı işlem diğer parametreler için de yapılır.

Adım 8: Sayfaların indeksini almak için tabloyu tarayın.

Adım 9: Köprüleri yerleştirmeniz gereken aralığı tanımlayın. Burada, bunu SheetNames'e yerleştirmeyi seçiyoruz.

Adım 10: Bir değer bulunamadığında kodun durmamasını sağlamak için değerler üzerinde VBA Hata işleme gerçekleştirin .

Adım 11: Önceki adımlarda belirtilen parametrelerle (sayfa adları, URL'ler ve Köprü metni aralığı) köprü metinlerini ekleyin.

Bağlantı adresi olmadığından, onu boş bir dize olarak bırakıyoruz ve belgenin içindeki bir konumu tanımlayan Alt Adresi tanımlıyoruz. Bu nedenle, VBA'daki Ad işlevini kullanarak bunu sayfa adına bağlıyoruz ve imleci "A1" hücresine yerleştiriyoruz.

Adım 12: Kullanıcıya sayfanın bulunamadığını söyleyen bir MsgBox döndüren bir Else koşulu tanımlayın.

Kod:

Sub AddSheetHyperlinks()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet3”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim rngSheetNames As Range
Set rngSheetNames = ws.Range(“A2:A” & lastRow)
Dim rngSheetIndices As Range
Set rngSheetIndices = ws.Range(“B2:B” & lastRow)
Dim i As Long
For i = 1 To rngSheetNames.Rows.Count
Dim sheetName As String
sheetName = rngSheetNames.Cells(i, 1).Value
Dim sheetIndex As Long
sheetIndex = rngSheetIndices.Cells(i, 1).Value
Dim hyperlinkCell As Range
Set hyperlinkCell = rngSheetNames.Cells(i, 1)
On Error Resume Next
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Sheets(sheetIndex)
On Error GoTo 0
If Not targetSheet Is Nothing Then
ws.Hyperlinks.Add _
Anchor:=hyperlinkCell, _
Address:=””, _
SubAddress:=”‘” & targetSheet.Name & “‘!A1”, _
TextToDisplay:=sheetName
Else
MsgBox “Sheet with index ” & sheetIndex & ” not found!”, vbExclamation
End If
Next i
End Sub

Adım 13: Yukarıdaki tablo için kodu çalıştırın. Çıktı aşağıda gösterilmiştir:

“Sheet1”e tıkladığımızda Çalışma Kitabı bizi otomatik olarak “Sheet1”e yönlendirir.

Başka bir örneğe bakalım.

Örnek 2

Excel VBA'da daha fazla kolaylık için bir bağlantıya tıklayarak dosyaları açmak istediğinizi varsayalım. Bu, aşağıdaki tabloda gösterilen dosya yollarına köprüler ekleyerek yapılabilir.

Adım 1: VBA gerçekleştiren bir alt program oluşturun. Bağlantılar tabloda belirtilen dosya yollarına gider.

Adım 2: Bu alt rutinin üzerinde çalışacağı çalışma sayfasını tanımlayın.

Adım 3: VBA XlUp fonksiyonunu kullanarak tablonun boyutunu döndürün.

VBA XlUp fonksiyonu her sütundaki son boş olmayan hücreyi bulur ve bir tamsayı değeri döndürür.

Adım 4: Tabloda bulunan dosyaların adlarını ekleyin ve bunları bir aralık değişkenine koyun.

Adım 5: Tabloya eklenen Dosya Yolları aralığını başka bir Aralık değişkenine ekleyin.

Adım 6: Tüm tabloyu kapsayacak bir for döngüsü oluşturun.

Adım 7: Dosya aralığı değişkeninde bulunan dosyaların adını yinelemeli olarak çalıştırmak için tanımlayın. Bu, ilgili dizinlerine köprüler oluşturmak için kullanılır.

VBA Cells fonksiyonunu kullanarak, VBA Cells fonksiyonuyla birlikte kullanılan değer fonksiyonu, satır ve sütun numaralarını tanımlayarak belirli hücrenin değerini döndürebilir. Bu fonksiyon, tabloda belirtilen dosyaların adlarını çıkarır. Aynı şey tüm parametrelere uygulanır.

Adım 8: Bireysel dosya dizinlerine ulaşmak için dosya yolu aralığını tarayın.

Adım 9: Köprüleri yerleştirmeniz gereken aralığı tanımlayın. Burada, onu dosya yoluna yerleştirmeyi seçiyoruz.

Adım 10: Dosya yolu boş bir dize değilse, VBA Köprü Bağlantıları ile bir VBA Köprü Bağlantısı gömün. Fonksiyon ekleyin.

Dize değişkeni dosya yolu daha sonra VBA Dir işlevi kullanılarak uygulanır ve ardından filePath sütununa sabitlenir ve içine gömülür. Görüntülenecek metin aynı kalır.

Adım 11: Dosya yolunun mevcut olmadığını belirten bir MsgBox döndüren else kısmını başlatın.

vbExclamation, Excel VBA'da MsgBox fonksiyonuna ikon eklemek için kullanılabilen bir ikondur.

Kod:

Sub AddFileHyperlinks()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet4”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim rngFileNames As Range
Set rngFileNames = ws.Range(“A2:A” & lastRow)
Dim rngFilePaths As Range
Set rngFilePaths = ws.Range(“B2:B” & lastRow)
Dim i As Long
For i = 1 To rngFileNames.Rows.Count
Dim fileName As String
fileName = rngFileNames.Cells(i, 1).Value
Dim filePath As String
filePath = rngFilePaths.Cells(i, 1).Value
Dim hyperlinkCell As Range
Set hyperlinkCell = rngFilePaths.Cells(i, 1)
If Dir(filePath) <> “” Then
ws.Hyperlinks.Add _
Anchor:=hyperlinkCell, _
Address:=filePath, _
TextToDisplay:=filePath
Else
MsgBox “File not found at location: ” & filePath, vbExclamation
End If
Next i
End Sub

Adım 12: Kodu çalıştırın. İlgili sayfaya gidin ve görüntüleyin.

“PowerPoint” yoluna tıkladığınızda sistemde o yolda bulunan bir sunum açılır.

Döngülerle Çoklu Sayfaların Hiper Bağlantıları

Excel'de birden fazla sayfayla çalışırken ve VBA'da döngüler kullanarak köprüler eklerken , bir döngüyü kullanarak sayfa adları listesinde dolaşabilir, ilgili sayfaları bulabilir ve aynı çalışma kitabı içinde bunlar arasında gezinmek için köprüler oluşturabilirsiniz.

Bunu nasıl başarabileceğinizi anlatıyoruz:

• Sayfa Listesi: Sayfalarınızdan birindeki bir sütunda sayfa adlarının bir listesini hazırlayın (örneğin, “Sayfa2,” “Sayfa3,” “Sayfa4”). Bu liste, köprü metinleri oluşturmak için referans olarak kullanılacaktır.
• Sayfa Adları Arasında Döngü: Listedeki her sayfa adını incelemek için bir döngü kullanın.
• Karşılık Gelen Sayfaları Bul: Her sayfa adı için, çalışma kitabındaki karşılık gelen sayfayı bulun. Sayfanın var olup olmadığını kontrol etmek için Çalışma Sayfaları koleksiyonunu kullanabilirsiniz.
• Köprü Ekleme: Sayfa mevcutsa, hedef sayfaya yönlendiren geçerli sayfada bir köprü oluşturun. Köprüyü eklemek için Hyperlinks.Add yöntemini kullanın.
• Hata İşleme: Listedeki sayfa adının çalışma kitabındaki mevcut sayfalardan hiçbirine karşılık gelmediği durumları işlemek için hata işlemeyi kullanmayı düşünün.
• TextToDisplay: Köprü metnini daha kullanıcı dostu hale getirmek için, sayfa adını göstermek yerine köprü metni için açıklayıcı bir metin görüntülemek üzere TextToDisplay parametresini kullanabilirsiniz.
• Döngüyü Çalıştırın: Döngü ve köprü oluşturma mantığını ayarladıktan sonra, çalışma kitabınızdaki birden fazla sayfaya köprü eklemek için VBA kodunu çalıştırın.

Bu yaklaşım, aynı Excel çalışma kitabındaki sayfalar arasında gezinmek için dinamik olarak köprüler eklemenize olanak tanır. Çok sayıda sayfanız olduğunda ve aralarında kolay erişim için bir gezinme sistemi oluşturmak istediğinizde faydalı olabilir.

Dikkat Edilmesi Gereken Önemli Noktalar

• Harici kaynaklara (web siteleri veya dosyalar) köprü bağlantıları oluştururken, kaynağın kullanılamadığı veya bağlantının koptuğu durumları ele almak için her zaman hata işleme özelliğini ekleyin.
• Bağlantıları programlı olarak oluşturduktan sonra üzerine tıklayarak doğru bir şekilde çalıştığından emin olun.
• Köprü metinleri için açıklayıcı metin sağlamak ve bağlantının nereye gittiğini kullanıcılara açıkça göstermek için TextToDisplay parametresini kullanın.
• Yeni köprü metinleri eklemeden önce, yinelenen bağlantılardan kaçınmak için hedef aralıktaki mevcut köprü metinlerini temizlemeyi düşünün.
• Köprü metni adreslerinin doğru ve geçerli olduğundan emin olun. Bozuk bağlantılara yol açabilecek yanlış URL'leri veya dosya yollarını kullanmaktan kaçının.
• Tek bir sayfaya birçok köprü metni eklemek onu karmaşık ve yönetilmesi zor hale getirebilir. Köprü metinlerini dikkatli kullanın.